This article demonstrate how to send multiple files from local system folder to FTP Server.
If you want to send only single file from your local system to FTP Server then you can refer post Send files to FTP Server.
Lets see the step by step implementation of Sending multiple files from local system to FTP Server.
As you can see there are one folder in our local system, which contains three files.
Also you notice that, one file has .txt extension while other two files have .xls extension.
These files will be sent to FTP server using SSIS FTP Task.
Lets take a look at FTP server and you can see it has one directory named Datafiles, which contains three files.
Lets create a new SSIS package in existing project, right click on SSIS package folder then select New SSIS Package.
Lets give it an appropriate name as SendMultipleFilesToFTPServer.
As we need to send multiple files from local system to FTP server, for this we will take a Foreach Loop Container, which repeats control flow for each member of a specified enumerator.
In Collection tab, select Foreach File Enumerator from Enumerator dropdown.
The Foreach File Enumerator enumerates files in a folder.
Once you select Foreach File Enumerator, it asks you to select folder path, and files.
Click on Browse button to select a folder that contains the files as shown below.
Now define a files type as “.” , as we want to send all files regardless file extension from local system folder to FTP Server, so used default expression which is “.” that means read all types of files regardless file extension.
In case, if you want to send only text format file then define file type as “.txt”, or define files type as “.xls” for excel file.
Now go to Variable Mappings tab, here we will create a variable to store file name and it’s extension.
You can select an existing variable or create a new by clicking on <New Variable..>.
Once you click on the <New Variable..>, the Add Variable window opens to create a new variable.
Lets specify the variable name as Var_FileNames, and
You can see, variable is created with default index value 0. Next click on OK button.
Now double click on FTP Task to configure the FTP connection manager.
You will see a FTP Task Editor window opens, now specify ftpconnection. You can select an existing FTP connection, If you already have created FTP connection else you can click on <New Connection..> to create new.
Once you click on <New Connection..>, a FTP Connection Manager Editor window open, now you need to specify ftp server name, user name or credentials.
Also you can verify FTP connection by clicking on Test connection button.
After that click on OK button.
Now in File Transfer tab, set IsLocalPathVariable as True, then in LocalVariable select Var_FileNames that we have created earlier so that Foreach Loop Container to loop over the files and stored the file names with extension in this variable.
Now select RemotePath, click on ellipses (…), a Browse for Directory window opens, now select directory and click on OK button.
Note that, If you do not have any directory you can specify root directory / .
Next select OverwriteFileAtDest as True, in case if you want to over write already existing file.
After that click on OK button.
Now we have configured the FTP task that will send multiple files to FTP Server.
Now click on Start button to execute the package.
You can see, package executed successfully.
Lets check the FTP Server to ensure whether the files are uploaded or not on FTP server, and you can see three files are sent to FTP Server.
Also Read..
SSIS – Load flat file data into SQL Server Table
SSIS – Load Excel file data into SQL Server Table
SSIS – Derived Column Transformation
SSIS – Conditional Split Transformations
SSIS – FTP task Send Files to FTP Server
