April 2, 2008 at 8:13 pm
have to run a few DTS packages and stored procedures in between before runing the DTS packages. This a job that I am taking over so the biz. logic is something that I can't recreate.
Now question is Is there a way I can automate this process.
I have approximately 3 DTS packages and 3 stored procedures. How do I tie up all these process so that I can call one stored procedure which inturn call the process in DTS1 -SP1-DTS2-SP2 .. so on ..
April 2, 2008 at 11:43 pm
You can set up a job if you want it to run at a particular time. If you just want 1 proc to call the rest, you can use xp_cmdshell within the proc to run the DTS package.
If you go that route, make sure you read up on the security implications of xp_cmdshell and the use of the proxy account.
Your proc would look something like this
CREATE PROC MasterProc
AS
EXEC SubProc1
EXEC SubProc2
exec master..xp_cmdshell 'DTSRUN <The command line for the dts package here>'
.. and so on ...
You can use DTSRUNUI to get the command line of the DTS package.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 3, 2008 at 8:12 am
Gail, how do you obtain the command line options for a DTS package?
-- You can't be late until you show up.
April 4, 2008 at 12:14 am
You can use DTSRUNUI to get the command line of the DTS package.
It's a gui app that lets you select the server and package, set the options and parameters and then generate the command line for DTSRun, either encrypted or unencrypted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 4, 2008 at 6:41 am
Thanks. Much appreciated.
-- You can't be late until you show up.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply