How to FTP a Dynamically Named Flat File

Introduction

In this article I explain how to create an SSIS Project that outputs a dynamically named flat file to a specified location using a variable and then sends the output file to a FTP server. In a second article, I explain how to run this SSIS Project as a SQL Job from within SSMS.

Scenario

My company created a requirement to read data from a SQL table and output to a flat file ‘xxx.xxx’ using a specific naming convention and then send the output to a FTP Server.

“Can it be done?” I was asked.

“Sure”, I said, “Let me finish my coffee.”

OK. I have some knowledge of SSIS as I have used it in the past, but not since the SQL Server 2005 days. Like many SQL projects I have previously worked on, my journey began with ‘Google’ followed by a visit to “sqlservercentral.com”. My research into this problem taught me a few new techniques, such as how to use variables within SSIS Packages. I was introduced to the new Control Flows, such as the FTP and Script Tasks as well as the dark art of scripting using a bit of Microsoft Visual C++.

On that note let me begin.

Create a Database

Let’s create a dummy database to test with. We can reference this test database later in this article. To create the dummy database open up SQL Server Management Studio and run the code below. The full script is attached in the references section below.

A new SQL Login, [sql-central] with password ‘sql123’, will be created as a sysadmin.

Create an SSIS Project

In this step, you will create your Integration Services Project. You will use Visual Studio 2013 to do this. First, open Visual Studio and create a new ‘Integration Services Project.’ Give the project a meaningful name. I called my project ‘FTP’. My solution is shown below.

Creating Variables

In the next step, you will create a variable. The idea of using a variable came from an article I read by decipherinfosys, entitled “SSIS: Exporting data to a text file using a package”. The variable is used to specify the location of the destination text file.

In the Control Flow pane, right click and select Variables. Create a new variable named ‘FileLocation’. In the ‘Value’ field type the path where you wish to save your destination text file. I used ‘C:\sql-central.’

Add Data Flows

This step has you adding a Data Flow Task and configuring an OLE DB Source to connect to the dummy database created in the first step. To start, drag and drop the Data Flow Task from the Control Flow Tool Box into the Control Flow Designer as shown below.

Double click on the Data Flow Task. Drag and drop an OLE DB Source from the SSIS Toolbox as shown below.

Double click the OLE DB Source task to open the OLE DB Source Editor.

Now you want to connect to your database. Create the new OLE DB source connection manager by clicking on the “New” button on the OLE DB Source Editor. This opens up the Configure OLE DB Connection Manager. Select ‘New’ to open the Connection Manager.

Next you will configure a connection to the dummy database.

In Server Name type ‘localhost.’

In Log on to the server select ‘Use SQL Server Authentication’

For user name enter ‘sql-central’

For password enter ‘sql123’

Your connection manager should look as follows;

Test that you are able to connect to the dummy database.

Retrieving Data

With the connection setup the next step is to retrieve some data from our newly created source. The data access mode option in the OLE DB source editor gives us various options of retrieving the data from the database. These options are:

Table or View

Table Name or View Name Variable

Sql Command

Sql Command from Variable

To retrieve data from my OLE DB Source I used the SQL Command option as I wanted to retrieve data from a table in a database. In the SQL command text pane write the following SQL command:

SELECT Contract, ContractID, ContractDescription
FROM Contracts

Select ‘Preview’. The results of your query will be returned as shown below.

Add a Destination

In this step, you will configure a Flat File Destination and a Data Flow Task using the variable created in step three. This will be the location the output destination file is saved to when the package is executed.

On the Data Flow tab, drag and drop the Flat File Destination from the Other Destinations section in the SSIS Tool Box. Drag the path (blue arrow) from the OLE DB Source and join to the Flat File Destination as shown below. This will add the connection to our file to which we want to transfer data.

Double click on the Data Flow Task. Drag and drop an OLE DB Source from the SSIS Toolbox as shown below.

Double click on the Flat File Destination task. This opens up the Flat File Destination Editor. Click on ‘New’ to create a new Flat File Destination Connection Manager. It will open up a Flat File Format window listing the options shown below.

Initially I chose ‘Delimited’, however on review of the output destination text file I was advised the format needed to be ‘Fixed Width.’ After a bit of trial and error I settled on the ‘Ragged right’ option.

Click on OK and SSIS will open up the GUI for configuring the connection manager for the Flat File Destination. Rename the Connection Manager Name if desired. For the File Name Option type, the path referenced is the variable created in step 3, ‘C:\sql-central.’

Click ‘OK’.

Click Mappings. Confirm the mappings are correct and then click OK.

Specify the Location

In this step, we will use the variable created in step 3 to specify the location to save the destination text file. Click on the Flat File Connection Manager from ‘Connection Managers’ pane.

In the Properties pane, scroll down and select the ellipses next to Expressions.

The Property Expressions Editor will be displayed.

From the Property menu list select Connection String.

Click Expression. This will open the expression builder. Expand Variables and Parameters. Drag parameter created earlier, User::FileLocation, into the expression pane as shown below.

Next you are going to configure the Script Task to name the destination text file dynamically. To do this click Edit Script button on the Script Task Editor. This will open up a dialog where you will write the code to generate the file name. I was asked to generate a destination text file in a specific format ‘xxxxddmmyyyy.txt’

The article I have referred to previously by decipherinfosys advised using the code shown below to generate the dynamic file name for our destination file.

The revised code compiled and generated a dynamically named file however the format was not quite right as the ddmmyyyy was applied as dmyyyy. I amended the DateTime.Now.Day and DateTime.Now.Month to include .ToString("d2").

To test the destination text file is dynamically named execute the project by clicking Start.

The dynamically named text file is created to the location specified in the variable created in step 3.

The FTP Task

In this step, you will add an FTP Task to send the dynamically named destination text file to a FTP Server. To do this whilst in the Control Flow, drag the FTP task into the design window as shown below.

Double click the FTP Task to launch the FTP Task Editor. With the General tab selected in the left pane, select <New Connection> from the menu list next to FtpConnection as shown below.

Enter your FTP connection details.

Test the connection. It should succeed. If not, double check your credentials.

Next you are going to specify the location of the file you want to FTP. With File Transfer selected in the left pane, set IsLocalPathVariable to ‘True’. For LocalVariable, select the variable created in step 3 as shown below.

Your settings should now look as shown below.

To test execute the project by clicking Start.

Build

The final step is to Build your project. This will create a file with a .ispac extension within the project's ‘bin' folder. The .ispac file will be used in a subsequent article.

To create the .isac file right click your project, FTP in my example, then select Build.

This creates an FTP.ispac file under the project's 'bin' folder as shown below.

Summary

This article explains how to extract data from an OLE DB Source, and then using a SQL command run against the OLE DB Source to retrieve data to output to a dynamically named destination text file in a format specified in Microsoft Visual C+ code within a Script Task. The destination text file ‘xxx.xx’ is then sent to a FTP server whose address is specified in the FTP Task.

In a subsequent article I explain how to run the FTP Project as a SQL Job from within SSMS.