Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Running DTS Package From SQL Query
- Thread starter cmhunt
- Start date
I have executed packeages using xp_cmdShell. This lets your stored proc execute a Dos prompt command.
The script was a bit tricky though. Here is what i did to execute a package that writes a Table to an Excell spreadsheet.
1. Create Package
2. Save package on SQL Server
3. On the SQL server (terminal services for me) run the app "dtsrunui" -no quotes- from the dos prompt.
4. Browse to the package name in the "package name box".
5. Click "Advanced"
6. In the lower right click Generate Script.
7. Copy the entire script.
8. Paste the script into the string area after "exec xp_cmdShell" in your proc. It should look like this:
exec master..xp_cmdshell 'DTSRun /S "(local)" /N "NoThankYouLetters" /G "{CE6E21DA-9A8E-4D33-B857-B2E7A5843BD5}" /W "0" /E'
Make sure to include the ".." above it makes a difference.
The only other thing is setting up your security. Depending on your security and where you are calling the proc from you may need to set a proxy for executing the xp_cmdShell. I have forgotten what exactely I did for that but it is in BOL.
Hope this Helps
Bassguy
The script was a bit tricky though. Here is what i did to execute a package that writes a Table to an Excell spreadsheet.
1. Create Package
2. Save package on SQL Server
3. On the SQL server (terminal services for me) run the app "dtsrunui" -no quotes- from the dos prompt.
4. Browse to the package name in the "package name box".
5. Click "Advanced"
6. In the lower right click Generate Script.
7. Copy the entire script.
8. Paste the script into the string area after "exec xp_cmdShell" in your proc. It should look like this:
exec master..xp_cmdshell 'DTSRun /S "(local)" /N "NoThankYouLetters" /G "{CE6E21DA-9A8E-4D33-B857-B2E7A5843BD5}" /W "0" /E'
Make sure to include the ".." above it makes a difference.
The only other thing is setting up your security. Depending on your security and where you are calling the proc from you may need to set a proxy for executing the xp_cmdShell. I have forgotten what exactely I did for that but it is in BOL.
Hope this Helps
Bassguy
- Status
Similar threads
- Locked
- Question