How to resolve SQL query parameters mapping issues while using Oracle OLE DB provider?

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

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.

Answer 2

If You use Data Flow Task and use OLE DB Source, and you need parameterize your Query :

1. Create Variable to save "Full" of Query statement : Right Click on blank area outside the package - and Click Variables :

Click Add Variables on Variables Window :

Make the name is SQL_DTFLOW_FULL or something that can you understand easily. The variable data type is STRING

2. Create Variable(s) to save your parameter(s).

i.e, the full of Query stamements is :

SELECT * FROM BOOK WHERE BOOK_ID = @BookID --@BookID is SQL Parameter

at the sample above, I have just one parameter : @BookID, so I need to create one variable to save my parameter. Add more variables depends on your Queries.

Give it name SQL_DTFLOW_BOOKID

The variable data type is STRING

So, you need make your SSIS neat, and the variables is sorted in understandable parts.

Try to make the variable name is SQL_{TASK NAME}_{VariableName}

3. Make Expression for SQL_DTFLOW_FULL variable, click on number 1, and start fill number 2. Make Your SQL Statements to be a correct SQL Statement using string block. String block usually using "Double Quote" at the beginning and the end. Concat the variables with the string block.

Click evaluate Expression, to showing result, to make sure your query is correct, copy-paste the Query result at SSMS.

Make sure by yourself that the variables is free from SQL Injection using your own logic. (Use your developer instinct)

4. Open the Data Flow Task, open the OLE DB Source Editor by double click the item.

• Select the Data Access Mode : SQL Command From Variable
• Select the Variable Name : SQL_DTFLOW_FULL
• Click Preview to make sure it works.

That is all, my way to prevent this SSIS failure case. Since I use this way, I never got that problem, you know, SSIS something is weird.

To change the variable value, set it before Data Flow Task, the SQL Result of SQL_DTFLOW_FULL variable will changed every you change your variable value.