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