2014-04-02T12:28:00Z

Microsoft SSIS vs. Informatica PowerCenter - which solution has better features?

Ariel Lindenfeld - PeerSpot reviewer
  • 7
  • 146
PeerSpot user
9

9 Answers

it_user91929 - PeerSpot reviewer
Vendor
2014-04-10T14:25:04Z
Apr 10, 2014

Like anything ETL related, it really depends on what your overall goals are for the tools you chose. No doubt about it, Informatica can do what SSIS does and more--most notably data cleansing for addresses, geo locations, etc. SSIS is really less about data cleansing and more about broad data transforms and data migrations. As @reviewer99681 states, the cost for maintaining Informatica is much higher than SSIS--so if all you really need to do are common transformations (lookups, datatype, merges, column dubs, etc.) and you have the integration services installed on a SQL server instance, I would try utilizing that first. One thing that is noteworthy about SSIS is its flexibility regarding extensibility through its scripting components. The scripting components are not like Informatica's SQL-like scripting, but are intended to avail the full suite of libraries in C# to the developer. It is possible to utilize Java from Informatica, although in a much more limited way. I have used SSIS to import data from multiple remote vendor sites, scrape web pages, manipulate the collective data-- all within the scripting components available from SSIS. Informatica simply isn't designed with developers in mind. The focus on Informatica is to provide a rock solid, server-based tool suite for manipulating and cleansing data designed for non-developers.

Product comparison that may be of interest to you
GM
Real User
Top 5Leaderboard
2016-11-01T15:24:11Z
Nov 1, 2016

Is the question regarding Informatica or SSIS?  I can't speak for Informatica but SSIS starting with 2012 version allow environment controlled parameter values. What this means is the ETL changes automatically based on where it's running if you use the sql agent scheduler. If you're using another scheduling tool then it's up to that scheduler to call SSIS packages with the correct parameter values.

it_user326337 - PeerSpot reviewer
Consultant
2016-11-01T08:37:20Z
Nov 1, 2016

How has automated parameter-file handling performed more recently? What has or has not changed?

it_user192060 - PeerSpot reviewer
Real User
2015-02-09T20:24:47Z
Feb 9, 2015

Parameter file handling in Informatica can be automated very easily using tables and Informatica itself. I have built a process in Informatica that will build parameter files dynamically. No manual creation or movement across environments.

GM
Real User
Top 5Leaderboard
2014-04-02T20:01:50Z
Apr 2, 2014

Two things
1) we already had this discussion on a LinkedIn group if you can find it. If you have questions about pros/cons of SSIS specifically - see my review of SSIS on this site. If I had one thing I'd say is significantly better with SSIS than Informatica, its parameter handling. Given the amount of money you pay for Informatica and SSIS is essentially free for most organizations, I'm rather disapointing for Informatica not to have resolved having to do parameter file handling by now. SSIS as of 2012 version solved that problem.

2) @Carlos comment "interact...out of luck" might seem misleading...since SSIS works with pretty much any database - if you can create ODBC or OLEDB connection, you can talk to it.

But it is true that SSIS won't run on anything on unix as far as the actual ETL process. And it doesn't natively support coordinating processes across multiple servers so it's native scalability it constrained. There's nothing preventing you from kicking off multiple ETL processes on multiple servers but you would have to manage that through a job scheduler. Of course Informatica's job scheduler also has a bit to be desired. That seems to be a common issue with all ETL tools. The native job parameters that SSIS uses are built-in with SqlServer SqlAgent scheduler but that doesn't prevent you from passing them at the command line level.

it_user99681 - PeerSpot reviewer
Consultant
2014-04-02T17:47:10Z
Apr 2, 2014

SSIS works very well with Oracle and DB2. It can connect to various desperate systems hosted on unix. Most cases it's as good as Informatica.

Find out what your peers are saying about Informatica PowerCenter vs. SSIS and other solutions. Updated: March 2024.
765,234 professionals have used our research since 2012.
it_user93816 - PeerSpot reviewer
Real User
2014-04-02T14:02:17Z
Apr 2, 2014

"Performance- For a SQL Server based environment of medium size both perform equally well"
Ah, but if you want to interact with anything other than SQL Server - say, DB2 or Oracle - or anything bigger than medium size or, say, you are running UNIX servers, SSIS is out of luck.

Want to save money? Go with Talend before SSIS.

it_user99681 - PeerSpot reviewer
Consultant
2014-04-02T13:17:35Z
Apr 2, 2014

Some thoughts about SSIS vs. Informatica

Informatica vs SSIS

Security - Both tools equally secure.
Administration & Maintenance - Easier in SSIS.
Ease of Use - Easier to use SSIS
Performance- For a SQL Server based environment of medium size both perform
equally well
Productivity - Higher in Informatica for large scale implementations due to
re-usability.
Product Maturity - Higher in Informatica as its present for a very long
time
Compliance requirements if any - Both Compliant
Cost - Zero for SSIS while its very high for Informatica.

SSIS Advantages
Fuzzy lookup and Fuzzy Grouping
Script Component on Dataflow
Beautiful GUI, easy and fast to develop
Easy Debugging due to its powerful integration with visual studio.
Variable as Object
Script Task - this helps to write custom scripts
File System Task
Disconnected Architecture - Each developer can work on his local without
connecting to ETL server.
Configurations through environment variables.
Easy and Flexible package configurations (Informatica uses parameter
files, which is very very manual and error prone)
Display errors in design mode than after developing it . In Informatica you
don't know the errors until you develop a mapping.
For each loop container. In Informatica we have to create multiple steps to
achieve this. Really poor in this area.

Informatica Advantages
Checkpoint works on Dataflow level. In SSIS, it's on the component/task
level.
Shared Sources and Targets
Import/Export Salesforce data. Good in cloud connectivity.
Import from PeopleSoft
Dynamic Lookup (Something like MERGE in SQL Server 2008 and UPSERT in
Oracle)
Lot of connectivity options to cloud source and standalone sources like
CRMOD, Hadoop, salesforce etc.
Prebuilt sequence generator. Useful of dimension tables loading
Unconnected Lookup - something like Calling a scalar function (returns
single cell output) in SQL Server. Good feature.
Mapplets - Set of mapping at once place
Worklets - Set of sessions at one place
Incremental Aggregation.
Commit to X rows in mapping (Data flow in SSIS) for any database.

Regards
AP

it_user93816 - PeerSpot reviewer
Real User
2014-04-02T13:05:28Z
Apr 2, 2014

PowerCenter - no contest. But it comes at a price.

Informatica PowerCenter vs. SSIS comparison
We performed a comparison between Informatica Powercenter and SSIS based on our users’ reviews in four categories. After reading all of the collected data, you can find our conclusion below. Ease of Deployment: Informatica PowerCenter users say the setup is a simple process but requires some level of knowledge because some manual work is required. For skilled users, the setup is easy, and some did it themselves. SSIS users say the initial setup is easy. Features: Valuable features of...
Download Informatica PowerCenter vs. SSIS comparison ReportRead more

Related Q&As

Data Integration experts

Prateek Agarwal - PeerSpot reviewer
Syed Fahad Anwar - PeerSpot reviewer
Hamid M. Hamid - PeerSpot reviewer
Joaquin Marques - PeerSpot reviewer
Miodrag Milojevic - PeerSpot reviewer
Ravi Kuppusamy - PeerSpot reviewer
Kevin McAllister - PeerSpot reviewer
JA