locked
Copy activity error after 10k rows RRS feed

  • Question

  • Hello,

    I've got simple pipeline:

    1. select data from one sql table

    2. push it to Stored procedure that inserts or updates data in another sql table

    After exactly 10k rows (which are processed - inserted or updated) i got error:

    <section class="fxs-tile fxs-tilesize-fullwidthfitheight fx-rightClick" style="height:auto;width:535px;-webkit-transition:height 0.125s linear 0.125s, width 0.125s linear 0s;transition:height 0.125s linear 0.125s, width 0.125s linear 0s;margin-bottom:0px;">
    <section class="fxs-part-content css-scope-Microsoft_Azure_DataFactory" style="font-size:12px;background-color:inherit;width:505px;height:80px;box-sizing:border-box;">Copy activity met invalid parameters: ErrorCode=InvalidParameter,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The value of the property '' is invalid: 'The SqlParameter is already contained by another SqlParameterCollection.'.,Source=,''Type=System.ArgumentException,Message=The SqlParameter is already contained by another SqlParameterCollection.,Source=System.Data,'.</section>

    <svg class="fxs-part-svg" focusable="false"><rect class="fxs-part-svg-rect" height="100%" width="100%" x="0" y="0"></rect></svg>
    </section>

    Tuesday, April 28, 2015 12:18 PM

Answers

All replies

  • Hello,

    I've got problem with Azure Data Factory and Stored Procedure.

    I've set SP as sink for input data:

    "sink": {
                            "type": "SqlSink",
                    "sqlWriterStoredProcedureName": "spAddProducts",
                        "storedProcedureParameters": {
                            "stringProductData": {
                                "value": "str1"
                            }
                        },

    and after execution I've got to process about 200k records, but after some limited number of processed rows (about 10k), I've got error:

    Copy activity met invalid parameters:
    ErrorCode=InvalidParameter,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,
    Message=The value of the property '' is invalid: 'The SqlParameter is already contained by another
    SqlParameterCollection.'.,Source=,''Type=System.ArgumentException,
    Message=The SqlParameter is already contained by another SqlParameterCollection.,Source=System.Data,'.

    SP code:

    CREATE PROCEDURE spAddProducts @DimProducts [dbo].[ProductsType] READONLY, @stringProductData varchar(256)
    AS
    BEGIN
    
    MERGE [dbo].[DimProducts] AS tpr
        USING @DimProducts AS spr
        ON tpr.ID = spr.ID
    WHEN MATCHED AND (tpr.Name <> spr.Name OR tpr.NameInternational <> spr.NameInternational OR tpr.ProductType <> spr.ProductType) THEN 
        UPDATE SET tpr.Name = spr.Name, tpr.NameInternational = spr.NameInternational, tpr.ProductType = spr.ProductType
    WHEN NOT MATCHED THEN 
        INSERT (Name, NameInternational, ProductType, ID) 
        VALUES(spr.Name, spr.NameInternational, spr.ProductType, spr.ID)
    ;
    END

    • Merged by Elliot Waingold (MSFT) Wednesday, October 14, 2015 5:28 PM Both threads describe the same issue from the same poster.
    Tuesday, April 28, 2015 8:08 AM
  • Hi Piotr,

    This is a defect we just fixed. The fix will be available in Prod in one or two days. Sorry for the inconvenience.

    Thanks,

    Yingqin

    Wednesday, April 29, 2015 3:17 AM
  • Hi Piotr,

    This issue is duplicate with your another post. The fix will be available in one or two days.

    Wednesday, April 29, 2015 3:28 AM
  • Right, I should update that one. Thank you for quick answer, I'm waiting for fix.
    Wednesday, April 29, 2015 8:12 AM
  • Update:

    We just did a Prod refresh, and the fix should have been in Prod now. You can take another try.

    Wednesday, April 29, 2015 8:31 AM