SSIS Packages fails using cmdshell

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

SSIS Packages fails using cmdshell

Author

Topic

sqldba20
Posting Yak Master

183 Posts

Posted - 2008-03-17 : 12:31:08

Folks:

When I run this from Windows Command Prompt (DOS) it runs successfully but when run from SQL Management Studio (Query nalyser) using xp_cmdshell it fails. Any help is appreciated.

This runs with SUCCESS.
-------------------------
Dtexec /F "E:\PackSSIS_Total1.dtsx" /SET \package.variables[User::strStatusEmailTo].Value;"xyz@abc.com" /SET \package.variables[User::strFileSystemRoot].Value;"Z:" /SET \package.variables[User::strServerName_Admin].Value;"pacers"

This FAILS.
------------
exec master..xp_cmdshell 'Dtexec /F "E:\PackSSIS_Total1.dtsx" /SET \package.variables[User::strStatusEmailTo].Value;"xyz@abc.com" /SET \package.variables[User::strFileSystemRoot].Value;"Z:" /SET \package.variables[User::strServerName_Admin].Value;"pacers"'

ERROR:
------
End Progress
Progress: 2008-03-17 12:28:53.13
Source: DFT Format Log Info
Validating: 100% complete
End Progress
Progress: 2008-03-17 12:28:53.15
Source: DFT Table Extract to File
Validating: 0% complete
End Progress
Error: 2008-03-17 12:28:53.15
Code: 0xC0202009
Source: MyPackage Connection manager "encore_cm"
Description: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host.
".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.".
End Error
Error: 2008-03-17 12:28:53.18
Code: 0xC020801C
Source: DFT Table Extract to File OLE_SRC tblFileConfigMaster [1]
Description: The AcquireConnection method call to the connection manager "encore_cm" failed with error code 0xC0202009.
End Error
Error: 2008-03-17 12:28:53.20
Code: 0xC0047017
Source: DFT Table Extract to File DTS.Pipeline
Description: component "OLE_SRC tblFileConfigMaster" (1) failed validation and returned error code 0xC020801C.
End Error

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-17 : 12:35:59

Did you enable XP_Cmdshell in SQL surface area config?