BACKGROUND

A colleague recently introduced me to the complexities that SQL Server Integrations (SSIS) FTP task can sometimes introduce. It turns out that in the SSIS editions of Business Intelligence Development Studio (BIDS) 2005/2008 you can’t set the ServerPassword property of the FTP connection using expressions. A common work around to this, which has been discussed in numerous technical websites, is to make use of a SSIS's Script Task (which can be in C# or VB.net - BIDS 2005 allowed for VB.net edit only) to set and reset FTP's ServerPassword property. But, what if the script throws an error? Most SSIS developers are not necessarily programmers and although the script used for initializing the FTP task is often a few lines, it could still be problematic for non-programmers to try and make sense of it, especially when they need to debug it. Thus this work around could lead to frustrations for SSIS Developers, alas, my colleague endured such frustrations. Consequently, such frustrations (and sometimes for mere convenience) led to some SSIS/BI Developers ended up turning to third party solutions such as Pragmatic Work's Task Factory and CozyRoc to extend some of the limitations of SSIS.

Fortunately, the FTP component available in SQL Server Data Tools now comes with the capability of dynamic (re)initialization of the ServerPassword property. Thus, the aim of this article is to demonstrate a dynamic download of files stored in different FTP remote directories.

SOLUTION

The below sections and subsections will discuss and demonstrate (sometimes with screenshots) how we can configure the FTP task to use expressions. We will start of by looking the at software requirements for this demo to be successful. I will then take you through creation of objects, and configuring the SSIS package.

Prerequisite

• SQL Server Data Tools
• SQL Server Database
• FTP Connection

Create Database Objects

The first database object that we need to create is the table that will be used to store ftp connection strings for files that will be downloaded. I called this object tblReceiveFTP and created it in the AdventureWorks2012 database.

