Microsoft® SQL Server™ Integration Services is the tool that connects the database to the world and works instead of us. I’ll show you how to extract simple data from the database, store the files in a the file system, rename them, upload them on FTP and report the errors to administrator if any.

In this first part I will create the package. Next I’ll implement it into SQL Server.

The steps we need are:
1. Create the directories for storing the files – C:\Temp\Source\, C:\Temp\ForUpload\, C:\Temp\Archive\. If they already exist, we use them instead of create them
2. Move the files from Source directory to ForUpload directory with changing of the name (adding date and time)
3. Upload the renamed files on FTP server
4. Move the files to Archive folder

First, create a new SSIS project (File –> New –> Project) in SQL Server Business Intelligence Development Studio.

New SSIS Project

Rename the package.

Rename package

Create Data Source – that’s the place where we will read the data from – MS SQL Server. In Solution Explorer, right click on Data Sources –> New Data Source…

Create Data Source

To use the Data Source, I create a New Connection from the existing Data Source. Right click on Connection Managers –> New Connection From Data Source…

Create New Connection from Data Source

Create the global variables for the directories.

Create global variables

Create the directories – Source, ForUpload, Archive. Drag the File System Task from Toolbox pane.

Create Folders

Right click on File System Task to set the properties of the folders.

Create Folder

For Exporting the data to flat file, first, I create Data Flow Task.

Create Data Flow Task

Data Flow Tab –> Toolbox –> Data Flow Sources (ADO NET Source), Data Flow Transformation Source (Sort), Data Flow Destinations.

Edit ADO NET Source. I have written the query in SSMS, but I can build it on this step.

Edit ADO NET Source

Edit Sort

Edit Sort Transformation

Create Flat File Connection – Delimited

Create Flat File Connection

I chose the delimiter ($) in the Columns section

The next step is to rename the files by adding to filename the date and time. I use Foreach Loop Container to cover all the files in the Source folder. I move them to ForUpload folder and rename them in one step. I do it like this, because I need to know on which step the execution is failed and in that case to handle the rest of operations manually.

Add Foreach Loop Container

Create the local variables for Foreach Loop Container. We need to combine the file path with file name while looping the folder. In the Filename variable we will store the names of the files. To the variable Dir_ForUpload_Filename we will add the date and time.

Create local variables for File Loop Container

I create expressions for Dir_ForUpload_FileName and Dir_Source_FileName variables. When I select the variable, the Properties windows (in the down-right corner of the screen) changes.

Create expression for variable

In the Expression field I write the expresisons:

1. Dir_ForUpload_FileName:

@[User::Dir_ForUpload]
+ SUBSTRING(@[User::FileName], 1, FINDSTRING(@[User::FileName], ".", 1) - 1)
+ "_"
+ SUBSTRING((DT_WSTR, 30)GETDATE(), 1, 4)
+ "-"
+ SUBSTRING((DT_WSTR, 30)GETDATE(), 6, 2)
+ "-"
+ SUBSTRING((DT_WSTR ,30)GETDATE(), 9, 2)
+ "_"
+ SUBSTRING((DT_WSTR, 30)GETDATE(), 12, 2)
+ SUBSTRING((DT_WSTR, 30)GETDATE(), 15, 2)
+ SUBSTRING((DT_WSTR, 30)GETDATE(), 18, 2)
+ SUBSTRING(@[User::FileName], FINDSTRING(@[User::FileName], ".", 1), LEN(@[User::FileName]))

2. Dir_Source_FileName:

@[User::Dir_Source] +  @[User::FileName]

Edit Foreach Loop Container. We mark a folder for loop (C:\Temp\Source\). We loop only the .txt files. In Variable Mappings section we add the FileName variable.

Setting Foreach Loop Container

Setting File System Task (inside the Foreach Loop Container)

Setting File System Task

FTP upload – drag the Foreach Loop Container and FTP task in it. I create local variable FileName – the same as in previous step. In Foreach Loop Editor I choose Foreach File Enumerator, C:\Temp\ForUpload\ in Folder field and *.txt in Files field.

Edit FTP task – I adjust the local and remote paths

Setting FTP task

The next step is loop for placing the files in Archive folder. I won’t explain it. It’s the same as the loop, I used to move and rename from Source to ForUpload folder.

The full schema of the objects looks like this:

All the objects

Next I will implement the package into SSMS, I will create Scheduled Job, which will include operator notification. While executing the Job in real-time working enviroment, I will decide whether to add the Send Mail Task in SSIS package.

Source files

Félicitations