SSIS 2008 Variables 101

Sometimes it is easy to get confused with Parameterized Stored Procedures in Execute SQL Task in SSIS specially when dealing with multiple variables.  In SQL Server 2000 DTS, executing multi-parameterized Stored Procedure is less confusing. You just have to specify the variable names in the statement to map the placeholders (?) with the Global Variables like this:

EXEC sp_myParameterizedStoredProc @myVariable1 = ?, @myVariable2 = ?

As you can see it is easy to check if you are mapping the right variable with the correct index. The syntax in SQL Server 2008 SSIS is a little bit different:

EXEC sp_myParameterizedStoredProc ?, ?

You notice that you don’t specify the variable names but only put the placeholders for the variables. The variable placeholders will be mapped with the package variables like this:

But suppose the Stored Procedure you are executing inside your SQL Task looks like this (pay attention to the “order” of the variables):

CREATE PROCEDURE sp_myParameterizedStoredProc
@myVariable2 INT,
@myVariable1 INT
AS
SELECT ...

Because you’re not specifying what variable name to map with which placeholder, the ?, ? in your EXEC statement will map to the stored procedures as @myVariable2 and @myVariable1 respectively. How about if your intention really is to map ?, ? to @myVariable1 and @myVariable2 in that order- not the other way around? Gotcha! In this example, both of the variables are INT. The task will execute successfully despite the mis-mapping of placeholders with their intended variables.

The point is, your placeholders should match the variable order of the actual stored procedure, not the package’s. In the example, your task variable should be like this:

About these ads