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

  • Article

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] + "'"

Note: The Integration Services expression syntax is similar to the syntax that the C and C# languages use. Expressions include elements such as identifiers (columns and variables), literals, operators, and functions. For more information, you can refer to:

https://msdn.microsoft.com/en-us/library/ms140206.aspx