Tag: ParameterName

I couldn’t find any step-by-step resources on this so I thought I’d blog it.  This is done in SQL Server 2008 R2. In this scenario I will use a SQL Statement to populate an SSIS variable, and then use that variable in another SQL Statement.

Create your variables

If the variables window isn’t visible already, from the menu select View –> Other Windows –> Variables.

This will bring up your variables window on the left hand side of your screen.  You should be able to tab between it and the Toolbox.  You may have to pin it to your screen first.

 

I have already created some variables in the screenshot above.  To create a variable, first decide on the scope of the variable, whether it is the entire package, a container or a specific task.  I normally scope all of my variables to the package, since you can’t see the variables in the window unless you have selected the scoped object.  I find it easier to manage variables having them all in one place.  I have not had any compelling reason not to do it this way, although I’m sure there are cases where you may want them scoped differently. 

Before creating the variable you must select the item for it’s scope. In this case select the canvas of the Control Flow which will permit you to scope the variable to the entire package.  Then create the variable by clicking on the Add Variable button at the top of the Variables window.

 

Give your variable a Name, a Data Type and a Value.  In this case we are working with EndDate, and although you would want to set it as Date, there are some compatibility issues when using the Date data type for a variable and then pulling into a SQL parameter.  I have found it is best to set the date as a string and then manipulate it from there.  Set your EndDate attributes as shown, with the Scope being the name of your package.

Populate an SSIS variable using a SQL statement

From the Toolbox, drag an Execute SQL Task onto your Control Flow.  Double click on it and set the following properties:

On the General tab:

1. Set the ResultSet to Single row
2. Select your Connection to the database where you will run your SQL Statement
3. Enter your SQL Statement

On the Result Set tab:

1. In the Result Name key in the name of the field being return in your SQL Statement.  In this case I named the field EndDate
2. In the Variable Name select the user variable you created.

Use the Variable in a SQL Statement

From the Toolbox, drag an Execute SQL Task onto your Control Flow. Double click on it and set the following properties:

On the General tab:

1. Select your Connection to the database where you will run your SQL Statement
2. Enter your SQL Statement, placing a ? where each variable is to be used.

In this case I need to add two Parameters to the Parameter Mapping tab, since I have used two question marks in my SQL Statement.  Even if you are using the same Variable multiple times, you still need to enter them separately on the Parameter Mapping tab, once for each time they appear in the SQL Statement.  This is the only way to identify which parameter is being used for which question mark.  Parameters are made much easier in 2012.

Here’s the tricky bit which isn’t at all self explanatory.

On the Parameter Mapping tab: 

1. Select the user variable in the Variable Name drop down which corresponded to the first variable (?) being used in your SQL Statement.
2. Set the Data Type to NVARCHAR, since we are using a string variable. (This is the spot where the data type compatibility didn’t mesh, which is why we opted for string).  
3. Set the ParameterName to 0.  This indicates the order in which the parameter is appearing in the SQL statement.  You can enter them in the pane in any order you want, but the ParameterName must contain numbers starting from 0 to however many parameters you have minus one.  So if you have two parameters you must have one ParameterName = 0 and one ParameterName = 1.  You can reverse the order, but they must both exist or the task will fail.