SSIS Is Not Just for SQL Server

Tim Mitchell, 2008-05-21 (first published: 2007-06-25)

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.

Rate

5 (2)

Share

Share

Rate

5 (2)

Related content

How to Asynchronously Execute a DTS package from ASP or ASP.NET

The Data Trasformation Services are a powerful tool, and sometime its features are so useful that you’d like to invoke a DTS package not only from SQL Server but from an external program.

To do this you have several choices: you can use the DTSRun.exe tool or you can do it leveraging the SQL-DMO features.

Unfortunately if you’re developing a web application (ASP, ASP.Net or whatever you use) none of them seems to be the right choice: too much problems, too much effort and a very modest results. In addition none of these solutions can be called asynchronously: if you just need to implement a “fire-and-forget” technique, you just cannot do that!

Additional Articles

2005-05-24

2,663 reads

Easy Package Configuration

One of the age old problems in DTS is moving packages between your development, test and production environments. Typically a series of manual edits needs to be done to all the packages to make sure that all the connection objects are pointing to the correct physical servers. This is time consuming and gives rise to the possibility of human error, particularly if the solution incorporates many DTS packages. Many companies have provided their own custom solutions for managing this problem but these are still workarounds for a problem that is inherently DTS's.

Additional Articles

2004-12-14

1,761 reads

For Loop Container Samples

One of the new tasks in SQL Server 2005 is the For Loop Container. In this article we will demonstrate a few simple examples of how this works. Firstly it is worth mentioning that the For Loop Container follows the same logic as most other loop mechanism you may have come across, in that it will continue to iterate whilst the loop test (EvalExpression) is true. There is a known issue with the EvalExpression description in the task UI being wrong at present. (SQL Server 2005 Beta 2).

Additional Articles

2004-11-18

3,221 reads