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

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

<p>I have tried following the suggestion here but don't quite understand what is required:<a href="http://microsoftdw.blogspot.com/2005/11/parameterized-queries-against-oracle.html" rel="noreferrer">Parameterized queries against Oracle</a></p>

<p>Any ideas?</p>

[{'answer_id': 59116, 'author': 'Rich Lawrence', 'author_id': 1281, 'author_profile': 'https://Stackoverflow.com/users/1281', 'pm_score': 5, 'selected': True, 'text': '<p>To expand on the link given in the question:</p>\n\n<ol>\n<li>Create a package variable</li>\n<li>Double click on the package variable name. (This allows you to access the properties of the variable)</li>\n<li>Set the property \'EvaluateAsExpression\' to true</li>\n<li>Enter the query in the expression builder.</li>\n<li>Set the OLE DB source query to SQL Command from Variable</li>\n</ol>\n\n<p>The expression builder can dynamically create expressions using variable to create \'parametised queries\'.<br>\nSo the following \'normal\' query:</p>\n\n<pre><code>select * from book where book.BOOK_ID = ?\n</code></pre>\n\n<p>Can be written in the expression builder as:</p>\n\n<pre><code>"select * from book where book.BOOK_ID = " + @[User::BookID]\n</code></pre>\n\n<p>You can then do null handling and data conversion using the expression builder.</p>\n'}, {'answer_id': 51440458, 'author': 'toha', 'author_id': 1084742, 'author_profile': 'https://Stackoverflow.com/users/1084742', 'pm_score': 1, 'selected': False, 'text': '<p>If You use Data Flow Task and use OLE DB Source, and you need parameterize your Query : </p>\n\n<ol>\n<li>Create Variable to save "Full" of Query statement : Right Click on blank area outside the package - and Click Variables : </li>\n</ol>\n\n<p><a href="https://i.stack.imgur.com/yLvNJ.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/yLvNJ.jpg" alt="Variables"></a></p>\n\n<p>Click Add Variables on Variables Window : </p>\n\n<p><a href="https://i.stack.imgur.com/AgX1w.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/AgX1w.jpg" alt="enter image description here"></a></p>\n\n<p>Make the name is <code>SQL_DTFLOW_FULL</code> or something that can you understand easily. The <code>variable data type</code> is <code>STRING</code></p>\n\n<ol start="2">\n<li>Create Variable(s) to save your parameter(s). </li>\n</ol>\n\n<p>i.e, the full of Query stamements is : </p>\n\n<pre><code>SELECT * FROM BOOK WHERE BOOK_ID = @BookID --@BookID is SQL Parameter\n</code></pre>\n\n<p>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. </p>\n\n<p><a href="https://i.stack.imgur.com/M4siL.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/M4siL.jpg" alt="ParamAdd"></a></p>\n\n<p>Give it name <code>SQL_DTFLOW_BOOKID</code></p>\n\n<p>The <code>variable data type</code> is <code>STRING</code></p>\n\n<p>So, you need make your SSIS neat, and the variables is sorted in understandable parts.</p>\n\n<p>Try to make the variable name is <code>SQL_{TASK NAME}_{VariableName}</code></p>\n\n<ol start="3">\n<li>Make Expression for <code>SQL_DTFLOW_FULL</code> 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.</li>\n</ol>\n\n<p><a href="https://i.stack.imgur.com/5UEVk.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5UEVk.jpg" alt="Expression"></a></p>\n\n<p>Click evaluate Expression, to showing result, to make sure your query is correct, copy-paste the Query result at SSMS. </p>\n\n<p>Make sure by yourself that the variables is free from SQL Injection using your own logic. (Use your developer instinct)</p>\n\n<ol start="4">\n<li>Open the Data Flow Task, open the OLE DB Source Editor by double click the item.</li>\n</ol>\n\n<p><a href="https://i.stack.imgur.com/Vb3tM.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Vb3tM.jpg" alt="Data Flow"></a></p>\n\n<ul>\n<li>Select the Data Access Mode : <code>SQL Command From Variable</code></li>\n<li>Select the Variable Name : <code>SQL_DTFLOW_FULL</code></li>\n<li>Click Preview to make sure it works.</li>\n</ul>\n\n<p>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.</p>\n\n<p>To change the variable value, set it before Data Flow Task, the SQL Result of <code>SQL_DTFLOW_FULL</code> variable will changed every you change your variable value. </p>\n'}, {'answer_id': 52480280, 'author': 'PanLondon', 'author_id': 8517285, 'author_profile': 'https://Stackoverflow.com/users/8517285', 'pm_score': 1, 'selected': False, 'text': '<p>In my case the issue was that i had comments within the sql in the normal form of /* */ and i also had column aliases as "Column name" instead of [Column Name]. </p>\n\n<p>Once i removed them it works. </p>\n\n<p>Also try to have your parameter ? statement within the WHERE clause and not within the JOINS, that was part of the issue too.</p>\n'}]
['https://Stackoverflow.com/questions/58540', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1281/']