How to pass dynamic parameter from flat file to FTP Task in ssis package?

How to pass dynamic parameter from flat file to FTP Task in ssis package?

SSIS  FTP Task – In this interesting post we will learn how to pass dynamic parameter to FTP task from Flat file .

I was working with FTP task before some days ,In which I need to download multiple files from multiple FTP’s , where credentials of all FTP was stored in one Flat file . I need to pass all the credentials one by one to FTP task and FTP task will take all parameters and download files from all FTP.

In the below article, we will be solving this practically by taking an example.

I have one file FTPCredentials.csv which have following columns:

  • FTPServer – store value of FTP server name
  • UserName – store username of FTP server
  • Password – store password of FTP server
  • LocalPath – store folder path where files to be download
  • RemotePath – store folder path from where files to download

Go To variables and create below variables

Variables

Step 1: create Data Flow Task and read Credentials File

Create one Data Flow Task . and inside Data Flow Task take one Flat File Source and read Credentials file.

DataFlowTask

Open Record set Destination go to custom Properties ->Variable Name

set variable to User::RecordsetOutPut

Recordset destination

Next in Input Columns It will shows all values of Credentials file as Input column in Recordset Destination.

InputColumns

clicks on ok and go to Control Flow.

On Control Flow drag ForeachLoop Container .  double click on it and go to Collection -> Enumerator 

choose “Foreach ADO Enumerator“. In Enumeration configuration , choose User::RecordsetOutput  as ADO object source variable.

Go to Variable Mappings set all variable Index according to fields in Credentials Files.

Foreachloop
VarialeMappings

Click on OK . now drag one sequence container inside  Foreach loop container ,which will pass all credentials to FTP task one by one in sequence.

Bring FTP task and configure with your FTP credentials using expression.

FTPTask

Complete package will look like below image.

FTP

click Ok and run the package .ForEachLoop will pass ftp credentials to FTP and FTP will execute and download files from different FTP server.

I hope you must have enjoyed our post on “How to pass dynamic parameter from flat file to FTP Task in ssis package?” . So, try these steps very carefully and still if you have any queries or issues then do comment below. We will really appreciate your feedback.

Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *