Search code examples
sqlsql-serveroraclessisdtexec

SSIS Command line - ORA-00907: missing right parenthesis

I am trying to run a SSIS package from command line but I keep getting this

"ORA-00907: missing right parenthesis".

The package runs fine from the SSDT and the query I am using is also a simple one. I do try to pass values to the query at runtime.

Variable Declaration :

enter image description here

My Query using expression : 

 "SELECT  country_id, city_id, name FROM cities where instr('" +  @[User::p_cityID]  + "' ||  ',', city_id || ',') > " +  @[User::p_count]

Final Query : (runs fine)

SELECT  country_id, city_id, name FROM cities where instr('DUBAI' ||  ',', city_id || ',') > 0

Package call :

Begin
declare @p_cityId varchar(10) = 'DUBAI'
declare @p_count varchar(10) = '0'

declare @query varchar(4000) = 
'"C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\DTExec.exe" /FILE C:\SSIS\pmtCity.dtsx /decrypt <mypass> /reporting V > C:\SSIS\log\citylog_'+
(replace(replace(replace(replace(convert(varchar(23), getdate(), 121),'-',''),':',''),' ',''),'.','')) +'.txt' 
+ ' /SET \Package.Variables[p_cityID].Value;''' +  @p_cityId + ''''
+ ' /SET \Package.Variables[p_count].Value;''' + @p_count + ''''

print @query
exec xp_cmdshell @query

End
Solution

  • I think you have a single/double quotes issue. Try the following command:

    Begin
declare @p_cityId varchar(10) = 'DUBAI'
declare @p_count varchar(10) = '0'

declare @query varchar(4000) = 
'"C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\DTExec.exe" /FILE C:\SSIS\pmtCity.dtsx /decrypt <mypass> /reporting V > C:\SSIS\log\citylog_'+
(replace(replace(replace(replace(convert(varchar(23), getdate(), 121),'-',''),':',''),' ',''),'.','')) +'.txt' 
+ ' /SET \Package.Variables[p_cityID].Value;"' +  @p_cityId + '"'
+ ' /SET \Package.Variables[p_count].Value;"' + @p_count + '"'

print @query
exec xp_cmdshell @query

End

    Also try removing quotes around variables values:

    Begin
declare @p_cityId varchar(10) = 'DUBAI'
declare @p_count varchar(10) = '0'

declare @query varchar(4000) = 
'"C:\Program Files (x86)\Microsoft SQL Server\140\DTS\Binn\DTExec.exe" /FILE C:\SSIS\pmtCity.dtsx /decrypt <mypass> /reporting V > C:\SSIS\log\citylog_'+
(replace(replace(replace(replace(convert(varchar(23), getdate(), 121),'-',''),':',''),' ',''),'.','')) +'.txt' 
+ ' /SET \Package.Variables[p_cityID].Value;' +  @p_cityId 
+ ' /SET \Package.Variables[p_count].Value;' + @p_count 

print @query
exec xp_cmdshell @query

End