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 :
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
- Display all data when Date Range inputs are null
- Snowflake join on mixed datatypes not returning results
- How to insert a value that contains an apostrophe (single quote)?
- Use Alias from one select to be used in another select
- SQL IN Clause 1000 item limit
- SQL Server: IF EXISTS massively slowing down a query
- SQL-Snowflake - How to count the days between dates in 2 or more rows with WHERE condition
- How to SELECT by MAX(date)?
- Presto sql: presto extract substring for the last occurrence of character in string
- SQL - Select first 10 rows only?
- Operator precedence of `EXISTS`
- mysql create view only if it doesn't already exist
- How to validate data is Hexadecimal in Presto
- Merge tables with different timestamps?
- Wordpress - generate random unique number using atomic operation and update database
- PostgreSQL ERROR: cannot accumulate arrays of different dimensionality
- PostgreSQL copy row minus a few columns
- SQL Server random selection
- SQL to show current PTID and Next_PTID based on my example
- How to add constraint if not exists
- Rolling sum that resets at a flag
- Why is LEFT INNER JOIN deprecated?
- Left outer join on multiple tables condition
- Will the CTE reflect changes in the data?
- incorrect results from MAX and ORDER BY
- Generate CREATE scripts for a list of indexes
- How to create a deterministic uniqueidentifier (GUID) from an integer value
- Why must some stored procedures in MSSQL be called with `master..` prepended while other can be called without it?
- Insert sorted rows in another table using SQL
- Is there a way to prevent ROW_NUMBER's ORDER BY from having to repeat the same thing?