Is there a workaround for the fact that you need to name the first parameter of the stored procedure (the one containing the table type) exactly as the property "tableName" in the input dataaset?
Im using Azure Data Factory V1.
Input dataset (On-premise Oracle source)
{
"name": "DS-ORA-WMS-CDC-DLYTRN",
"properties": {
"published": false,
"type": "OracleTable",
"linkedServiceName": "LS-ORA-WMS-CDC",
"typeProperties": {
"tableName": "WMST.DLYTRN"
},
"availability": {
"frequency": "Hour",
"interval": 1
},
"external": true,
"policy": {}
}}
Output dataset (Azure SQL database)
{
"name": "DS-ASQL-ANALYTICS-DLYTRN",
"properties": {
"published": false,
"type": "AzureSqlTable",
"linkedServiceName": "LS-SQL-ANALYTICS-DB",
"typeProperties": {
"tableName": "wms.DLYTRN"
},
"availability": {
"frequency": "Hour",
"interval": 1
}
}}
Pipeline
{
"name": "test",
"properties": {
"description": "test pipeline",
"activities": [
{
"type": "Copy",
"typeProperties": {
"source": {
"type": "OracleSource",
"oracleReaderQuery": "select * from WMST.DLYTRN"
},
"sink": {
"type": "SqlSink",
"sqlWriterStoredProcedureName": "wms.spPersistDlytrn",
"storedProcedureParameters": {
"srcdc": {
"value": "CDC"
}
},
"sqlWriterTableType": "wms.DLYTRNType",
"writeBatchSize": 0,
"writeBatchTimeout": "00:00:00"
}
},
"inputs": [
{
"name": "DS-ORA-WMS-CDC-DLYTRN"
}
],
"outputs": [
{
"name": "DS-ASQL-ANALYTICS-DLYTRN"
}
],
"policy": {
"timeout": "1.00:00:00",
"concurrency": 1,
"retry": 3
},
"scheduler": {
"frequency": "Hour",
"interval": 1
},
"name": "TestWMSCopyWithSproc"
}
],
"start": "2018-01-04T07:00:00Z",
"end": "2018-01-08T00:00:00Z",
"isPaused": false,
"hubName": "hub",
"pipelineMode": "Scheduled"
}}
Stored procedure
CREATE PROCEDURE [wms].[spPersistDlytrn]
@DLYTRNTable [wms].[DLYTRNType] READONLY,
@srcdc VARCHAR(4)
AS
...
RETURN 0
When running the activity it return the below error message:
Database operation failed on server 'Sink:tcp:someservername.database.windows.net,1433' with SQL Error Number '349'. Error message from database execution : The procedure "spPersistDlytrn" has no parameter named "@wms.DLYTRN"..
As it is not possible to name the stored procedure parameter "wms.DLYTRN" is there a way to exclude the schema prefix?