Hey, Trouble in mapping parameter or vaiable? This should be fairly a simple thing to do, why the SSIS Designer is showing so many issues ?. If you are one among those asking that question, hope this post will help you.
This post is about mapping a variable in any SSIS data elements, i.e ADO.NET Data Source, SQL tasks ,OLE DB source etc.
To use expressions for an ADO.NET source in a data flow, here are a few simple steps:
Step 1 – Go to the Data Flow Task Properties window
On the Control Flow tab (not the Data Flow tab), right-click the Data Flow Task you are working on and select Properties.
Step 2 – Open the Property Expression Editor
In the Properties window, find a property called Expression and a small button next to it. Click the button to open the Expression Editor.
Step 3 – Open the Expression Builder for SQL Command
In the Property list, select [ADO NET Source].[SqlComamnd] and click the button next to the Expression column to open Expression Builder.
Step 4 – Write query with variables
Start to write your query with variable names and click the Evaluate Expression button to test the expression.
Example : “Select col1,col2,col3 from Table Where id=” + @[User::ParamId]
You may get an error “the data types dt_wstr and dt_i4 are incompatible for binary operator +” , if an incompatibledata type used for concatenation.
Either cast the variable a string or convert variable type to String for concatenation operation.
“Select col1,col2,col3 from Table Where id=” + (DT_WSTR,10) @[User::ParamId]
By default variable will be created as Boolean, to convert check variable type expand the variable explorer window. Visual Studio. Menu SSIS >> Variables
Note: You can change the DelayValidation property to true, so you do not need to provide an initial value to the variable holding the parameter value, otherwise the package could fail validation.
How to: Map Query Parameters to Variables in an Execute SQL Task
Difference when using an expression Vs Parameters:
Thanks for reading. Hope this help