USE [AdventureWorks2012]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblReceiveFTP](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [tblLocalDir] [varchar](max) NOT NULL,
        [tblRemoteDir] [varchar](max) NOT NULL,
        [tblFileName] [varchar](max) NOT NULL,
        [tblSrvName] [varchar](max) NOT NULL,
        [tblSrvPassword] [varchar](max) NOT NULL,
        [tblSrvUserName] [varchar](max) NOT NULL,
        [tblLog] [varchar](100) NULL,
        [tblRunDate] [datetime] DEFAULT (getdate()) NULL,
    CONSTRAINT [PK_tblReceiveFTP] PRIMARY KEY CLUSTERED
     (
        [id] ASC
     )
  WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The second object is a stored procedure that basically calls the table object created above. The definition of the stored procedure is as follows:

USE [AdventureWorks2012]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[spReceiveFTPList]
as
SELECT
        [id]
       ,[tblLocalDir]
        ,[tblRemoteDir]
        ,[tblFileName]
        ,[tblSrvName]
        ,[tblSrvPassword]
        ,[tblSrvUserName]
FROM [AdventureWorks2012].[dbo].[tblReceiveFTP]
GO

Populate Table Object

Now that the database objects have been created, it's time to populate the table object with some dummy data (you can obviously replace this with real data):

INSERT INTO
         [AdventureWorks2012].[dbo].[tblReceiveFTP]
  (
         [tblLocalDir]
        ,[tblRemoteDir]
        ,[tblFileName]
        ,[tblSrvName]
        ,[tblSrvPassword]
        ,[tblSrvUserName]
  )
VALUES
         ('C:\Temp\DynamicFTP','/httpdocs/Sifiso/SQL/','Create_Extract_SQL_Job.sql','myftp1.net','ftpP@ss1','ftp-user1')
        ,('C:\Temp\DynamicFTP','/httpdocs/Sifiso/SSIS/','Generic_Extract.dtsx','myftp2.net','ftpP@ss2','ftp-user2')
        ,('C:\Temp\DynamicFTP','/httpdocs/Sifiso/XML/','logging_details.xml','myftp3.net','ftpP@ss3','ftp-user3')
        ,('C:\Temp\DynamicFTP','/httpdocs/Sifiso/SSIS/','MASTER_CONTROLLER.dtsx','myftp2.net','ftpP@ss2','ftp-user2')
        ,('C:\Temp\DynamicFTP','/httpdocs/Sifiso/CSS/','cssstyle.css','myftp4.net','ftpP@ss4','ftp-user4')

Create an Integration Services Project

The next step is to create an Integration Services Project (ISP) that will contain the package used to dynamically connect and download files from an FTP site. In order to do this we will need to launch SQL Server Data Tools (SSDT) and click New Project - Integration Services (under Business Intelligence installed templates) - Integration Services Project. Assign the name to the project i.e. DynamicFTP and click OK to launch your project (prior to clicking OK you can also change the default location of the project, solution name, and add your project to source control system). After your project has launched, go to solution explorer (View menu - Solution Explorer) and you will normally have a package called Package under the SSIS Packages folder in your project. Let's rename that to DownloadDynamicFTPFiles.dtsx.

Your Solution Explorer should now look as follows:

Add Variables

Whenever we set out to create a dynamic SSIS Package, it is considered best practice to make use of variables to store dynamic content. Thus, in this step we are going to add variables that will be used by our package. In order to create package variables, right click on the control flow pane and select "Variables".

I have identified the following variables as required in our package (you don't have to type them out - a link to the copy of this package we working on will be provided for download at the end of the article):

Set-up Package Connections

We are going to require two connections: an OLE DB connection and FTP connection. We don't need to set these connections as project connections, thus, we will just make them package connections (for difference between project vs. package connections see here).

To add an OLE DB connection, look for "Connection Managers" - it is usually at the bottom pane of your package. Inside the pane, right click and select "New OLE DB Connection". This will bring a Configure OLE DB Connection Manager window. Click New to setup a new connection. Another window, Connection Manager will appear and then provide your Server Name and Database details of where your tblReceiveFTP table is stored. Click Test Connection, and if the connection is successful click OK. If the testing of the connection is unsuccessful, make sure that you have correctly configured your Server Name and Database properties.

One you return back to the Configure OLE DB Connection Manager window, click OK as well. The connection you have just set-up should now appear in the Connection Managers pane. The naming convention of the connection you have just set-up will be in a form of servername.database, you can rename this to simpler terms such as the database name which is what I have done for my connection.

Your Connection Manager pane should look as follows:

The second connection that we need to add is an FTP connection. Therefore, still on the Connection Manager pane, right click and select New Connection. An Add SSIS Connection Manager window will immediately appear and under connection manager type. Look for FTP and click Add. Shortly after that an FTP Connection Manager Editor will appear. Configure the Server Settings and Credentials boxes as well as click Test Connection once you are done. If the connection is successful click OK. If the testing of the connection is unsuccessful, make sure that you have correctly configured the properties in Server Settings and Credentials boxes.

The FTP connection is currently static, and we need to make it dynamic so it can connect to different FTP directories. In order to make the connection dynamic, on the Connection Manager pane right click the FTP connection and click on properties. By default, the properties should appear just below the solution explorer (if you can't see the window, go to View menu and click Properties Window). On the properties window of the FTP connection, look for the Expressions property and click the ellipsis button (the button with bottom-centered dots). You will then be redirected to a Property Expressions Editor. Ensure that you have the following properties mapped:

Click OK to save and close the Property Expressions Editor window.

Your Connection Manager pane should have the two connections with a small fx icons next to your FTP connection name to indicate that it is configured to use expressions (I renamed the FTP Connection to FTP selectSifiso):

Configure the Package Control Flow

Our control flow will consist of Execute SQL Tasks, Foreach Loop Container, and the FTP Task.

Let's drag the Execute SQL Task (EST) from SSIS Toolbox into the Control Flow. (Another way to get the task into a control flow is to double click on the task and it will be added to your working pane). Remember SSDT has Toolbox and SSIS Toolbox windows; this is different from previous Microsoft BI IDE such as BIDS as it had only Toolbox. Patrick LeBlanc discusses how to locate your SSIS Toolbox here.

Back to the EST on the Control Flow, right click it and choose Edit. This will open an Execute SQL Task Editor window of which I configured the General Tab as follows:

Still on the Execute SQL Task Editor, let's click on the Result Set tab and configure it as follows:

Click OK to save and close the Execute SQL Task Editor.

The above EST, which I named as "SQL - Get List of FTP Files", basically executes the proc "spReceiveFTPList" and stores the data in an "objFTPFiles" variable.

Let's now go back to the SSIS Toolbox and click and drag the Foreach Loop Container (FLC) into the Control Flow pane.

Before you configure the FLC, let's join the existing EST to the FLC. By default the connecting arrow to the two objects, also called the precedence constraint, will be green, and we want it to be like that because we want to execute the FLC on condition that the EST executed successful.

Back to the FLC, right click it and click on "Edit". The Foreach Loop Editor window appears and under Collection tab, ensure that you configure it as follows:

Let's proceed to configure the Variable Mappings tab of the FLC.

Click on the Variable Mappings tab, just below the Collection tab, and configure it as follows:

Click OK to save and close the Foreach Loop Editor.

One last setting on the FLC is to go to its properties and set the property of ForceExecutionResult to Success. We are doing this because say during the (re)initialization of the FTP connection one of the connection strings throws an error, normally the FLC will stop at that iteration and not proceed to fetch next sets of connection strings - even if they are correctly configured. Thus, by setting this property to Success we are basically forcing the FLC to ignore whatever failure it may encounter during a given iteration and skip to the next iteration.

That concludes the setting of the FLC.

Now let's drag the FTP Task into the FLC and call it FTP - Download Files. Right click the FTP task and click "Edit". The FTP Task Editor comes up and the General tab should be configured as follows:

The following tab, File Transfer, should be configured as follows:

Final configuration of the FTP task is to ensure that, under the Expressions tab, you map the RemotePath property to FullRemoteDir variable.

Remember we configured the FLC to skip errors until the iterations are complete? What we need to do now is to configure exception handling mechanism such that despite skipping bad iterations, we create a log per iteration. In order to do this, let’s drag the two Execute SQL Tasks into the FLC, just below the FTP task and connect the FTP task to both the Execute SQL Tasks. Pick any FTP task join to the EST and ensure that it is joined on Failure - this will be used to log error connections as shown below:

The second join is an inverse of the first in that it is used to log successful execution of the FTP Task and it precedence constraint is configured as follows:

Let's configure the EST used to log failed connections by right clicking and choose to "Edit". Under the General tab in the Execute SQL Task Editor ensure that you have configured as follows:

Under the Expressions tab, configure the SQLStatementSource property as follows:

The final task to configure is the EST used to log successful execution of the FTP task. Right click the EST and select "Edit". Under General tab of the Execute SQL Task Editor, ensure you have configured it as follows:

Finally, under the Expressions tab, configure the SQLStatementSource property as follows:

CONCLUSION

The FTP Task in SSDT has been further enhanced to allow the setting and resetting of properties such as the ServerPassword. This article has discussed and demonstrated how to get that functionality working.

As promised, see the attached package demo for download.

Cheers,

Sifiso.