January 10, 2012 at 3:19 am
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...
AND:
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
Any suggestions please?
Thanks
January 10, 2012 at 11:23 am
Hi
It's not that easy.
You have to install Integration Services on your Production server (that must be done on your dev server);
It will install the required DTExec and the engine that will process your dtsx package.
January 11, 2012 at 12:54 am
Ok, Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply