SSIS Oracle Parameter Mapping

Question

When trying to enter a SQL query with parameters using the Oracle OLE DB provider I get the following error:

Parameters 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.
ADDITIONAL INFORMATION:
Provider cannot derive parameter information and SetParameterInfo has not been called. (Microsoft OLE DB Provider for Oracle)

I have tried following the suggestion here but don't quite understand what is required:Parameterized queries against Oracle

Any ideas?

Answer 1

Show us some code to help you find your mistake.

Answer 2

To expand on the link given in the question:

1. Create a package variable
2. Double click on the package variable name. (This allows you to access the properties of the variable)
3. Set the property 'EvaluateAsExpression' to true
4. Enter the query in the expression builder.
5. Set the OLE DB source query to SQL Command from Variable

The expression builder can dynamically create expressions using variable to create 'parametised queries'.
So the following 'normal' query:

select * from book where book.BOOK_ID = ?

Can be written in the expression builder as:

"select * from book where book.BOOK_ID = " + @[User::BookID]

You can then do null handling and data conversion using the expression builder.