SQL Server 2005 Books Online and MSDN have provided many references on the new features of SSIS; however few references demonstrate how to use parameters in Execute SQL Task. I would like to share my experiences here to enlighten those interested and save them time.

 

This chapter introduces three examples explaining the usage of OLE DB parameters in Execute SQL Task.

 

1.   Preparation

SQL Server 2000: My2KServer

Database: TestDB

Table: PCTEST

CREATE TABLE [dbo].[PCTEST] (

      [id] [int] IDENTITY (1, 1) NOT NULL ,

      [pid] [int] NOT NULL ,

      [sname] [char] (10)

) ON [PRIMARY]

GO

 

2.   Examples

2.1 Insert an item into the table with parameters.

1) Create an OLE DB Connection "OLE DB Connection: My2KServer.TestDB"

2) Drag a "Execute SQL Task" to the "Control Flow" panel from Toolbox;

3) Double click the task, set the properties as following:

    [General]  

    Connection: My2KServer.Northwind

    SQLSourceType: Direct Input

    SQLStatement:  INSERT INTO PCTEST (pid, sname) VALUES (?,?)

    BypassPrepare: False

    ResultSet: None   

Configuration at General Tab 

 

 

 

[pic] 1. Configuration at General Tab

    

[Parameter Mapping]

    Create two variables User:pid and User::sname.

    User::pid

    ---------------------------------

    Container: Execute SQL Task

    Name: pid

    Namespace: User

    Value Type: Int32

    Value: 10

Set variable for pid 

 

[pic] 2. Set variable for pid

 

    User::sname

    -------------------------------

    Container: Execute SQL Task

    Name: sname

    Namespace: User

    Value Type: String

    Value: Charles

Set variable for sname 

 

[pic] 3. Set variable for sname

 

    [Variable Name][Direction][DataType][Parameter Name]

    User::pid             Input         LONG           0

    User::sname      Input         VARCHAR  1

 

 

 

[pic] 4. parameter mapping

 

4) Save the package and execute the task.

 

 [pic] 5. A successful execution

           The result:

 

 

[pic] 6. Result in SQL Server

2.2 Get an output parameter value from stored procedure

1) Create a procedure in the database 

create procedure proc_insertPCTEST

(

@pid int,

@sname varchar(20),

@id int output

)

AS

INSERT INTO PCTEST (pid, sname) VALUES (@pid,@sname)

SELECT @id=SCOPE_IDENTITY()

 

2) In SSIS development environment, double click the "Execute SQL Task", change the SQLStatement "EXEC proc_insertPCTEST ?,?,? output", switch to the Parameter Mapping, add a variable:

User::id

==============

Container: Package

Name: id

Namespace: User

Value type: Int32

Value: -1

 

Direction: Output

Data Type: LONG

Parameter Name: 2

===============

        3) Create a second "Execute SQL Task" named "Execute SQL Task 1" and

            set the first "Execute SQL Task" as its input. Double click the "Execute

            SQL Task 1", set the OLE DB Connection and enter the SQL statement:

 "UPDATE PCTEST SET sname='TEST' WHERE id=?"

 

 

 

[pic] 7. Combine two “Execute SQL Task” into one package

 

Click Parameter Mapping, and add the User::id as its input parameter:

Variable Name: User::id

Direction: Input

Data Type: LONG

Parameter Name: 0

 

4) Save the package and execute it.

You will find that the new inserted value has been changed.

 

2.3 Get the return value from a stored procedure

         1) On the basis of 2.2, create a stored procedure:

create procedure proc_getid

as

declare @intval int

set @intval = (select max(id) as maxid from PCTEST )

RETURN @intval

 

2) Set the task properties in SSIS development environment:

Execute SQL Task Properties:

========================

[General]

SQL Statement: exec ? = proc_PCTEST

ResultSet: None

[Parameter Mapping]

User::id       ReturnValue       LONG    0

 

Execute SQL Task1 Properties:

=======================

[General]

SQL Statement: Update PCTEST Set sname='TEST1' WHERE id=?

ResultSet: None

[Parameter Mapping]

User::id Input    LONG    0

 

         3) Save the package and execute it.

The column ‘sname’ value will be changed to TEST1.

 

3.   Conclusion

The three examples demonstrate common usages of passing parameters in SSIS packages. In SSIS Development Studio, it is required to use the character ‘?’ to represent parameters of ADO/ODBC/OLE DB; though Package scope or Task scope variables can be defined and mapping to parameters, the parameters names must be numbers like 0,1,etc which represents the parameters sequentially. However for ADO.NET connections, it is required to use @<parameter> as the parameter name.

 

4.   Reference

Execute SQL Task 

http://msdn2.microsoft.com/en-us/library/ms141003.aspx

Integration Services Variables 

http://msdn2.microsoft.com/en-us/library/ms141085.aspx

How to: Add a Variable to a Package Using the Variables Window 

http://msdn2.microsoft.com/en-us/library/ms141670.aspx

OLE DB Source 

http://msdn2.microsoft.com/en-us/library/ms141696.aspx