Skip to content

SSIS 2008 Variables 101

March 25, 2010

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
3 Comments leave one →
  1. March 25, 2010 6:07 AM

    You can use SQL Server’s ‘map by parameter’ instead of ‘map by position’. I have an example below:

    exec dbo.usp_update_AncillaryData @invoice=?,@sellingcompany=?,@webunique=?,@Division=?

    You still need to track your variables in the order they appear in the call, so the first ? (@invoice=?) corresponds to parameter 0, but now it is easier to see what parameter is connected to what variable.

Trackbacks

  1. iAwaaz-News-by-People
  2. DotNetShoutout

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Archives



  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video


  • Software Blogs - BlogCatalog Blog Directory
  • Follow

    Get every new post delivered to your Inbox.

    Join 28 other followers

    %d bloggers like this: