ADF: passing the output of a Set Variable to Azure SQL database table

Question

I am developing an ADF pipeline that looks through a set of ADLS directories and extracting the name and last modified dates for each folder. I have written the extracted information to a "Set Variable" activity, my major issue now is how to pass these arrays into an Azure SQL table. I have tried using a stored procedure, but it does not write any records, I also tried copy activity but I wasn't sure how to configure the parameters.

Here is what I have tried:

"Set variable1" collects all the records appended by the inner Append variable activity.

The output in the set variable is like this:

{
    "name": "allFiles",
    "value": [
        "FileName,LastUpdated",
        "final_directory1,2024-11-15T10:50:23+00:00",
        "all_entry_levels,2025-07-11T20:58:34+00:00",
        "final_directory,2025-07-11T21:01:38+00:00",
        "groceries_list,2025-07-11T21:04:44+00:00",
        "all_onetime_records,2025-07-11T21:04:58+00:00",
        "industries_list,2025-07-11T21:05:11+00:00",
        "SNAPSHOT_last,2025-07-11T21:05:25+00:00",
        "LATEST_information_farming,2025-07-11T21:05:38+00:00"
    ]
}

For the copy activity, I created a dummy csv file and pointed the dataset to it, then mapped an additional column to the set variable

At this point, I have no idea what to do, I know I need to map the columns but I am a bit confused as to how.

Any help would be really appreciated, either with copy activity or stored procedure activity.

On a side note: be aware that the LastModifiedDate of a folder in ADLS is only in reference to the Folder and not to its contents. If you add/change/delete an item inside the folder it will NOT update the LastModifiedDate of the folder itself. — Joel Cochran
– Joel Cochran, Commented Jul 15 at 20:13

SQL006 · Accepted Answer · 2025-07-13 16:18:48Z

You can use stored procedure activity to insert the array data into table.

Modify the array result by removing the "FileName,LastUpdated" and also used |(pipe) in place of ,(comma) between the 2 fields as shown below

Now assign this array variable to stored procedure activity, since it is array convert it into string either by using @string(variables('AllFiles')) or @{variables('AllFiles')} as shown below

Create a table to store this data

CREATE TABLE [dbo].[ADLSMeta](
    [FileName] [varchar](max) NULL,
    [LastUpdated] [datetime2](7) NULL
) 
GO

Stored proc to process this array data

Create PROCEDURE dbo.Insertmetadata
@metadata varchar(max)
as
BEGIN
SET NOCOUNT ON;

INSERT INTO [dbo].[ADLSMeta]([FileName],[LastUpdated])
SELECT b.[FileName],b.[LastUpdated]
FROM OPENJSON(CONCAT('[',REPLACE(@metadata,',','],['),']')) a
CROSS APPLY OPENJSON(CONCAT('[',REPLACE(a.[value],'|','","'),']'))
WITH([FileName]   VARCHAR(max)  '$[0]'
    ,[LastUpdated]  datetime2(7)  '$[1]'
    ) b;


END

The result look like this

Collectives™ on Stack Overflow

ADF: passing the output of a Set Variable to Azure SQL database table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related