Saturday 22 September 2012

Differences between Informatica and SSIS(SQL Server Integration Services )

You all must be aware of Informatica which I have already explained in my previous posts.
 
But SSIS is a different ETL tool.SQL Server Integration Services (SSIS) is Microsoft’s platform for a new generation of high performance data integration technologies which will ship with SQL Server 2005.


  • The key Difference between the two is the type of expression language used to derive the data
SSIS
Informatica
Mathematical
Character
String
Conversion
Date/Time
Date
NULL
Numerical
Type Casts
Scientific
Operators
Special

Test



  • Sorter Transformation in Informatica and Sort Transformation in SSIS are similar but in SSIS duplicates are removed by setting the Eliminates Duplicates property toTRUE whereas in Informatica, the same thing is accomplished by checking the Distinct property.



  • In Informatica and SSIS they follow modular approach but the only difference is that SSIS builds modularity into its packages through use of the container hierarchy & Informatica promotes building libraries


  • Informatica uses Sequence transformation to generate the Surrogate key. In SSIS we don’t have Sequence transformation. Instead we use Script transform component for the same purpose

  • Both of them uses Aggregate function but SSIS contains few aggregate functions only


  • Informatica uses Debugger in Informatica Designer to debug the mapping and SSIS’s debugging capabilities are provided through the use of Visual Studio 


Now we will see what the basic difference in the transformation level between the two ETL tools
Informatica
SSIS
Description
Aggregator Transformation
Aggregate Transformation
The Aggregator transformation used to performs aggregate calculations, such as averages and sums.
Expression
Transformation
Derived Column Transformation
Manipulates data in the transformation pipeline using expressions.
Filter Transformation
Conditional Split Transformation:
Filters data in the transformation pipeline.
Joiner
Transformation
Merge Join Transformation
Used to Join two set of data
Lookup
Transformation
Lookup Transformation
Fetches data from external table
Router
Transformation
Conditional Split Transformation
Routes data in the pipeline to different places depending
on the data and others to default group.
Sorter
Transformation
Sort Transformation
Used to sort data ASC or DSC


Below are some other difference between the terminologies in Informatica and SSIS
Informatica
SSIS
Description
Source
Connection
It contains the source data
Source Qualifier
Source Adapter
The component used to fetch data for the transformation pipeline.
Target
Destination Adapter
Target is the place where the data is loaded into
Transformation
Transformation
Transformation used to apply business logic on data
Workflow
Control flow or package
Collect all the designed objects into a single, executable, unit of work
Worklet
Sequence Container
Logically group related components into a single unit of work

4 comments:

  1. Great,

    Very good and easy understandable format..

    Thanks,
    Dhanunjay

    ReplyDelete
  2. I am beginner to Informatica with some previou knowlege on SSIS. This article helped me to understand the differences and learn well. Thank you!

    ReplyDelete
  3. Can you let us know the few aggregate functions which are there in Informatica but not in SSIS

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...

ShareThis