SSIS: Mapping parameter inside of a Execute SQL Task, OLE DB Source Component or Datareader component

I have seen many posts recently in the SSIS MSDN forum from people having trouble mapping parameters inside of SQL statements in both Execute SQL tasks and OLE DB source components (using the ‘parameter’ settings of those 2 components). If you are one among those having that difficulty, let me show you an alternative method:

Let’s work under the assumption you already have a SSIS variable call @[User::MyParameter] that holds the value to be used as parameter in the SQL Statement.

• Create a variable of string type to hold the SQL Statement. Let’s say @[User::MyQuery]
• Change the property EvaluateAsExpression of @[User::MyQuery] variable to TRUE
• Click the ellipsis button in the Expression property of @[User::MyQuery] and create an expression like:

“Select X,Y,C from Table Where X=” + @[User::MyParameter]

• Click ‘Validate expression’ and make sure the query looks right. Tip: copy and paste this query inside of SSMS or other native querying tool to test it.
• Go to the Execute SQL task set the SQLSourceType to variable and assign the variable holding the SQL statement to the SourceVariable Property. If you are using an OLE DB Source Component instead, change the Data Access mode property to SQL Command from Variable and then choose the variable from the Variable name drop down list.

BTW, this approach also works for Datareader source components. In that case, after you have configured the variable @[User::MyQuery], go to the control flow and select the data flow having the datareader you want to affect. If you look into the properties of the data flow task, you should be able to see the expressions property. Expand that property and look for for a property that looks like [DataReaderName].[SqlCommand], and place an expression using the variable holding the query. The expression should look like: @[User::MyQuery]

I like this approach because I can see how the SQL statement is being evaluate in the expression editor, plus, in the case of the Execute SQL task, I don’t have to be aware of different ways of ‘naming’ and mapping the parameter based on the connection being used.

Notice: Unless you change the DelayValidation property to true, you may need to provide an initial value to the variable holding the parameter value, as the package could fail validation.

Update: See Darren's comments about some diffrences when using an expression Vs parameters: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2415979&SiteID=1