Use xm_cmdshell to execute a SSIS package and pass a variable

Question

Use xm_cmdshell to execute a SSIS package and pass a variable

mz1derful

Ten Centuries

Points: 1234
More actions
January 25, 2011 at 6:35 am

#233399

the following code produces error 'C:\program' is not recognized as an internal or external command,
operable program or batch file'. If I comment out the full path line to dtexec and uncomment the other line I get dtexec versioning errors. What am I doing wrong? Thanks.
DECLARE @CompanyAccountNoVARCHAR(10)
DECLARE @cmd varchar(1000)
DECLARE @ssispath varchar(1000)
set @ssispath = 'C:\program files\Microsoft SQL Server\100\DTS\Packages\SyncOneCompany.dtsx'
SET @CompanyAccountNo = '2575052'
--select @cmd = 'dtexec /F "' + @ssispath + '"'
select @cmd = 'C:\program files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe /F "' + @ssispath + '"'
PRINT @cmd
select @cmd = @cmd + ' /SET \Package.Variables[User::CompanyAccNo].Properties[Value];"' + @CompanyAccountNo + '"'
exec master..xp_cmdshell @cmd

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sriram-288748 SSCrazy Points: 2874 More actions · Answer 1

There is a space in the path of the directory which is the cause for the issue. Get the directory path's in double quotes and run, that would resolve the problem

Sriram

mz1derful Ten Centuries Points: 1234 More actions · Answer 2

mz1derful

Ten Centuries

Points: 1234

January 25, 2011 at 6:59 am

#1277815

Thanks but that didn't fix the problem.

Sriram-288748 SSCrazy Points: 2874 More actions · Answer 3

Hi,

Check the below code.

DECLARE @CompanyAccountNoVARCHAR(10)

DECLARE @cmd varchar(1000)

DECLARE @ssispath varchar(1000)

set @ssispath = '"C:\program files\Microsoft SQL Server\100\DTS\Packages\SyncOneCompany.dtsx"'

SET @CompanyAccountNo = '2575052'

--select @cmd = 'dtexec /F "' + @ssispath + '"'

select @cmd = '"C:\program files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /F ' + @ssispath + ''

select @cmd = @cmd + ' /SET \Package.Variables[User::CompanyAccNo].Properties[Value];"' + @CompanyAccountNo + '"'

exec master..xp_cmdshell @cmd

This should work, if you still get any error, please post the error.

Sriram

mz1derful Ten Centuries Points: 1234 More actions · Answer 4

Unfortunately I still get the same error: 'C:\program' is not recognized as an internal or external command.

mz1derful Ten Centuries Points: 1234 More actions · Answer 5

Actually I got a little further but now am getting this error when running dtexec from xp_cmdshell:

Error: 2011-01-26 15:14:52.01

Code: 0xC0047062

Source: Data Flow Task ADO NET Source [1]

Description: Microsoft.SqlServer.Dts.Runtime.DtsCouldNotCreateManagedConnectionException: Could not create a managed connection manager.

at Microsoft.SqlServer.Dts.Runtime.ManagedHelper.GetManagedConnection(String assemblyQualifiedName, String connStr, Object transaction)

at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager100.AcquireConnection(Object pTransaction)

at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(Object transaction)

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections(IDTSManagedComponentWrapper100 wrapper, Object transaction)

End Error

Error: 2011-01-26 15:14:52.01

Code: 0xC0047017