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.
Great Post , but it doesnt work for OLEDB.
ReplyDeleteVery useful!
ReplyDeleteFor OLEDB use AccessMode "SQL Command with Variable".
How would you bypass the 4000 char. limitation when the query is long and only way you have is SSIS
ReplyDelete@Last comment: put the query in a variable and pass it to the SQL Command property similarly to STEP 4 above
ReplyDeletecan I to pass a variable table name ? is it possible ?
ReplyDeleteIt 's an amazing and Very Nice,Thanks for sharing
ReplyDeleteDot Net Online Course Hyderabad
It was really a nice post and i was really impressed by reading this Data Science online Training Hyderabad
ReplyDelete