Azure DataFactory chain activities

Question

I'm very new to DataFactory and having issues understanding how to properly create a Pipeline that will execute a stored proc before performing a copy function.

The stored proc is simply a TRUNCATE of the destination table which is used as the output dataset in the second activity.

From the DataFactory docs, it tells me that to execute the stored proc first, specify the proc's "output" as the "input" of the second activity.

However, there's no real "output" from the stored proc. In order to get it to "work", I cloned the output of the second activity, changed the name of it and made it external=false to make it past the provisioning errors but that's obviously a total kludge.

It doesn't make sense to me that, at least in the case of a TRUNCATE action performed by this stored proc, why there would even need to be an output defined.

But, when I tried to use the output from the stored proc as an additional input, I received an error about having a duplicated table name.

How can I get the TRUNCATE stored proc activity to successfully execute (and complete) prior to running the copy activity?

Here's the pipeline code:

{
    "name": "Traffic CRM - System User Stage",
    "properties": {
        "description": "Move System User to Stage",
        "activities": [
            {
                "type": "SqlServerStoredProcedure",
                "typeProperties": {
                    "storedProcedureName": "dbo.usp_Truncate_Traffic_Crm_SystemUser",
                    "storedProcedureParameters": {}
                },
                "outputs": [
                    {
                        "name": "Smart App - usp Truncate System User"
                    }
                ],
                "policy": {
                    "timeout": "01:00:00",
                    "concurrency": 1,
                    "retry": 3
                },
                "scheduler": {
                    "frequency": "Day",
                    "interval": 1
                },
                "name": "Smart App - SystemUser Truncate"
            },
            {
                "type": "Copy",
                "typeProperties": {
                    "source": {
                        "type": "SqlSource",
                        "sqlReaderQuery": "select * from [dbo].[Traffic_Crm_SystemUser]"
                    },
                    "sink": {
                        "type": "SqlSink",
                        "writeBatchSize": 0,
                        "writeBatchTimeout": "00:00:00"
                    },
                    "translator": {
                        "type": "TabularTranslator",
                        "columnMappings": "All columns mapped here"
                    }
                },
                "inputs": [
                    {
                        "name": "Traffic CRM - SytemUser Stage"
                    }
                ],
                "outputs": [
                    {
                        "name": "Smart App - System User Stage Production"
                    }
                ],
                "policy": {
                    "timeout": "1.00:00:00",
                    "concurrency": 1,
                    "executionPriorityOrder": "NewestFirst",
                    "style": "StartOfInterval",
                    "retry": 3,
                    "longRetry": 0,
                    "longRetryInterval": "00:00:00"
                },
                "scheduler": {
                    "frequency": "Day",
                    "interval": 1
                },
                "name": "Activity-0-[dbo]_[Traffic_Crm_SystemUser]->[dbo]_[Traffic_Crm_SystemUser]"
            }
        ],
        "start": "2017-01-19T14:30:57.309Z",
        "end": "2099-12-31T05:00:00Z",
        "isPaused": false,
        "hubName": "stagingdatafactory1_hub",
        "pipelineMode": "Scheduled"
    }
}

Answer 1

Your SP activity output dataset i.e. "name": "Smart App - usp Truncate System User" should be input for the next activity. If you have confusion on what to put in dataset, just create a dummy dataset like below

{
    "name": "DummySPDS",
    "properties": {
        "published": false,
        "type": "SqlServerTable",
        "linkedServiceName": "SQLServerLS",
        "typeProperties": {
            "tableName": "dummyTable"
        },
        "availability": {
            "frequency": "Hour",
            "interval": 1
        },
        "IsExternal":"True"
    }
}

Here is complete pipeline code

{
    "name": "Traffic CRM - System User Stage",
    "properties": {
        "description": "Move System User to Stage",
        "activities": [
            {
                "type": "SqlServerStoredProcedure",
                "typeProperties": {
                    "storedProcedureName": "dbo.usp_Truncate_Traffic_Crm_SystemUser",
                    "storedProcedureParameters": {}
                },
                "inputs": [
                    {
                        "name": "DummySPDS"
                    }
                ],
                "outputs": [
                    {
                        "name": "Smart App - usp Truncate System User"
                    }
                ],
                "policy": {
                    "timeout": "01:00:00",
                    "concurrency": 1,
                    "retry": 3
                },
                "scheduler": {
                    "frequency": "Day",
                    "interval": 1
                },
                "name": "Smart App - SystemUser Truncate"
            },
            {
                "type": "Copy",
                "typeProperties": {
                    "source": {
                        "type": "SqlSource",
                        "sqlReaderQuery": "select * from [dbo].[Traffic_Crm_SystemUser]"
                    },
                    "sink": {
                        "type": "SqlSink",
                        "writeBatchSize": 0,
                        "writeBatchTimeout": "00:00:00"
                    },
                    "translator": {
                        "type": "TabularTranslator",
                        "columnMappings": "All columns mapped here"
                    }
                },
                "inputs": [
                    {
                        "name": "Smart App - usp Truncate System User"
                    }
                ],
                "outputs": [
                    {
                        "name": "Smart App - System User Stage Production"
                    }
                ],
                "policy": {
                    "timeout": "1.00:00:00",
                    "concurrency": 1,
                    "executionPriorityOrder": "NewestFirst",
                    "style": "StartOfInterval",
                    "retry": 3,
                    "longRetry": 0,
                    "longRetryInterval": "00:00:00"
                },
                "scheduler": {
                    "frequency": "Day",
                    "interval": 1
                },
                "name": "Activity-0-[dbo]_[Traffic_Crm_SystemUser]->[dbo]_[Traffic_Crm_SystemUser]"
            }
        ],
        "start": "2017-01-19T14:30:57.309Z",
        "end": "2099-12-31T05:00:00Z",
        "isPaused": false,
        "hubName": "stagingdatafactory1_hub",
        "pipelineMode": "Scheduled"