SSIS – Use block comments in your source query, and Parameter mapping

Hi, ever caught up in dilemma where you try map your query parameter to pass in your OLEDB source and it seems to go into abyss, meaning nothing happens or no rows get returned with no clue in the log messages.

Hmm… Just that recently happened with me where I was trying to pass in one of the query parameters to my OLE DB source as follows:

SELECT * FROM [SomeTable] --This is a query for sample demo
WHERE [SomeKey] > <<Parameter>>

And eventually with loads of debugging with multiple drivers and login permissions we ended up in the discovering that the issue was aroused due the use of single line comments in the SQL which I just copied and bumped off in the OLE DB source component.

This comment in the SQL query was causing this issue when I queries the SQL 2008 R2 from the SSIS ETL package.

So finally the solution with other references I found was to use the block comments in SQL while bumping off in any place in SSIS.

SELECT * FROM [SomeTable] /*This is a query for sample demo*/
WHERE [SomeKey] > <<Parameter>>

The following article proved me quite helpful for the same and thought this experience of mine helps others.

http://blog.hoegaerden.be/2012/12/05/ssis-ole-db-source-parameters-and-comments-a-dangerous-mix/

Hope it helps.