Azure IR Copy from Onprem directly to Azure SQL

Question

I have setup an IR on a local sql server, And connected it to my Data factory v2, test run successfully copied a simple table dbo.employee into Blob storage.

Because i am copying small amounts of data i would like to copy data directly from on prem SQL, to SQL Azure database using a select.

I followed this tutorial - https://docs.microsoft.com/en-us/azure/data-factory/tutorial-hybrid-copy-powershell

I then created a pipeline with a copy task, using my sqlserverdataset as a source - Simple Employee Table with 3 columns and 3 rows.

I created a similar table on my azure sql server testemployee with the same columns and set that for as the sink dataset.

Im not sure how to structure the stored proc its asking for in the sink.

for test purpose i created a sp on my azure sql as

CREATE PROCEDURE spCopyFromOnPremToAzure AS BEGIN INSERT [dbo].[TestEmployee] SELECT * FROM [dbo].[Employee] END

and used that as the proc in sink. for table type i used TABLE

under mapping i can see the source and destination columns have mapped.

I triggered the pipeline which then failed,

SOURCE

{
    "source": {
        "type": "SqlSource"
    },
    "sink": {
        "type": "SqlSink",
        "writeBatchSize": 10000,
        "sqlWriterStoredProcedureName": "[dbo].[spCopyFromOnPremToAzure]",
        "sqlWriterTableType": "TABLE"
    },
    "enableStaging": false,
    "translator": {
        "type": "TabularTranslator",
        "columnMappings": {
            "ID": "ID",
            "FirstName": "FirstName",
            "LastName": "LastName"
        }
    }
}

OUTPUT

{
    "dataRead": 62,
    "dataWritten": 0,
    "rowsRead": 3,
    "rowsCopied": 0,
    "copyDuration": 8,
    "throughput": 0.01,
    "errors": [
        {
            "Code": 11000,
            "Message": "'Type=System.Data.SqlClient.SqlException,Message=The procedure \"spCopyFromOnPremToAzure\" has no parameter named \"@[dbo].[TestEmployee]\".,Source=.Net SqlClient Data Provider,SqlErrorNumber=349,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=349,State=1,Message=The procedure \"spCopyFromOnPremToAzure\" has no parameter named \"@[dbo].[TestEmployee]\".,},],'",
            "EventType": 0,
            "Category": 5,
            "Data": {},
            "MsgId": null,
            "ExceptionType": null,
            "Source": null,
            "StackTrace": null,
            "InnerEventInfos": []
        }
    ],
    "effectiveIntegrationRuntime": "TestIR",
    "usedParallelCopies": 1,
    "executionDetails": [
        {
            "source": {
                "type": "SqlServer"
            },
            "sink": {
                "type": "AzureSqlDatabase"
            },
            "status": "Failed",
            "start": "2019-01-30T13:45:19.4402274Z",
            "duration": 8,
            "usedParallelCopies": 1,
            "detailedDurations": {
                "queuingDuration": 1,
                "timeToFirstByte": 0,
                "transferDuration": 7
            }
        }
    ]
}

ERROR

{
    "errorCode": "2200",
    "message": "'Type=System.Data.SqlClient.SqlException,Message=The procedure \"spCopyFromOnPremToAzure\" has no parameter named \"@[dbo].[TestEmployee]\".,Source=.Net SqlClient Data Provider,SqlErrorNumber=349,Class=16,ErrorCode=-2146232060,State=1,Errors=[{Class=16,Number=349,State=1,Message=The procedure \"spCopyFromOnPremToAzure\" has no parameter named \"@[dbo].[TestEmployee]\".,},],'",
    "failureType": "UserError",
    "target": "Copy Data1"
}

Im assuming this has to do with the stored proc, How would i structure it to do a basic select from Source and copy to sink ?

Fang Liu Fang Liu · Accepted Answer · 2019-01-30T15:39:10

In your case, seems you don’t need stored procedure. Just set table name in dataset is enough.

Please go through this doc. It also has an example for stored procedure. https://docs.microsoft.com/en-us/azure/data-factory/connector-azure-sql-database#invoking-stored-procedure-for-sql-sink

Azure IR Copy from Onprem directly to Azure SQL

2 Answers