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.

Fail to save package file : class not registered

Problem:

Sometime a user may encounter an error like below while creating a SSIS package from BIDS.

Error creating package

------------------------------
ADDITIONAL INFORMATION:

Failed to save package file "C:\Documents and Settings\UserName\Local Settings\Temp\tmp87.tmp" with error 0x80040154 "Class not registered".

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

Solution:

To solve this try to register below DLLs by typing below commands in CMD promopt:-

regsvr32 msxml3.dll
regsvr32 msxml4.dll
regsvr32 msxml6.dll