Load FileName in SQL Table using Copy Data Activity

Question

I am newbie to Azure Data Factory. I'm trying to load multiple files of various states from FTP location into a single Azure SQL Server table. My requirement is to get state name from of the file and dump it into table along with actual data.

Currently, my source is FTP. Sink is Azure SQL Server table. I have used Stored Procedure to load the data. However, I'm unable to send file name as a parameter as shown below to the stored procedure so that I can dump it into the table. Below is the Copy Data component -

I have defined SourceFileName parameter in stored procedure, however, I am unable to send it via COPY Data activity.

Any help is appreciated.

I remember that in the source of the copy activity, there is an additional column option, you can get the filename via $$filename — Joseph Xu
Yes @JosephXu - the name of the file has state name like sample_CA (California) etc — Rameshwar Pawale
Yes, I saw that $$filename variable of Source part and tried to put it in Value ($$filename) of param as highlighted in yellow in above screenshot in Sink part. it throws an error — Rameshwar Pawale

Joseph Xu Joseph Xu · Accepted Answer · 2020-11-05T03:28:21

We can conclude that additional column option can not be used here. Because ADF will return a column(contain filepath) not a string. So we need to use GetMetaData activity to get the file list, then foreach the file list and inside a Foreach activity to copy them.

I've created a simple test, it works well.

In my local FTP server, there is two text files. I need to copy them into an Azure SQL table.
At GetMetaData activity, I use Child Items to get the filelist.
At ForEach activity, I use @activity('Get Metadata1').output.childItems to foreach the file list.
Inside the ForEach activity, I use dynamic content @item().name to get the file path.
source setting: sink setting:

So we can get the filename. Follows are some operations I did on Azure SQL.

-- create a table
CREATE TABLE [dbo].[employee](
    [firstName] [varchar](50) NULL,
    [lastName] [varchar](50) NULL,
    [filePath] [varchar](50) NULL
) ON [PRIMARY]
GO

-- create a table type
CREATE TYPE [dbo].[ct_employees_type] AS TABLE(
    [firstName] [varchar](50) NULL,
    [lastName] [varchar](50) NULL
)
GO

-- create a Stored procedure
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spUpsertEmployees]

@employees ct_employees_type READONLY,
@filePath varchar(50)

AS

BEGIN
set @filePath = SUBSTRING( @filePath,1,len(@filePath)-4)

MERGE [dbo].[employee] AS target_sqldb

USING @employees AS source_tblstg

ON (target_sqldb.firstName = source_tblstg.firstName)

WHEN MATCHED THEN

UPDATE SET

firstName = source_tblstg.firstName,

lastName = source_tblstg.lastName


WHEN NOT MATCHED THEN

INSERT (

    firstName,

    lastName,

    filePath

)

VALUES (

    source_tblstg.firstName,

    source_tblstg.lastName,

    @filePath
);

END
GO

After I run debug, the result is follows:

Load FileName in SQL Table using Copy Data Activity

1 Answers