Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Manipulating Files with SSIS

  • 11 November 2012
  • Author: Kathi Kellenberger
  • Number of views: 10355
  • 0 Comments

One of the most common reasons to use SQL Server Integration Services (SSIS) is to import data from or export data to text files. Maybe the data in these files is delimited by pipes (|) or commas. Maybe the files are in XML format.  Regardless of the type of file, SSIS is a great tool to work with this data.

When importing a file, the data is loaded into memory of the server where any of the available transforms are at your disposal for manipulating the data.  One thing you could do is calculate aggregates with the Aggregate transform or create a derived column with, you guessed it, the Derived Column transform.  Once you are done working with the file, you can easily archive it using the File System Task.

While these tasks and transforms are great to use, some users of SSIS have created their own to provide more functionality than available out-of-the-box.  Pragmatic Works is a leader in crafting new and improved tasks and transforms with Task Factory.

So, besides the transforms that can be used on any data, what does Task Factory bring to the table to work specifically with files?  When I take a look at the SSIS Toolbox, I see three: The Download File Task, the File Properties Task and the Secure FTP Task.


 
The purpose of the Secure FTP Tasks is obvious; it enables you to use SFTP within your package since only regular FTP is available with SSIS.

The Download File Task allows you to download a file from a website. While downloading a file from an FTP site is built in to SSIS, downloading a file from a site is not. I actually worked on a project using DTS about 10 years ago where I needed to download files from a government site each night and had to come up with a way to do it. It would have been much easier if I had Task Factory available back then.

To see how this works, I created a new SSIS 2012 package and added the Task Manager Download File Task.

After double-clicking the task, I took a look at the properties.  The first step is to create an HTML connection manager.  Like any built-in task, I can create the connection manager from the drop-down box or from the Connection Managers section.  The site I am using is http://www.textfixer.com.  Since this site doesn’t require any credentials or certificates, that is the only property I need for the connection manager. 
 
The next task property to configure is the path to the file I want to download. Just like any other task, I can choose an expression, a variable or a hard-coded path.  The Download File Task expects the full path to the file here.  I am downloading a list of the most commonly used English words from http://www.textfixer.com/resources/common-english-words.txt.

Finally, I enter the path where I wish the file to land. I can enter a hard-coded path, an expression, a variable or a Connection manager.  Here is a look at the properties once I had them all completed.


 
After running the package, I took a look at the file to see if the task was successful.


 
The final task dedicated to working with files from Task Factory is the File Properties task. To learn about this task, I added it to my project.


 
This task requires the source of the file path within a connection manager or a variable.  You then can save any of the many file properties into variables. Here is my task configured to pick up a few of the properties from the file I just downloaded.


 
This would be great when processing files that another system exports and you don’t know exactly when those files will be ready. You can look at FileInUse property to skip files that aren’t ready and pick them up later.

These tasks are just a couple of the many available with Task Factory.  They are easy to use and will make developing your SSIS packages faster than ever!

 

 

Print
Categories: SQL Server
Tags:
Rate this article:
5.0

Please login or register to post comments.