DTSRun from xp_cmdshell

[capooti]

Hi guys

something strange is happening, I can't understand why.
If i launch my dts package from command promp, just like this:

dtsrun /SmyServer /UmyAccount /PmyPwd /NmyDTSPack

everything works fine. But if i try to launch this dts from a sp with:

master..xp_cmdshell '/SmyServer /UmyAccount /PmyPwd /NmyDTSPack'

then I get this error:

DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error String: Cannot open database requested in login 'MyDB'. Login fails.

My account is correctly set to execute xp_cmdshell, in fact i can, ie, execute: master..xp_cmdshell 'dir' (the account infact belongs to sa role)

What can it be? I am going to be crazy!!!

Thanks in advance for you help

 

[mapfax]

I had a similar problem.

Check your permission properties for xp_cmdShell in 'Extended Procedures' are set up correctly for your account.

Also you might want to try the /E switch (entrusted) instead of /U /P.

Sorry if this doesn't help but I've only just recently started using SQL.

 

[capooti]

well, mapfax, thanks for your help, but I solved.
It was a permission issues, but not conerning xp_cmdShell.
In DTS the destination is SQL Server, with login based on SQL Protection. I had it based on Windows protection. I thought when I call dtsrun, if i give username & password as parameter this would be the user that connect also to SQL Server destination in DTS. But it is not so!