How to write to two tables using pipelines

Question

Hello

I'm trying to achieve the following:

1. Get data from one table using a stored procedure
2. Compare this data to another table
3. Write to 2 tables using a stored procedure

It seems i can use dataflow to do 1&2 but not 3, and I can use pipeline to 1 and 3 but only 1 table How can I achieve this? I would like to be able to perform the lookup and write back to two tables.

Thank you in advance

Regards

Answer 1

Hello @V

To achieve your goal of writing to two tables using a stored procedure, you can use a pipeline with a Stored Procedure Activity. Here are the high-level steps you can follow:

1.Use a Lookup Activity to get data from the first table using a stored procedure.

2. Use a Copy Activity to copy data from the second table to a staging table in your destination database.
3. Use a Stored Procedure Activity to compare the data from the Lookup Activity with the data in the staging table and write to both tables using a stored procedure.

Here's an example of how you can define the Stored Procedure Activity in your pipeline:

{
  "name": "WriteToTwoTables",
  "type": "SqlServerStoredProcedure",
  "linkedServiceName": "",
  "typeProperties": {
    "storedProcedureName": "",
    "storedProcedureParameters": {
      "inputTable": {
        "value": "@activity('LookupActivity').output",
        "type": "Table"
      },
      "outputTable1": {
        "value": "",
        "type": "String"
      },
      "outputTable2": {
        "value": "",
        "type": "String"
      }
    }
  },
  "dependsOn": [
    {
      "activity": "LookupActivity",
      "dependencyConditions": [
        "Succeeded"
      ]
    },
    {
      "activity": "CopyActivity",
      "dependencyConditions": [
        "Succeeded"
      ]
    }
  ]
}

In the Stored Procedure Activity, you can pass the output of the Lookup Activity as a parameter to your stored procedure. You can also pass the names of your two output tables as parameters to the stored procedure. I hope this helps! Let me know if you have any further questions.

Answer 2

@V

The error you’re encountering is due to the data type “Table” not being supported in the context you’re using it. Here are a few steps to troubleshoot and resolve this issue:

Check Data Types:

Ensure that the data types used in your stored procedure and pipeline activities are supported by Azure Data Factory. The “Table” data type might not be directly supported, so you may need to use a different approach.

Use JSON or String Data Types:

Instead of using the “Table” data type, consider using JSON or string data types to pass the data. You can serialize the table data into a JSON string and then deserialize it within your stored procedure.

Modify Stored Procedure:

Modify your stored procedure to accept JSON or string input instead of a table. Here’s an example of how you can do this:

CREATE PROCEDURE [dbo].[YourStoredProcedure]
@JsonInput NVARCHAR(MAX)
AS
BEGIN
    -- Parse JSON input and insert into table
    INSERT INTO YourTable (Column1, Column2)
    SELECT value1, value2
    FROM OPENJSON(@JsonInput)
    WITH (
        value1 NVARCHAR(50),
        value2 NVARCHAR(50)
    )
END

• In your pipeline, use a Set Variable Activity to store the JSON string.
• Pass this variable to the stored procedure.