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
|
Great,
ReplyDeleteVery good and easy understandable format..
Thanks,
Dhanunjay
I am beginner to Informatica with some previou knowlege on SSIS. This article helped me to understand the differences and learn well. Thank you!
ReplyDeleteThank You for the valuable information.Nice Blog
ReplyDeleteInformatica Cloud Training Course Online
Informatica Cloud certification Training
Can you let us know the few aggregate functions which are there in Informatica but not in SSIS
ReplyDelete