Wednesday, February 11, 2015

How do I use parameters in an SSIS ADO NET source?

How do I use parameters in an SSIS ADO NET source -

Question
 
When I use SQL Command Text, there is a Parameters button in OLE DB Source. But now I want to use ADO NET Source in Data Flow and I cannot find such button in ADO NET source. How do I pass parameters to an ADO NET Source?
 
Answer
 
You can use variables together with Expression to pass the parameters:
1)   Switch to Control Flow, click the Data Flow Task.
2)   In the Properties window, notice a property called Expression and a small button next to it. Click the button to open the Expression Editor.
3)   In the Property list, select [The name of ADO NET Source].[SqlComamnd] and click the button under expression column to open Expression Builder.
4)   Write the query with variable names and click the Evaluate Expression button to test the expression. For example:
"SELECT * FROM EMPLOYEES WHERE FNAME='" +  @[User::FName] + "'"
 
 

No comments:

Post a Comment