2014-04-02T12:28:00Z
Ariel Lindenfeld - PeerSpot reviewer
Director of Community at PeerSpot
  • 7
  • 221

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

9
PeerSpot user
9 Answers
it_user91929 - PeerSpot reviewer
Senior Architect at a construction company
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
GaryM - PeerSpot reviewer
Data Architect at World Vision
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
Customer Success Manager at PeerSpot
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
ETL Solution Architect at a tech services company
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.

GaryM - PeerSpot reviewer
Data Architect at World Vision
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
Senior SQL Server DBA at a tech services company with 51-200 employees
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: November 2022.
655,465 professionals have used our research since 2012.
it_user93816 - PeerSpot reviewer
Consultant at a energy/utilities company with 10,001+ employees
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
Senior SQL Server DBA at a tech services company with 51-200 employees
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
Consultant at a energy/utilities company with 10,001+ employees
Real User
2014-04-02T13:05:28Z
Apr 2, 2014

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

Related Questions
Netanya Carmi - PeerSpot reviewer
Content Manager at PeerSpot (formerly IT Central Station)
Nov 14, 2022
Which services do you use it with and how efficient is it?
Netanya Carmi - PeerSpot reviewer
Content Manager at PeerSpot (formerly IT Central Station)
Nov 14, 2022
What do you use the tool for and what do you think it works best for?
Related Articles
Anna Odrynska - PeerSpot reviewer
Chief Strategy Officer at Alpha Serve
Oct 11, 2022
Business owners cannot rely on their own market assessments and strategic assumptions to make informed tactical decisions. Rapid technology development and tough competition mean that thorough data analysis is vital for success. In this article, we explain how business analysts, IT teams, and company managers can benefit from Microsoft Power BI integration with the help of BI connectors. Why i...
Ricardo Giampaoli - PeerSpot reviewer
EPM/BI certified Consultant, Oracle ACE and TeraCorp Consulting CEO at TeraCorp Consulting
Mar 22, 2021
Hey guys, how are you?  In this post, I’ll talk about my preferred tips and tricks that I use in ODI (any version). I’m always using these pieces of code for pretty much everything I do in ODI especially because it makes the code more elegant, dynamic, and easier to change later. First, let’s talk about my all-time preferred, Loops. Normally we learn to loop in ODI using a count variable and ...
Product Comparisons
Related Categories
Related Articles
Anna Odrynska - PeerSpot reviewer
Chief Strategy Officer at Alpha Serve
Oct 11, 2022
Power BI Integration: Why Should You Integrate Power BI and Your Software?
Business owners cannot rely on their own market assessments and strategic assumptions to make inf...
Ricardo Giampaoli - PeerSpot reviewer
EPM/BI certified Consultant, Oracle ACE and TeraCorp Consulting CEO at TeraCorp Consulting
Mar 22, 2021
How to use ODI to generate Dynamic code for you
Hey guys, how are you?  In this post, I’ll talk about my preferred tips and tricks that I use in...
Download Free Report
Download our FREE report comparing Informatica PowerCenter and SSIS based on reviews, features, and more! Updated: November 2022.
DOWNLOAD NOW
655,465 professionals have used our research since 2012.