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.

Answer 1

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.

1. In my local FTP server, there is two text files. I need to copy them into an Azure SQL table.
   

2. At GetMetaData activity, I use Child Items to get the filelist.

3. At ForEach activity, I use @activity('Get Metadata1').output.childItems to foreach the file list.

4. Inside the ForEach activity, I use dynamic content @item().name to get the file path.
   source setting: sink setting:

5. 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
   

6. After I run debug, the result is follows: