SSIS: stored proc and ado.net

Here is another way to pass value out of stored procedure. This one is using ado.net as Connection type for EXECUTE SQL TASK.

variables are declared as follows:

Name: inp
Scope: Package1
Datatype : Int32
value: 1

Name: outp
Scope: Package1
Datatype : Int32
value: 0

Hers are the settings for the task.

Genereal tab:
    Resultset  = none
    Connection type = ado.net
    Connection = localhost.AdventureWorks
    SqlSourceType = DirectInput
    SQL Statement =

declare @o int
exec testproc @in=@i1,@out = @o output
select @o1 = @o

    IsQueryStoredProcedure = False

Parameter mapping tab
    Variable Name = User::outp
    Direction = Output
    Datatype = Int32
    Parameter Name = @o1

    Variable Name = User::inp
    Direction = Input
    Datatype = Int32
    Parameter Name = @i1

Resultset and Expressions tabs are left emtpy.

The stored proc looks like this..

create PROCEDURE [dbo].[testproc]
    @in int ,     @out int output
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    select 
        @out = count(*)
        from a_cust where customerid = @in
    set nocount off
END

You can connect the output of this sql execute task to script task

Script tab
    ReadOnlyVariables = User::outp

Inside the script add the following line right under neatch public sub main so the whole method looks like this..

    Public Sub Main()
        '
        ' Add your code here
        '
        MsgBox(Dts.Variables(0).Value)

        Dts.TaskResult = Dts.Results.Success
    End Sub

Feel free to change the values of inp in the variable section and check out the output via messagebox. The variable inp could be set using any mechanism such as a database table, file , or any other source.

I hope this helps answering some of the questions regarding stored procs and sql execute task.

Thanks

ZULFIQAR SYED

Comments

[Amol]

Great article ! Helped me. thanks