SSIS Variable

  • I have been trying this all day but don’t seem to get to work. What I am trying to do is to capture the number of rows deleted/inserted/updated by a stored procedure. I have two proc to do these. I captured @@rowcount values to an integer output parameter in store procedure. I then call this sp from execute sql task and map the parameter to a package variable as output like this EXEC ? = dbo. procname ? OUTPUT, this is right? or is it exec dbo.procname ?output.What difference is between return code and output parameter? After that I need to insert these variable values and current timestamp. How in the world do I insert SSIS variable values into a table? Any suggestions will be greatly appreciated.

  • To get the output of a stored procedure for a single value you can use a select for example

    CREATE PROCEDURE Test AS

    BEGIN

    SELECT 10

    END

    Then have your execute SQL task within package and call the stored procedure and store the result in an SSIS Variable.

    You can then write the value of the variable into another table using agaijn an execute SQL task and using the input parameters.

    The Execute SQL task would have something like

    DECLARE @result int

    SET @result = ?

    INSERT INTO X ()

    VALUES (@result)

    MCITP SQL 2005, MCSA SQL 2012

  • If you want to receive an output value from a store procedure in SSIS package, you need to select

    1- Create a SSIS variable such as MyVariable

    2- Drag and drop Execute SQL task

    3- Call your store procedure in SQL Statement option

    EXEC Demo ? Output

    CREATE PROCEDURE Demo

    @ReturnValue int

    As

    BEGIN

    SET @ReturnValue = 10

    END

    4- In Result section, select Single Row

    5- In Parameter Mapping, set SSIS variable MyVariable = @ReturnValue and Direction = Output

    6- Use the SSIS variable to insert base on your expectation

  • Dung Dinh (6/21/2012)


    If you want to receive an output value from a store procedure in SSIS package, you need to select

    1- Create a SSIS variable such as MyVariable

    2- Drag and drop Execute SQL task

    3- Call your store procedure in SQL Statement option

    EXEC Demo ? Output

    CREATE PROCEDURE Demo

    @ReturnValue int

    As

    BEGIN

    SET @ReturnValue = 10

    END

    4- In Result section, select Single Row

    5- In Parameter Mapping, set SSIS variable MyVariable = @ReturnValue and Direction = Output

    6- Use the SSIS variable to insert base on your expectation

    Actully parameter mapping was not the biggest trouble here. Inserting a ssis variable value into a table is not straight forward as it appears. I tried the above method b by declaring a variable within the execute sql task as declare @result int and setting it as set @result=? And inserting that variable into the table. Keeps telling me ,invalid parameter number, result set blab la…….does n e one know a working procedure to insert SSIS variable value into a table?

  • I assume that you know how to retrieve value for SSIS variable from a store procedure

    1- Create new a store procedure to insert SSIS variable into a table

    CREATE PROCEDURE InsertValueFromSSISVariable

    @SSISVariable int

    AS

    BEGIN

    INSERT INTO TableA(Value)

    SELECT @SSISVariable

    END

    2- Drag and drop a SQL Execute SQL task

    3- Call store procedure in SQL Statement

    EXEC InsertValueFromSSISVariable @SSISVariable = ?

    In Parameter Mapping, set SSIS variable as a parameter of store procedure

    MyVariable = SSISVariable and set Direction = Input and Data Type = int

  • Dung Dinh (6/21/2012)


    I assume that you know how to retrieve value for SSIS variable from a store procedure

    1- Create new a store procedure to insert SSIS variable into a table

    CREATE PROCEDURE InsertValueFromSSISVariable

    @SSISVariable int

    AS

    BEGIN

    INSERT INTO TableA(Value)

    SELECT @SSISVariable

    END

    2- Drag and drop a SQL Execute SQL task

    3- Call store procedure in SQL Statement

    EXEC InsertValueFromSSISVariable @SSISVariable = ?

    In Parameter Mapping, set SSIS variable as a parameter of store procedure

    MyVariable = SSISVariable and set Direction = Input and Data Type = int

    Dung, I think we are misunderstanding each other. Here are my procedural flow.

    1.Stored procedure performs delete operation.

    2.Capture the number of rows deleted @@rowcount and return it as Output Parameter in SP.

    3.Call the SP in SSIS through Execute SQL Task (Exec dbo.myproc ? output)

    4.Map the parameter into a SSIS variable in Parameter Mapping

    Variable Name: User::deletedrows

    Direction:OutPut

    DataType: Long

    ParameterName:0

    Parametersize;-1

    5.This should load whatever was returned from StoredProcedure to SSIS variable (deletedrows),correct?

    6.Now I want to insert the number of rows into a table, but how?

    The reason I am passing into ssis variable and not doing from SP is because I also have a datetime column that needs to consume datetime when the package is done running.

    Insert into tableA(effectivedate,RowsDeleted) values (getdate(),_____????) –this is where I need help with?

  • Hi gravitysucks

    Are you sure you are using a Package level variable to collect the value returned by your stored procedure instead of a task level variable?

    If you are using a task level variable, it will be out of scope on the next task, that could be your problem.

    Cheers,

    Hope this helps,
    Rock from VbCity

  • Rock from VbCity (6/21/2012)


    Hi gravitysucks

    Are you sure you are using a Package level variable to collect the value returned by your stored procedure instead of a task level variable?

    If you are using a task level variable, it will be out of scope on the next task, that could be your problem.

    Cheers,

    its package scoped variable.

    what is the syntax/method to insert a ssis variable value into a table through execute sql task?

  • Hi Gravitysucks,

    As Rtaylor made it clear the syntax is pretty straightforward.

    e.g. have a look on below dummy example

    INSERT INTO RunInfo

    (Col_1, Col_2)

    VALUES (?, ?);

    In SQL statement property of Exec SQL Task put above command.Where Col_1 & col_2 mapped with SSIS variables.

    In short use ? placeholder is used for these 🙂

    --rhythmk
    ------------------------------------------------------------------
    To post your question use below link

    https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
    🙂

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply