3

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
Hadi
  • 36,233
  • 13
  • 65
  • 124
Rohit
  • 1,520
  • 2
  • 17
  • 36
  • I am trying to pass `DUBAI` using the `@p_cityId` parameter. check the package call query. – Rohit Jun 04 '19 at 13:10

1 Answers1

1

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
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    You were right, it turned out to be the single quote issue. the 2nd query worked just fine. Thanks so much for the help ;) – Rohit Jun 04 '19 at 14:02