Error when running SSIS package with parameter - dtexec

0

I am facing issues when I run SSIS package with parameters but without parameter it works.

What could be the problem?

Query without parameter-

Begin

declare @query varchar(4000) = 
'dtexec /Project C:\SSIS\DUTPackages.ispac /Package cityTransfer.dtsx /decrypt password1!'

exec xp_cmdshell @query

End

Query with parameter-

Begin
declare @p_cityId varchar(10) = '%'
declare @p_count varchar(10) = '-1'

declare @query varchar(4000) = 
'dtexec /Project C:\SSIS\DUTPackages.ispac /Package cityTransfer.dtsx /decrypt password1!'
+ ' /SET \Package.Variables[$Package::p_cityID];''' +  @p_cityId + ''''
+ ' /SET \Package.Variables[$Package::p_count];''' + @p_count + ''

exec xp_cmdshell @query

End

Error I get when running query with parameter-

[OLE DB Source [25]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E10.
[SSIS.Pipeline] Error: OLE DB Source failed the pre-execute phase and returned error code 0xC0202009.
sql-server
ssis
asked on Stack Overflow Sep 5, 2018 by Rohit

2 Answers

0

I think you might just need to add some double quotes and remove a couple single quotes. Try this:

Begin
declare @p_cityId varchar(10) = '%'
declare @p_count varchar(10) = '-1'

declare @query varchar(4000) = 
'dtexec /Project C:\SSIS\DUTPackages.ispac /Package cityTransfer.dtsx /decrypt password1!'
+ ' /SET "\Package.Variables[$Package::p_cityID];' +  @p_cityId + '"'
+ ' /SET "\Package.Variables[$Package::p_count];' + @p_count + '"'

exec xp_cmdshell @query

End
answered on Stack Overflow Sep 5, 2018 by digital.aaron
0

In some instances, only the parameters need to be quoted with ". So try - '" + @p_cityId + "' and same for @p_count

answered on Stack Overflow Sep 5, 2018 by Shiv Sidhu • edited Sep 6, 2018 by Shiv Sidhu

User contributions licensed under CC BY-SA 3.0