Introduction:

SQL Server Integrations (SSIS) is a powerful tool for data integration and transformation. One of the tasks in SSIS is the FTP task, which allows you to transfer files to and from an FTP server. However, there are some complexities and limitations when it comes to configuring the FTP task in SSIS. In this article, we will explore these complexities and discuss workarounds to overcome them.

The Limitation:

In the SSIS editions of Business Intelligence Development Studio (BIDS) 2005/2008, you cannot set the ServerPassword property of the FTP connection using expressions. This limitation can be frustrating for SSIS developers, especially those who are not programmers and find it difficult to debug scripts.

The Workaround:

A common workaround to this limitation is to use a Script Task in SSIS to set and reset the FTP’s ServerPassword property. However, this workaround can be problematic for non-programmers and may lead to frustrations. Some developers even turn to third-party solutions to extend the limitations of SSIS.

The Solution:

Fortunately, the FTP component available in SQL Server Data Tools now comes with the capability of dynamic (re)initialization of the ServerPassword property. This means that you can now configure the FTP task to use expressions, making it easier to set and reset the ServerPassword property.

Implementation:

In order to demonstrate the dynamic download of files stored in different FTP remote directories, we will create an Integration Services Project (ISP) using SQL Server Data Tools. We will also create a database table to store FTP connection strings and use variables to store dynamic content. Additionally, we will configure the package control flow to execute SQL tasks, a foreach loop container, and the FTP task.

Conclusion:

The FTP Task in SQL Server Data Tools has been enhanced to allow the setting and resetting of properties such as the ServerPassword. In this article, we have discussed and demonstrated how to configure the FTP task to use expressions, overcoming the limitations of SSIS. With this new capability, SSIS developers can now easily work with FTP connections and transfer files without the need for complex scripts or third-party solutions.

Download the attached package demo to explore the implementation in detail.

Cheers,

Sifiso