none
A database operation failed with the following error: Invalid object name inDataFromSource

    Question

  • Hi Team,

    I am working on data factory pipeline COPY tool to move data from one sql DB to another sql DB. In my source DB i have SP which returns me Dataset and if i directly map the result with destination table it is working fine. In my case i have created user defined table type in destination DB with same column like source dataset. This table type I am using as input parameter for my destination SP e.g create storeprocedure xyz (@inDataFromSource as Userdefinedtabletype readonly).

    in my copy tool in Sink tab i am selecting the destination SP and providing tabletype field as Userdefinedtabletype. In mapping tab i am not doing any mapping. Now when I run my copy i get below error

    "errorCode": "2200", "message": "ErrorCode=FailedDbOperation,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=A database operation failed with the following error: 'Invalid object name 'inDataFromSource'.',Source=,''Type=System.Data.SqlClient.SqlException,Message=Invalid object name 'inDataFromSource'.,Source=.Net SqlClient Data Provider,SqlErrorNumber=208,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=208,State=1,Message=Invalid object name 'inDataFromSource'.,},],'", "failureType": "UserError", "target": "CopyCustomerData"

    belwo is my json code of copy data tool

    {
    "name": "CustomerCopyusingSP",
    "properties": {
    "activities": [
    {
    "name": "CopyCustomerData",
    "type": "Copy",
    "policy": {
    "timeout": "7.00:00:00",
    "retry": 0,
    "retryIntervalInSeconds": 30,
    "secureOutput": false
    },
    "userProperties": [
    {
    "name": "Destination",
    "value": "inDataFromSource"
    }
    ],
    "typeProperties": {
    "source": {
    "type": "SqlSource",
    "sqlReaderQuery": "select C.CustomerId,NameStyle,Title,FirstName,MiddleName,LastName,Suffix,EmailAddress,Phone,AddressID,AddressType from SalesLT.Customer C, SalesLT.CustomerAddress CA where C.CustomerID=CA.CustomerID"
    },
    "sink": {
    "type": "SqlSink",
    "writeBatchSize": 10000,
    "sqlWriterStoredProcedureName": "[dbo].[sp_insertCutomerData]",
    "sqlWriterTableType": "tabTypeUploadData"
    },
    "enableStaging": false,
    "dataIntegrationUnits": 0
    },
    "inputs": [
    {
    "referenceName": "GetCustomerData",
    "type": "DatasetReference"
    }
    ],
    "outputs": [
    {
    "referenceName": "CopyCustomerData",
    "type": "DatasetReference"
    }
    ]
    }
    ]
    },
    "type": "Microsoft.DataFactory/factories/pipelines"

    }

    please help me on this I am not sure what i am doing wrong

    Tuesday, July 17, 2018 4:40 PM

Answers

  • Hi DELSUNDAS,

    What is the tableName of sink dataset? According to the doc: https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-database

           The parameter name(inDataFromSource in your case) of the stored procedure should be the same as the tableName defined in the dataset.

    Please verify it.

    Thanks.


    Wednesday, July 18, 2018 1:38 AM

All replies

  • Hi DELSUNDAS,

    What is the tableName of sink dataset? According to the doc: https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-database

           The parameter name(inDataFromSource in your case) of the stored procedure should be the same as the tableName defined in the dataset.

    Please verify it.

    Thanks.


    Wednesday, July 18, 2018 1:38 AM
  • As per the documentation to bind source data direct to sql table type I need to specify the Table type name in Sink section. in that case my table type name is tabTypeUploadData not inDataFromSource

    "sink": {
    "type": "SqlSink",
    "writeBatchSize": 10000,
    "sqlWriterStoredProcedureName": "[dbo].[sp_insertCutomerData]",
    "sqlWriterTableType": "tabTypeUploadData"
    },

    getting the same error my source schema and table type schema is also same

    Wednesday, July 18, 2018 4:15 AM
  • Thank you for reply. it is resolved problem was in my end only in sp i did something wrong now i have fixed it and working fine
    Wednesday, July 18, 2018 4:27 AM
  • Good to hear that.

    Have fun with ADFv2!

    Wednesday, July 18, 2018 5:38 AM