Tuesday, November 24, 2009

Dynamic query as sql command for ADO NET / OleDb Source

A question on MSDN forum drew my attention towards a very common requirement which is made very tricky to do in SSIS.

A user may need to pass a dynamic query to OleDb source/ADO source something like
SELECT * FROM TblNm WHERE Col1 = @Var
In OleDb source user can create query like this in a variable and pass it to the OleDb Source but ADO NET source does not have Variable as Data Access Mode, so what do we do for ADO Net Source. Okay dont scratch around there is way out which works for both OleDb and ADO Net Source.

1. Create a variable


2. Create ADO NET Source or OleDb Source and select SQL Command as DATA ACCESS MODE and give a simple query like SELECT * FROM TblNm


3. In Control Flow Select DataFlow Task which contains this Source and from it Property Tab select Expressions and open Property Expressions Editor


4. Choose [ADO NET Source].[SQL Commnad] and make a expression like

5. Now run the package and Sql will be dynamically created and will get the value of variable.

5 comments:

Anonymous said...

Great Post , but it doesnt work for OLEDB.

Anonymous said...

Very useful!
For OLEDB use AccessMode "SQL Command with Variable".

Anonymous said...

How would you bypass the 4000 char. limitation when the query is long and only way you have is SSIS

Rahul Kumar said...

@Last comment: put the query in a variable and pass it to the SQL Command property similarly to STEP 4 above

Anonymous said...

can I to pass a variable table name ? is it possible ?

Post a Comment