Sql Dts Package

VeRiTo
12-15-2003, 07:16 AM
Hi people! I have a simple question.... I have a DTS Package and i need to execute it from a stored procedure but i dont know how!
Any help?
Thanks in advance!

KnooKie
12-15-2003, 07:46 AM
3 choices..........

1) Use DTSRUN via xp_cmdshell
2) Use sp_OA* procedures with the Package Object and the LoadFromSQLServer and Execute methods
3) Create a job that runs your DTS package using CmdExec jobstep and DTSRUN and call sp_start_job

Of these (1) is probably the easiest but requires xp_cmdshell access (highly NOT recommended though)

The sp_OA method..............

/* Running DTS FROM STORED PROCEDURE */

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

select @PackageName = 'Data Import Package' ,
@ServerName = @@ServerName ,
@DatabaseNaame = db_name() ,
@FileName = '\\MyPC\InpFile\TestFile.txt'
exec sp_OACreate 'DTS.Package', @objPackage output
exec @rc = sp_OAMethod @objPackage, 'LoaadFromSQLServer' , 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").vaalue', @FileName
exec @rc = sp_OAMethod @objPackage, 'Execute'
exec @rc = sp_OADestroy @objPackage

VeRiTo
12-15-2003, 07:52 AM
thanks a lot!
i am gonna check this out!

KnooKie
09-21-2005, 09:45 AM
see if this helps

http://www.nigelrivett.net/ImportTextFiles.html

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum