SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SSIS Is Not Just for SQL Server

By Tim Mitchell,

SSIS: Not Just for SQL Server

It's been said that when all you have is a hammer, everything looks like a nail.  SQL Server Integration Services is just such a tool, and can turn lots of difficult or troublesome tasks - some of which are unrelated to SQL Server data storage and retrieval - into nothing more than simple 16 penny nails.  Since I got started with SQL Server Integration Services (SSIS) over a year ago, I've found a number of uses for this product that would have required hours of coding or batch scripting.  This article will share a simple use of SSIS for data flow that does not even involve SQL Server proper.

A little background here... I ran into a task that required me to download web log files from a remote web server each day, run an executable to process each file, then archive the log file locally.  This was being done manually before the SSIS hammer got involved.  Even though this information is external to SQL Server, the nature of the task seemed to be a perfect fit for SSIS.  Automating this process was a quick and easy task with Integration Services.

I start off by creating a simple script task whose purpose is to set a couple of package variables.  The file name we are to download is for the previous day and is named according to the date.  The following snippet shows the logic to set those variables:

Next, I'll use another simple task, the FTP Task, to download the source file from the FTP server.  The RemoteFile name set in the script task above is used to download yesterday's file.  You can see that I am using the variables referenced in the above script task to dynamically set the file names in the FTP Task:


The next step is to create an instance of the very useful Execute Process task.  This element allows you to call an external exectuable from within SSIS, and also allows you to specify arguments for those calls.  In this case, I need to call the perl.exe executable and pass the name of the Perl script to run along with some other values (including the local file name set earlier).  The settings here are relatively straightforward; the only thing that requires a little tweaking is to set the argument properly, since this will need to be dynamic.  See below where I use an expression to set the name of the Argument to the local file I need to process:


You can also see that I am capturing the output of the call to perl.exe below, to allow me to review the statistics generated by the script.  As shown below, the StandardOutputVariable can be used to capture the command line output and  send it to another package variable:

Lastly, I use a Send Mail task to e-mail the output message in the PerlOutput variable to the operator to verify record counts.


SQL Server Integration Services is a very powerful data processing tool, and its uses with SQL Server are almost limitless.  Even more, this article has shown that SSIS can be used apart from SQL Server to simplify data processing in heterogeneous environments.
Total article views: 11509 | Views in the last 30 days: 16
Related Articles

Execute Process Task: Failed to lock Variable

Execute Process Task: Failed to lock Variable


Trying to download a file from HTTP via Script Task.

Trying to download a file from HTTP via Script Task.


Downloading multiple files from internet with SSIS and C#

This is a tutorial to download multiple files from internet using the SSIS Script Task.


Using the Script Task in SSIS to Process Data Files When They Arrive

Learn how you can process data files with SSIS as soon as they arrive using the Script Task.


Script task variable population

How to use a variable populated by a script task later in the data flow.