Azure Data Factory SQL join output of stored procedure with another SQL table

Question

I need to be able to SQL join the output of one Stored procedure with another SQL table.

Currently, I am running one lookup activity (name - CustomSP) to run the Stored procedure and get the output.

Output of Stored Procedure is as follows -

{
  "count": 3,
  "value": [
     {
        "Name": "Test1",
        "ID": 1,
        "Zip": "34567"
     },
     {
		"Name": "Test2",
		"ID": 2,
		"Zip": "23456"
	 },
     {
		"Name": "Test3",
		"ID": 3,
		"Zip": "22342"
	 }
  ]
}

I have another SQL table (named - Custom.In.Details within DB named User , Schema is dbo)

Custom.In.Details SQL table has field named CustomID among all the other fields and I was trying to join the output of SP with field ID and SQL Table with field CustomID and also filter rows which only had InOrOut field as I in the SQL table.

Custom.In.Details table is as follows-

In order to achieve that, currently I was trying to run the Lookup activity (named - SQL Join to run the below Query -

SELECT * FROM [User].[dbo].[Custom.In.Details] t1
INNER JOIN activity('CustomSP').output.value t2
ON t1.CustomID = t2.ID WHERE t1.InOrOut = 'I'

I was currently seeing errors during a JOIN. I was wondering if the approach I am using is the right one, or is there anything else that had to be done before trying to directly JOIN an output of SP with another SQL table.

Answer 1

You may need to think about inserting its results into a temporary or permanent table in your database directly.

Or using ADF, you can create a new dataset that represents the temporary or permanent table where your stored procedure outputs its data :

1. Create another dataset for the SQL table [User].[dbo].[Custom.In.Details].
2. Then use a Join transformation to join these two sources on CustomID = ID. Set the join type to Inner Join.
3. Add a Filter transformation to include only rows where InOrOut = 'I'

Keep in mind that you can execute your stored procedure before the data flow by configuring a pipeline with an Execute SQL activity to run the stored procedure before your Data Flow activity.

Make sure that the execution order is enforced by linking the activities correctly with success dependencies.