CODEWITHSUNDEEP
sqlazureazure-sql-databaseazure-data-factory
c.malcolm Richard
c.malcolm Richard

Reputation: 21

ADF - Iterative select Output to Copy activity

we have an SP where the SP executes and returns multiple result set with change in column number as below:

create proc test ab
as 
select 1 as num1
select 1 as num1, 2 as num2
select 1 as num1, 2 as num2,3 as num3

The below output in screenshot should be moved to multiple tables using ADF. how to we achieve this?

enter image description here

Note: The SP cannot be changed and this is an example script.

Upvotes: 2

Views: 1064

Answers (2)

wBob
wBob

Reputation: 14389

If the process is exactly as you describe, then the new Script activity supports multiple resultsets and can be combined with a For Each activity or multiple Script activities to walk through them in parallel and load them to database tables. Please bear in mind that the Script activity (just like the Lookup activity) is limited to 5,000 rows and 2MB of data, so it's not meant for large-scale data imports, and understand that it is fundamentally different to the Copy activity in that it pulls data into the pipeline which you have to manipulate.

A simple example:

enter image description here

The first Script activity simply calls the stored proc that returns the multiple resultsets. The output from the activity looks roughly like this:

{
    "resultSetCount": 3,
    "recordsAffected": 0,
    "resultSets": [
        {
            "rowCount": 1,
            "rows": [
                {
                    "num1": 1
                }
            ]
        },
        {
            "rowCount": 1,
            "rows": [
                {
                    "num1": 1,
                    "num2": 2
                }
            ]
        },
        {
            "rowCount": 1,
            "rows": [
                {
                    "num1": 1,
                    "num2": 2,
                    "num3": 3
                }
            ]
        }
    ],
    "outputParameters": {},
...

As you can see, your multiple resultsets are now in the pipeline and can be manipulated. The resultsets are 0-based, so the first one (which returns only num1) is resultSets[0] and they can be referred to using the following syntax:

activity('Script0').output.resultSets[0]

So for each Script activity we have slightly differing statements:

Script0 - call the stored proc:

EXEC usp_multipleResultSets;

Script1 - load resultset 0:

@concat('INSERT INTO dbo.multipleResultSets ( num1, dateAdded ) SELECT ', activity('Script0').output.resultSets[0].rows[0].num1, ', GETDATE();' )

Script2 - load resultset 1:

@concat('INSERT INTO dbo.multipleResultSets ( num1, num2, dateAdded ) SELECT ', activity('Script0').output.resultSets[1].rows[0].num1, ', ', activity('Script0').output.resultSets[1].rows[0].num2, ', GETDATE();' )

Script3 - load resultset 2:

@concat('INSERT INTO dbo.multipleResultSets ( num1, num2, num3, dateAdded ) SELECT ', activity('Script0').output.resultSets[2].rows[0].num1, ', ', activity('Script0').output.resultSets[2].rows[0].num2, ', ', activity('Script0').output.resultSets[2].rows[0].num3, ', GETDATE();' )

The Script type should be set to Query for Script 0 but can be NonQuery for the others.

NB The Script task does allow you to add multiple scripts using the plus (+) button but they would then run in serial. I'm using three here as I want the three items of SQL to run in parallel.

Upvotes: 2

Michael Green
Michael Green

Reputation: 1491

ADF now has the SQL Script activity which allows for multiple resultsets.

Upvotes: 0

Related Questions