locked
Copy Activity is throwing error RRS feed

  • Question

  • Hi there,
    Thanks for helping me online.
    i thought of sharing my error details with you 


    ERROR DETAILS

    { "errorCode": "2200", "message": "ErrorCode=UserErrorInvalidParameterInStoredProcedure,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the property 'Value' is invalid for the stored procedure parameter 'MaxSourceId'.,Source=Microsoft.DataTransfer.ClientLibrary,''Type=System.Exception,Message=System.Collections.Generic.Dictionary`2[System.String,System.Object] is not a valid value for Int32.,Source=System,''Type=System.FormatException,Message=Input string was not in a correct format.,Source=mscorlib,'", "failureType": "UserError", "target": "Test_OnPremisesDB_To_AzureDB", "details": [] }

    All i am trying to do is pass the out from a stored proc( stored proc activity )  to a copy activity(  inside the source )

    However when i change the dynamic content ( marked as ERROR in above screen shot)  as follows it works ...

    dynamic content  = @int( 5)


    So in nutshell , what i need to do is , basically convert the data( output /result ) of a stored procedure to INT and pass it to the COPY ACTIVITY
    thanks in advance

    • Edited by rninet2020 Monday, May 4, 2020 7:14 AM
    Monday, May 4, 2020 7:12 AM

Answers

  • 1) So there is no way that we can pass the output of the stored proc activity to another activity ( in my case COPY ACTIVITY ). Is that a bad appraoch ?

    2) why must we use the LOOK UP ACTIVITY , ( like a work around - which will talk to the entire dataset .firstrow. ..etc) where as we could use a stored procedure to get the straight forward value ( single value ) and pass it to another activity..?

    1 --> SP Activity can just run the SP (Mostly to transform data). You won't be able to pass the data further to another activity. 

    2 --> I am running SP in a Lookup activity. The SP returns just 1 row. So it's not like we scan all records and get top firstRow. 

    Below blog should clear all confusion

    https://blog.pragmaticworks.com/azure-data-factory-lookup-and-stored-procedure

    My lookup setting:


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    • Marked as answer by rninet2020 Monday, May 4, 2020 12:42 PM
    Monday, May 4, 2020 12:25 PM
  • Thanks for your time and patience Vibhav !!!

    you are absolutely right :-)

    I have changed my Pipeline as follows and it works now  ... 

    So the lesson learned is  -- you cannot pass the value from stored-procedure-activity to other activity , rather use lookup activity.

    LOOKUP ACTIVITY

    Lookup Activity

    COPY ACTIVITY 

    Copy Activity

    Thanks for guiding me to the right direction :-)  kudos to you :-)

    • Marked as answer by rninet2020 Monday, May 4, 2020 12:42 PM
    Monday, May 4, 2020 12:40 PM

