SSIS vs Informatica

In a recent outing, I met with a product vendor wanting to do ETL for a Siebel CRM product from Oracle database.

Trust me, I was quick to recommend SSIS which I am very familiar and very recently just had a laudable success extracting and Migration of Billing Information from a Legacy ENDAN (SQL Server based) application to Singl.eView from Intec.

On further discussion with the vendor, he would prefer the use of Informatica (first time getting to know this). I was able to show him lots of cool features SSIS have to offer.

Afterwards, I decided to know more about Informatica and found this on the net

Informatica does not have an equivalent of SSIS’s UNION component . This is a big problem for what I’m trying to do because a lot of the logs that I’m trying to load are in different folders (to represent the different web servers). Informatica requires 2 pipelines (see #3 below) to extract this data whereas SSIS can just have 2 source adapters in the same data-flow and UNION the data together.

SSIS’s method of loading multiple files (i.e. the "Multiple Flat Files Source Adapter") is a lot better than Informatica's. With Informatica you have to, externally to Informatica, build a list of files to process and then pass that list back into Informatica. To make this dynamic at runtime you would have to shell out to an external process to produce the file list. This is not pleasant – especially compared to SSIS’s very simple method of just specifying “*.log” in the source adapter.

I have a pipeline (built in both SSIS & INFA) that filters out all comment lines from the web logs, extracts all the individual fields (e.g. Timestamp, ClientIP address, URI, Referrer etc...), and inserts into a SQL Server table. The SSIS pipeline is working on ~150000 rows and completes in ~23 seconds. The Informatica method (which uses 2 pipelines cos of #1 above) takes ~45 seconds. Even 1 Informatica pipeline on its own (working on about half the records) took ~27 seconds. Bear in mind also that the SSIS pipeline was run from BIDS and as I have previously mentioned, BIDS places a large overhead on the execution of a package. I would suppose that Informatica does not have the same restriction. In short, SSIS seems a lot quicker!

SSIS’s method of dynamically setting the destination at runtime (i.e. configurations) is a lot better than the Informatica equivalent. With Informatica you have to configure each task to use the dynamic value, and setting up the value itself is a manual process because you have to manually handcraft what is termed a parameter list. SSIS does this using the configuration wizard. Let me say again, EVERY Informatica task that uses an external connection has to have a dynamic configuration set up using this method; with SSIS you do it in one place, on the Connection Manager.

This even has more indept comparison
http://download.microsoft.com/download/1/0/3/103fd39e-3ca4-4db7-a087-1263dc6ed0b1/CompIntTools.pdf

Duplicate Filtering in SSIS

Having being frustrated on lack of duplicate filtering in SSIS, I decided to take a work around, first dump the data in a temporary table and pick it up with "select distinct … “ in the next task. This work around works though but fails in instances that the filter had to be applied on some of the columns selected unlike “select distinct ..” that applies across all the selected columns.

Alas!!! SSIS provides this in the Sort Task and can be applied just as I want it in my project. Simply select the columns you want to filter on as the sort keys and check “Remove rows with duplicate soft values”.  Voila! problem solved.

 

IMHO, I consider the sort task an overkill for this purpose but no other alternative unless the developer will not mind building a dedicated filtering task for SSIS. SQL Central provides samples on how to build SSIS task.

Record Value Frequency In SSIS

I blogged last week about a problem on Record Value Frequency in SSIS. I suggested in the blog post on how to go about taking care of this.

“The only way I could see out of this is to write a CLR function to achieve this which I am feeling so lazy to do but still has to be done.”

This I finally had time to do and pasted below is the simple CLR User Defined Functions (UDF)

static readonly Dictionary<SqlString, SqlInt32> myDic = new Dictionary<SqlString, SqlInt32>();

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString SvSequence(SqlString colValue)

    {

        SqlInt32 nextValue = 1;

        if (myDic.ContainsKey(colValue))

        {

            myDic[colValue] += 1;

            nextValue = myDic[colValue];

        }

        else

            myDic.Add(colValue, 1);

        // Put your code here

        return nextValue.ToSqlString();

    }

    [Microsoft.SqlServer.Server.SqlFunction]

    public static SqlString RestartSequence()

    {

        myDic.Clear();

        return new SqlString();

    }

Notice the readonly used in the static declaration for the dictionary. This is enforced by the compiler for static class member as a requirement. I also had to create a reset function which IMHO is not that cool but I need a way to reset this as the value kept increasing each time the UDF is called.

dbo.RestartSequence ;

select AccountId, dbo.SvSequence(AccountId) from dump

Record Value Frequency

First I’d like to quote my recent tweet on this issue

Have always considered SSIS to be extremely flexible but lately found wrong. Nevertheless, yet to find any ETL too that can beat it

I think I may have been wrong to blame this on SSIS but rather on limitations of TSQL itself. My situation is a need to have frequency of a record value indicated in a column as indices as below

Value
SEQNR
1
1
2
1
2
2
4
1
4
2
4
3

I tried to use Rank() with partition as shown in snippet below but got a different result. Also ‘Bing(ed) this but came up void of a similar issue.

select value, Rank() over (partition by Value order by Value)

This is like to use and inject into SSIS package. Doing this with combination of SSIS task is not quite straightforward.

The only way I could see out of this is to write a CLR function to achieve this which I am feeling so lazy to do but still has to be done.

Ftp’ing files in Folder in SSIS

Recently, I came across a need to copy all csv files generated from an SSIS process to a Unix server. FTP became handy for this purpose. The FTP SSIS task will not work with folder/directories – came up with error message

File Usage type of connection "xxxxxx" should be "FileExists" for operation Send

This error message is quite misleading. The interpretation is that once you the File Connection Manager being used in the FTP task has a property Usage Type and this Usage Type must be set to “File exists”. This automatically limits the usage of the FTP task to files rather than folder/directories.

The solution is to use the Foreach Loop Container task and place the FTP task within it. Literarily, the loop container enumerates all the files and place them in a variable. This variable is then used in the FTP task.

 
Foreach Loop Container with FTP task embedded

Loop Container configuration

FTP task configuration