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