Parameters cannot be extracted from the SQL command. The provider might not...

SSIS 2005 errors...glad many go away with SSIS 2008

It’s amazing to me on just how much better, nicer running, and less clunky SQL Server Integration Services (SSIS) 2008 is over its’ SQL Server 2005 counterpart. Every time I (have to) open it up – usually because client versioning and demands – I am reminded that, wow, wish I were using the latest version!  I haven’t used it in probably 9 months, so I’m developing in SSIS 2005 and I get an error trying to use variables in the OLEDB connection task. If I had read my earlier blog on the subject, I might have used expresssions rather than the parameter mapping function with an OLE source. But I wanted to try it this way. 

So I wire up, throw in a couple of question mark placeholders for them (?), and I get the following error:

I do what everyone else might do and Google! it, but find mostly nothing. So I set out to figure out a solution since I didn't see one on Google!, and I think I have one. What you need to do is the following:

• Paste your TSQL code in the OLEDB source editor.
• Include your place holders (question marks) where your variables will be placed.
• Click on the "Build Query" button. You'll see a familiar query builder box
• Click the arrow "Run" button
• The dialog box "Query Parameters" opens up; there should be a placeholder for each of your parameters
• Fill in a value for each.  The dates in mine can be any value that you choose.
• Click OK and your query will run.  Click OK again and you're back to the OLE DB Source Editor.
• Click your parameter button again. Success! Your variables are now mapped.

Lee

--------------------------

'Cause I'm a karate man! And a karate man bruises on the inside! They don't show their weakness. But you don't know that because you're a big Barry White looking ******! So get outta my face!

Reference

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).