Execute SQL Task and Parameter mapping

The EXECUTE SQL Task in SQL Server SSIS allows you to run sql sever TSQL script inlcuding Stored procedures. You can use the variables defined in the package to map to the parameters in the TSQL script. However, the mapping definition and TSQL script is different based on the SQL connection type you selected to use. The following is the summary:
 
Connection type

Parameter marker

Parameter name

Example SQL command

ADO

?

Param1, Param2, …

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

ADO.NET

@<parameter name>

@<parameter name>

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = @parmContactID

ODBC

?

1, 2, 3, …

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

EXCEL and OLE DB

?

0, 1, 2, 3, …

SELECT FirstName, LastName, Title FROM Person.Contact WHERE ContactID = ?

For those that parameter marker use "?", the mapping must be declared  in the order.  This makes those connection type inconvenient.

The best one to use is ADO.net. You use the real variable in your TSQL to define the map. e.g.:

In parameter mapping, you define:

Variable Name

Parameter Name

Direction

Data type

Parameter Length

User::DbName

@DBToBackup

Input

String

128

User::ReturnMsg

@Msg

Output

String

200

 Edit the SQL statement:
DECLARE  @BackupFile nvarchar(255)
SET @BackupFile
=N'C:\Test\'

BACKUP LOG @DBToBackup TO  DISK = @BackupFile  WITH NOFORMAT, INIT,  NAME = "Log backup", SKIP, REWIND, NOUNLOAD,  STATS = 10

SET @Msg=N'Log backup succeeded' 

You can see, you donot need to declare the variable mapped to the SSIS package variable in the SQL statement. You use the parameters wherever you need in the same way as local TSQL variables. The code is more readable than those marked by ? for parameters.

Some reference links:

How to: Map Query Parameters to Variables in an Execute SQL Task: http://technet.microsoft.com/en-us/library/ms140355.aspx
Discussion in a thread: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2546899&SiteID=17

 

 

 

Advertisements
This entry was posted in SQL Server SSIS. Bookmark the permalink.

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