All replies

  • Screenshot is missing in the post. Please put a request on below thread to get account verified - https://social.microsoft.com/Forums/en-US/94f05325-8566-4c4c-806c-179a5a0beafc/verify-accounts-43?forum=reportabug

    Does the SP has OUTPUT parameter and you try to pass this param to Copy activity? This is currently not supported. 

    See and upvote this feedback - https://feedback.azure.com/forums/270578-data-factory/suggestions/36538312-allow-stored-procedure-output-parameters


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Monday, May 4, 2020 8:56 AM
  • Hi there,

    Adding to what Vaibhav mentioned,

    The OUTPUT parameter is not supported and you need to alter the stored procedure script as result set output, rather than the output parameter.

    Once this is done, you should be able to use the output in the copy activity. Please make sure you chain the copy activity to only run after the success of the Stored Procedure  activity.

    Hope this helps.


    Monday, May 4, 2020 9:00 AM
  • Thanks for you response

    infact i don;t have any OUTPUT parameter in my stored proc .. 

    FYI ; Following is the stored proc

    CREATE PROCEDURE [dbo].[sp_GetLastSourceId_FromEmployeeInAzureDB] 

    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;


    select 
    cast( ISNULL(max(sourceId),0)  as int) as LastSourceId
    from 
    [dbo].[Employee]
                 
    END

    The resultset/dataset ( which will be INT as "LastSourceId"  ) from the above stored proc is what i want to pass to my COPY ACTIVITY 

    Monday, May 4, 2020 9:30 AM
  • Thanks for you response

    infact i don;t have any OUTPUT parameter in my stored proc .. 

    FYI ; Following is the stored proc

    CREATE PROCEDURE [dbo].[sp_GetLastSourceId_FromEmployeeInAzureDB] 

    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;


    select 
    cast( ISNULL(max(sourceId),0)  as int) as LastSourceId
    from 
    [dbo].[Employee]
                 
    END

    The resultset/dataset ( which will be INT as "LastSourceId"  ) from the above stored proc is what i want to pass to my COPY ACTIVITY 

    Monday, May 4, 2020 9:31 AM

  • The resultset/dataset ( which will be INT as "LastSourceId"  ) from the above stored proc is what i want to pass to my COPY ACTIVITY 

    If you just run the Stored Procedure activity, does it run successfully? How do you want to pass the LastSourceId value to Copy activity? Copy activity would need both Source and target configurations again, so just wanted to check where the LastSourceId value goes.

    If you just want to copy this value to some target, maybe then in Copy activity - In source use - SQL SP or direct the SELECT query as it looks simple


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Monday, May 4, 2020 11:24 AM
  • 

    Hi Vibhav,

    Thanks for you kind response , Following is the screen shot  of my Pipeline.

    To answer you question ( Stored Procedure activity, does it run successfully  ..?? ) : YES

    To answer you question ( How do you want to pass the LastSourceId value to Copy activity? ) :  i want to pass the value ( LastSourceId ) from a stored procedure ( which runs Azure database ) , then pass  the "LastSourceId"  to the  SOURCE =>STORED PROC => in COPY ACTIVITY.

    The aim is to copy only subset of data from the source ( on premises Sql database ) to azure database.

    {
        "errorCode": "2200",
        "message": "ErrorCode=UserErrorInvalidParameterInStoredProcedure,
    	'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,
    	Message=The value of the property 'Value' is invalid for the stored procedure parameter 'MaxSourceId'.,Source=Microsoft.DataTransfer.ClientLibrary,'
    	'Type=System.Exception,Message=System.Collections.Generic.Dictionary`2[System.String,System.Object] is not a valid value for Int32.,Source=System,'
    	'Type=System.FormatException,Message=Input string was not in a correct format.,Source=mscorlib,'",
        "failureType": "UserError",
        "target": "Test_OnPremisesDB_To_AzureDB",
        "details": []
    }

    in nutshell all i need is - to pass the dateset/reset from the stored--proc--activity to  copy-activity ( to the source stored proc ). But unfortunately i am getting conversion error.

    thanks in advance



    • Edited by rninet2020 Monday, May 4, 2020 12:04 PM
    Monday, May 4, 2020 11:33 AM
  • 

    Try Lookup activity to get the Id and the pass that Id to Copy activity using below like expression 

    @activity('Lookup-GetEmpid').output.firstRow.EmployeeId


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    Monday, May 4, 2020 12:06 PM
  • Hi Vibhav,

    Thanks Again!! . Sorry i am not trying to make my problem yours :-)

    My question is this ..

    1) So there is no way that we can pass the output of the stored proc activity to another activity ( in my case COPY ACTIVITY ). Is that a bad appraoch ?

    2) why must we use the LOOK UP ACTIVITY , ( like a work around - which will talk to the entire dataset .firstrow. ..etc) where as we could use a stored procedure to get the straight forward value ( single value ) and pass it to another activity..? .. 

    Monday, May 4, 2020 12:14 PM
  • 1) So there is no way that we can pass the output of the stored proc activity to another activity ( in my case COPY ACTIVITY ). Is that a bad appraoch ?

    2) why must we use the LOOK UP ACTIVITY , ( like a work around - which will talk to the entire dataset .firstrow. ..etc) where as we could use a stored procedure to get the straight forward value ( single value ) and pass it to another activity..?

    1 --> SP Activity can just run the SP (Mostly to transform data). You won't be able to pass the data further to another activity. 

    2 --> I am running SP in a Lookup activity. The SP returns just 1 row. So it's not like we scan all records and get top firstRow. 

    Below blog should clear all confusion

    https://blog.pragmaticworks.com/azure-data-factory-lookup-and-stored-procedure

    My lookup setting:


    If the response helped, do "Mark as answer" and upvote it
    - Vaibhav

    • Marked as answer by rninet2020 Monday, May 4, 2020 12:42 PM
    Monday, May 4, 2020 12:25 PM
  • Thanks for your time and patience Vibhav !!!

    you are absolutely right :-)

    I have changed my Pipeline as follows and it works now  ... 

    So the lesson learned is  -- you cannot pass the value from stored-procedure-activity to other activity , rather use lookup activity.

    LOOKUP ACTIVITY

    Lookup Activity

    COPY ACTIVITY 

    Copy Activity

    Thanks for guiding me to the right direction :-)  kudos to you :-)

    • Marked as answer by rninet2020 Monday, May 4, 2020 12:42 PM
    Monday, May 4, 2020 12:40 PM