How to pass dynamic parameter from flat file to FTP Task in ssis package?khushbu Thakkar
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
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.
Open Record set Destination go to custom Properties ->Variable Name
set variable to User::RecordsetOutPut
Next in Input Columns It will shows all values of Credentials file as Input column in Recordset Destination.
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.
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.
Complete package will look like below image.
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.