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.
Rename the 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…
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 the global variables for the directories.
Create the directories – Source, ForUpload, Archive. Drag the File System Task from Toolbox pane.
Right click on File System Task to set the properties of the folders.
For Exporting the data to flat file, first, I 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.
Create Flat File Connection – Delimited
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.
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.
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.
In the Expression field I write the expresisons:
@[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]))
@[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 File System Task (inside the Foreach Loop Container)
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
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:
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.