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
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