Amazing SSIS: Parameters in Execute SQL Task


 


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


 


Comments (10)

  1. D Coates says:

    Thanks alot! I was stuck on this issue for 8 hours!

  2. ColinBowern says:

    Thanks for the simple tutorial.  I find that the documentation sometimes lacks the simple level of verbosity that would be helpful.  Instead they ramble off a lot of if this, then that, and it’s hard to follow.  This was what created the "ah-ha!" for me.

  3. badwomble says:

    They would be more amazing if they supported input/output parameters. (SQL2005 stored procs)

    If I specify a parameter as output, null is passed to the sql task. Unlike a stored proc that accepts values in a parameter, whether output or not, SSIS output parameters are output only. This means having to specify parameters twice in SQL, once for input and one for ouput. Progress ?

    Why are SSIS interfaces to primitive?

  4. kunalbhattacharya says:

    yeah, I’m facing the same problem.The only difference here is that the stored proc cannot be changed to make the same parameter as input and output

    I don’t have the rights to access

    Is there a workaround ?

  5. Steve Nolan says:

    This is an excellent basis from which to investigate more complex Stored Procedure usage within SSIS.  

    As a ‘newbie’ to SSIS, I’ve searched in vain on the web for such a simple, well laid out solution

    Thank You

  6. Jerry Higgins says:

    This is great!  It’s the ONLY thing I’ve found on the internet anywhere that really explains this.  Thank you so much.

  7. Anish says:

    Problem:

    With varchar type of data it has issues. it puts a line feed at the end of a string

    e.g where cname =?

    Lets assume i pass ‘abc’. What it does is it puts a line feed at the end

    i.e. where cname =’abc[line feed character]’

    Even ltrim(rtrim(?)) cannot remove it

    any solution

    Am using OLEDB to connect to oracle through ssis and run a update sql

  8. ob says:

    it is easy to pass integers, what about strings?

    select * from My Table where name = ?

    will not do it

  9. vinod says:

    what is the best way to log execution details into a table ETL_Audit_Log as long as usage of INSERT statement holds good with sql INSERT or sp with INSERT

    e.g, direct SQL in execute sql task : INSERT into ETL_Audit_Log(P_Name, PStartDate) values(@pack_name, @start_time)

    or

    create proc log_details (@pack_name varchar(100), @start_time datetime) as begin  INSERT into ETL_Audit_Log(P_Name, PStartDate) select @pack_name, @start_time end

  10. thanks for providing collective information