home


Loading DTS package via sp_oacreate
Author Nigel Rivett

This is written for sql server v7
This can be used for setting the server/database or any other values needed.
A client application language (e.g. VB) is preferred for a production system but this can be useful for testing.

Global Variables for the package
ServerName
DatabaseName
FileName

declare @objPackage int
declare @PackageName varchar(128)
declare @rc int
declare @ServerName varchar(128)
declare @DatabaseName varchar(128)
declare @FileName varchar(128)

	select 	@PackageName = 'Data Import Package' ,
		@ServerName = @@ServerName ,
		@DatabaseName = db_name() ,
		@FileName = '\\MyPC\InpFile\TestFile.txt'

	exec sp_OACreate 'DTS.Package', @objPackage output
	exec @rc = sp_OAMethod @objPackage, 'LoadFromSQLServer' , null, 
		@ServerName = @ServerName, @Flags = 256, @PackageName = @PackageName 
	exec @rc = sp_OASetProperty @objPackage,
		'GlobalVariables("ServerName").value', @ServerName
	exec @rc = sp_OASetProperty @objPackage,
		'GlobalVariables("DatabaseName").value', @DatabaseName
	exec @rc = sp_OASetProperty @objPackage,
		'GlobalVariables("FileName").value', @FileName
	exec @rc = sp_OAMethod @objPackage, 'Execute'
	exec @rc = sp_OADestroy @objPackage

You would need to test the return code between each of these statements of
course.

home