FTP (File Transfer Protocol) is a network protocol that allows file transferring between machines. SSIS includes an FTP task that will download and upload data files and from an FTP location. Let’s find out how does it work and how do we configure it properly.
Let’s create and configure an FTP connection first.
- Find the Connection Manager tab (usually on the bottom area of the screen) and click on “New Connection…”;
- Once the “Add SSIS Connection Manager” opens up, find the “FTP” Connection manager in the list;
- The FTP Connection Manager Editor will open up:
- Under Server settings, enter the name of your FTP server and the port;
- If you don’t have an FTP server, you can always create an FTP site on your local machine, if you have Windows 10. If you need help with that, click HERE to read a guide on that.
- Under Credentials, use the username and the password that you use for authentication;
The rest can be left as it is:
- Checking the passive mode will allow the client to activate the connection, if left unchecked, the server will initiate the connection;
- Retries will dictate the number of retires allowed to establish a connection;
- The “Text Connection” button will check if the connection is valid or not.
- More info on the FTP Connection is available on the Microsoft Documentation website.
Let’s find out how the actual task works...
- Find the FTP Task in the SSIS Toolbox and drag and drop it on top of the Control Flow area;
- Double clicking one the task will open up the FTP Task Editor
FtpConnection – select an already created FTP connection or create a new one;
Name and Description – up to the user’s preference;
File Transfer Tab
The Local Parameters will allow the user to configure the source.
There are about 8 different operations:
- Send files – uploads files from your machine to the FTP server/site;
- Receive files – downloads files from the FTP server;
- Create a local directory – creates a directory on your machine;
- Create a remote directory – creates a directory on the FTP server;
- Remove local directory – deletes a directory on your machine;
- Remove remote directory – deletes a directory on the FTP server;
- Delete local files– delete files from your machine;
- Delete remote files– delete files from the FTP server.
The Remote Parameters will allow the user to configure the destination.
This section allows the user to evaluate the SSIS FTP Task properties as expressions.