Executing Procedure in SSIS package

  • Hi everyone..

    I have two stored procedures in two data bases,two procedures returns some binary record,my question is i have to two variables in my ssis package,i want to store the that two return values to the SSIS variables.Can any one help me its very urgent.

    Regards..
    guru12

  • To do this you will need to use a resultset in your execute SQL task.

    Edit the task and under the general tab change the resulset setting to the one you need.

    Then under the resulset tab add the variable that you need the resultset mapped to

  • Hi Steveb..

    Can you explain briefly ont this,I am posting my procedure

    ALTER PROCEDURE GetLastLSN

    (

    @TableName NVARCHAR(100),

    @LastLSN BINARY(10) = NULL OUTPUT

    )

    AS

    BEGIN

    /*****************************************************************************************************************

    Gets the last LSN that was parsed during the last run for a given table.

    When used to run the job again increment the last LSN by 1 so that we are actually not parsing duplicate rows.

    If last LSN returned is 0x00000000000000000000, use "sys.fn_cdc_get_min_lsn" to get the minimum LSN to use.

    *****************************************************************************************************************/

    DECLARE @_LastLSN BINARY(10)

    SET @_LastLSN = 0x00000000000000000000

    SELECT @_LastLSN = LastLSN FROM LSNLog WHERE TableName = @TableName

    SET @LastLSN = @_LastLSN

    END

    So i need to run this procedure and have to save the lastLSN value to a variable,this procedure returns a binary object..please explain in brief

    Regards..
    guru12

  • Edit the sql task and chane the resultset to singlerow under the general tab.

    In the resultset tab add a new variable to hold the data that is being returned, make sure you use a compatible data type. It looks like you are returning a binary datatype rather than a binary object.

  • HI Steveb..

    YEs i want to return a binary data type,i did like what you said it is throwing error...

    [Execute SQL Task] Error: Executing the query "EXEC GetLastLSN @Tablename=?,@LastLSN=? OUTPUT;" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    My Package variable is

    Tablename,

    Regards..
    guru12

  • Have you checked the possible issues in the error message?

    such as; make sure your parameters have variables associated with them and also check that the resultset is set properly and the resultset is being mapped to a variable..

  • Hi Steveb..

    Now it is giving new error

    [Execute SQL Task] Error: Executing the query "EXEC GetLastLSN @Tablename=?,@LastLSN=? OUTPUT;" failed with the following error: "Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    I think I din't tell you my requirement properly,here i am sending you again ,please help on this its very urgent.

    Actual we are implementing CDC for incremental load,we have tow procedures in two databases,here are my two procedures

    Procedure 1:

    CREATE PROCEDURE GetLastLSN

    (

    @TableName NVARCHAR(100),

    @LastLSN BINARY(10) = NULL OUTPUT

    )

    AS

    BEGIN

    /*****************************************************************************************************************

    Gets the last LSN that was parsed during the last run for a given table.

    When used to run the job again increment the last LSN by 1 so that we are actually not parsing duplicate rows.

    If last LSN returned is 0x00000000000000000000, use "sys.fn_cdc_get_min_lsn" to get the minimum LSN to use.

    *****************************************************************************************************************/

    DECLARE @_LastLSN BINARY(10)

    SET @_LastLSN = 0x00000000000000000000

    SELECT @_LastLSN = LastLSN FROM LSNLog WHERE TableName = @TableName

    SET @LastLSN = @_LastLSN

    END

    Proceddure 2.

    CREATE PROCEDURE GetEndLSN

    (

    @TableName NVARCHAR(100),

    @EndLSN BINARY(10) OUTPUT

    )

    AS

    BEGIN

    /****************************************************************************************************

    Gets the end LSN number to be used while fetching the changes that are made to a table's data.

    ****************************************************************************************************/

    SELECT @EndLSN = sys.fn_cdc_get_max_lsn()

    END

    GO

    These two procedures will return two binary type values,i have to put these values into my package variables

    my package variables are.

    StartLSN,

    EndLSn ,

    TableName.

    i have to store the GetlastLSn value into StartLSn variable and GetEndLsn value into endLsn variable,

    for this i have taken Executesqltask,i executed first procedure like this

    EXEC GetLastLSN @Tablename=?,@LastLSN=? OUTPUT;

    and in parameter mapping Usertable::Tablename input and User::StartLSn output.

    and in resultset i selected single row,and result setname=o,User::StartLsn.

    i have taken parameterdatatypes both string and package variables also string,

    when i run my package it is giving the following error

    [Execute SQL Task] Error: Executing the query "EXEC GetLastLSN @Tablename=?,@LastLSN=? OUTPUT;" failed with the following error: "Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    ..

    please help me on this..

    Regards..
    guru12

  • It seems that you are trying to insert a string into a variable of datatype binary, which is causing the error.

    You said that your package variables are of the string datatype, so you have to do a conversion first, as it is mentioned in the error itself.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • HI da zero ..

    Can you Explain briefly how to do conversion in ssis ,i have taken oledb source,

    gave one connection and in sql comman

    i wrote like this

    Exec GETLASTLSN @tablename='Mas_customer',@LastLSN=? output;

    but it is throwing an error,

    There is a datasource column with no name,there should be a name for each column.

    Regards..
    guru12

  • HI steve ,DA zero..

    THanks for your supporting the issue is resolved,in my procedure for the out put parameter i have given string datatype,string is not supporting the binaray type,so i have changed to int ,it is fine now and running succesful.Thaankyou Steve for your quick response.

    Regards..
    guru12

  • Hi guru12

    I am facing same problem while working on SQL server CDC.

    Per your last comment did you change in procedure @LastLSN to INT ( instead of BINARY(10))?

    I understood that u put

    in parameter mapping Usertable::Tablename input and User::StartLSn output.

    and in resultset selected single row,and result setname=o,User::StartLsn.

    parameterdatatypes both string and package variables also string,

    I am not getting option to select parameterdatatypes as string, did you put varchar here ?

    Could you please clarify what changes fixed your issue??

    Thanks in advance.

Viewing 11 posts - 1 through 10 (of 10 total)

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