execute ssis using xp_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 2008 Forums

SSIS and Import/Export (2008)

execute ssis using xp_cmdshell

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-10 : 05:18:49

Executing ssis package using xp_cmdshell
As you see below, executing this sql in Development sqlserver is fine and works correctly.
But as you see further down, there is a problem when executing this sql in Production sql server.
Do you see why I get the mentioned error?

--Development Server
-------------------

declare @param varchar(4000)

set @param =
C:\"Program Files (x86)"\"Microsoft SQL Server"\100\DTS\Binn\DTExec.exe /f "\\DEV_ServerName\D$\Users\MyUsername\My Development\SSIS\CompanySSIS\CompanySSIS\FXRates.dtsx" /SET "\Package.Variables[User::MainPath].Value";"\cgi" /SET "\Package.Variables[User::ServerName].Value";"\\DEV_ServerName" /SET "\Package.Variables[User::FileName].Value";"FX Rates.csv" /SET "\Package.Variables[User::MailTo].Value";"MyUsername@Company.com" /SET "\Package.Connections[DBName].Properties[InitialCatalog]";"DevDatabaseName" /SET "\Package.Connections[DBName].Properties[ServerName]";"DEV_ServerName"

EXEC master..xp_cmdshell @param

--Production Server
-----------------
--Error message: "The system cannot find the path specified."

declare @param varchar(4000)

set @param =
C:\"Program Files (x86)"\"Microsoft SQL Server"\100\DTS\Binn\DTExec.exe /f "\\PROD_ServerName\CGI\SSIS\Packages\FXRates.dtsx" /SET "\Package.Variables[User::MainPath].Value";"\cgi" /SET "\Package.Variables[User::ServerName].Value";"\\PROD_ServerName" /SET "\Package.Variables[User::FileName].Value";"FX Rates.csv" /SET "\Package.Variables[User::MailTo].Value";"MyUsername@Company.com" /SET "\Package.Connections[DBName].Properties[InitialCatalog]";"ProdDatabaseName" /SET "\Package.Connections[DBName].Properties[ServerName]";"PROD_ServerName"

EXEC master..xp_cmdshell @param

NOTES
-----
In Dev sql server, I am using the full path i.e. \\DEV_ServerName\D$\Users...
In Prod sql server, I am using the following maopped path i.e. \\PROD_ServerName\CGI...

Any suggestions please?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 06:43:23

is the path accessible otherwise? can you try pasting it in run and see if you can browse to the path?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-10 : 07:01:44

Yes, indeed.
In the run command of the Dev server, I can type the follwoing which points to the Prod machine and works.
\\PROD_ServerName\CGI\SSIS\Packages\FXRates.dtsx

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-10 : 07:51:04

After doing alot of searching on th eproduction, it seems that the DTEXEC.exe is NOT present

C:\"Program Files (x86)"\"Microsoft SQL Server"\100\DTS\Binn\DTExec.exe
Whereas this file does exist in Dev.
May be the first thing to do is to add the dtexec.exe into tis folder in Prod? If so how as this file does not exist any where on the prod server.
Thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-10 : 08:34:55

Found the dtexec which is in a different path.
Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources