DTS from SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DTS from SP

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2002-12-20 : 15:12:49

Is it possible to run a DTS package from a stored procedure?

Thanks,
Lane

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-12-20 : 17:33:32

jsmith8858 mentions 3 options
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=21032

If you want to use the sp_OA* method here is the code
from a post within the last few months

/* Running DTS FROM STORED PROCEDURE */

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

Edited by - ValterBorges on 12/20/2002 17:58:03

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources