MS SQL Server DTS - Issue calling DTS from stored procedure

Author

Issue calling DTS from stored procedure

khoy@dmt.ca

2006-03-21, 11:31 am

I have a stored procedure that executes xp_cmdshell and calls DTSRun.
Up until recently this worked fine. However, something has happened and
I can no longer use the stored procedure to start the package.

If I copy the line that is passed to xp_cmdshell and paste it into the
cmd window then the package runs. I am also able to run the package
from within Enterprise Manager. However, when I run the package through
Query Analyzer nothing happens. It does actually get into the package
and does some work inside, but it will not actually run the entire
package (which calls 2 other packages).

When running through QA I get:
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSActiveScr
iptTask_1
DTSRun OnFinish: DTSStep_DTSActiveScr
iptTask_1
DTSRun OnStart: DTSStep_DTSExecuteSQ
LTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQ
LTask_1
DTSRun OnStart: DTSStep_DTSActiveScr
iptTask_2
DTSRun OnFinish: DTSStep_DTSActiveScr
iptTask_2
DTSRun OnStart: DTSStep_DTSActiveScr
iptTask_4
DTSRun OnFinish: DTSStep_DTSActiveScr
iptTask_4
DTSRun OnStart: DTSStep_DTSActiveScr
iptTask_3
DTSRun OnFinish: DTSStep_DTSActiveScr
iptTask_3
DTSRun: Package execution complete.
NULL

but the data that should be placed in my database is not there, only
when I run the package through the command line or EM.

This was working at some point in the past but just stopped working. I
have looked through the posts and I am unable to find any information
to help me. The closest I can come to is a security problem but I do
not understand enough about SQL Server security to know what people are
talking about.

Any help would be greatly appreciated.

Thank you!

Darren Green

2006-03-23, 7:40 am

Lookup xp_cmdshell in Books Online. What you need to understand is the
security context used to execute the package, which is the windows account
used for xp_cmdshell. This will be the same account as used for the SQL
Server service, see EM or Services in Control Panel.

Does that user have rights to do what it needs?

--
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com

"khoy@dmt.ca" wrote:

> I have a stored procedure that executes xp_cmdshell and calls DTSRun.
> Up until recently this worked fine. However, something has happened and
> I can no longer use the stored procedure to start the package.
>
> If I copy the line that is passed to xp_cmdshell and paste it into the
> cmd window then the package runs. I am also able to run the package
> from within Enterprise Manager. However, when I run the package through
> Query Analyzer nothing happens. It does actually get into the package
> and does some work inside, but it will not actually run the entire
> package (which calls 2 other packages).
>
> When running through QA I get:
> DTSRun: Loading...
> DTSRun: Executing...
> DTSRun OnStart: DTSStep_DTSActiveScr
iptTask_1
> DTSRun OnFinish: DTSStep_DTSActiveScr
iptTask_1
> DTSRun OnStart: DTSStep_DTSExecuteSQ
LTask_1
> DTSRun OnFinish: DTSStep_DTSExecuteSQ
LTask_1
> DTSRun OnStart: DTSStep_DTSActiveScr
iptTask_2
> DTSRun OnFinish: DTSStep_DTSActiveScr
iptTask_2
> DTSRun OnStart: DTSStep_DTSActiveScr
iptTask_4
> DTSRun OnFinish: DTSStep_DTSActiveScr
iptTask_4
> DTSRun OnStart: DTSStep_DTSActiveScr
iptTask_3
> DTSRun OnFinish: DTSStep_DTSActiveScr
iptTask_3
> DTSRun: Package execution complete.
> NULL
>
> but the data that should be placed in my database is not there, only
> when I run the package through the command line or EM.
>
> This was working at some point in the past but just stopped working. I
> have looked through the posts and I am unable to find any information
> to help me. The closest I can come to is a security problem but I do
> not understand enough about SQL Server security to know what people are
> talking about.
>
> Any help would be greatly appreciated.
>
> Thank you!
>
>

Sponsored Links