set package variable to a value from database

Question

I have an SSIS project in which I would like to set one of my package variables to the maximum value of some column in one of my database tables. Suppose the package variable is called var.

my SQL would look like:

SET var = (SELECT max(some_column) from SomeTable)

How does this fit into an SSIS Control Flow /Data Flow? What type of task would this SQL go into? Can I even access a package variable via SQL?

Answer 1

You can use an Execute Sql Task to accomplish this. The syntax will vary depending on the type of database connection you are using (OleDb or .Net).

If you are using an OleDb connection manager, your sql statement will look something like this:

SELECT ? = MAX(some_column) FROM some_table

Then select the Parameter Mapping tab to map your parameter/variable. Select your package variable from the dropdown for the "Variable Name" field. Set the Direction to Output and set the Parameter Name to 0. (variables on an OleDb connection are mapped in sequential order starting from 0)

If you are using a .Net connection manager, you can use the more familiar @variableName for the parameter place holder in your sql statement, as well as the Parameter Name in the Execute SQL task.

Edit: In response to your comment. See a few screenshots pulled directly from one of my projects. Please compare what you are doing to make sure we are on the same page.

In this shot, note the connection type of the Execute Sql Task and note the last statement of the Sql Statement where I am selecting a parameter to be equal to a sql function.

In this next shot, see how I am configuring the second parameter, making sure that the parameter direction is set to output. The parameter is named 1 as it is the second parameter/parameter holder in the sql statement.