January 25, 2007 at 2:41 am
Hi
I am getting error while passing parameter in SSIS package
I am writing following query in sql command
select * from Mytable where empid in (select empid from testtable where empid = ?)
Error:
Parameter cannot be extracted from the SQL Command. The Provider might not help to parse parameter information from the command. In that case, use the "SQL Command from variable" access mode, in which the entire SQL command is stored in a variable.
can any body help me out
Thanks
January 26, 2007 at 8:33 am
I only had success with parameters passed to SQL Execute Task when I pass the value (?) to a stored proc....
I assume you defined the parameter correctly.
Also, you pass ? when the connection is OLEDB, ODBC connections are passed differently...
January 26, 2007 at 9:00 am
What is the data provider of your source? The error is indicating that you are using a provider that does not support query parameters.
One good way around this is what the error message is telling you can be done. Use two variables.
Create one variable just like the one you have - I am calling it Variable1.
Your new variable will be a string variable. Set the variable to evaluate as an expression and make the expression something like: "SELECT * FROM TableA WHERE Col = " + @[User::Variable1]
You can then set the execute task to use a variable as the SQL statement and use your new string variable as the statement. This will make SSIS do the work of creating the entire SQL statement rather than relying on your data provider.
Watch out for variable scope and read up on when variable expressions are evaluated and you will be fine.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply