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.

Conclusions

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.