DTS - 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

Transact-SQL (2000)

DTS

Author

Topic

majnoon
Starting Member

26 Posts

Posted - 2002-10-09 : 07:25:27

Can you run a DTS package via a SQL statement?

Wishing you a peaceful journey

nr
SQLTeam MVY

12543 Posts

Posted - 2002-10-09 : 07:46:26

yes

check out dtsrun

also

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

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-09 : 07:47:15

Yes.

Look up DTSRun.exe in Books Online ...
or
Call the pkg from a job and start the job using sp_start_job

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources