Coming October 25: PeerSpot Awards will be announced! Learn more
Ariel Lindenfeld - PeerSpot reviewer
Director of Content at PeerSpot (formerly IT Central Station)
  • 7
  • 227

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

PeerSpot user
9 Answers
PeerSpot user
Senior Architect at a construction company
10 April 14

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.

GaryM - PeerSpot reviewer
Data Architect at World Vision
Real User
Top 5Popular
01 November 16

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.

PeerSpot user
Customer Success Manager at PeerSpot
01 November 16

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

PeerSpot user
ETL Solution Architect at a tech services company
Real User
09 February 15

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 5Popular
02 April 14

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
02 April 14

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: August 2022.
633,572 professionals have used our research since 2012.
PeerSpot user
Consultant at a energy/utilities company with 10,001+ employees
Real User
02 April 14

"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
02 April 14

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
Product Maturity - Higher in Informatica as its present for a very long
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
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
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.


PeerSpot user
Consultant at a energy/utilities company with 10,001+ employees
Real User
02 April 14

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

Related Questions
Netanya Carmi - PeerSpot reviewer
Content Manager at PeerSpot (formerly IT Central Station)
Feb 13, 2022
2 out of 3 answers
Advanced Senior Programmer at a individual & family service with 10,001+ employees
25 October 21
Cloud Data Integration
21 November 21
Complex transformations can easily be achieved using PowerCenter, which has all the features and tools to establish a real data governance strategy. Additionally, PowerCenter is able to manage huge quantities of data with a high level of reliability. We also like the metadata repository and the data warehouse application console. We would like to see the licensing improved, though. We would like to see a feature-wise option as opposed to the current bundle option. PowerCenter is a very expensive solution. The numerous levels of transformation and multiple interfaces can make it a very confusing solution to learn. Cloud Data Integration offers great options for data cleansing, data mastering, data masking, master data management, and API management. The UI is very easy to use and the setup is straightforward. The new licensing is very flexible. Cloud Data Integration has inbuilt tools that allow for a lot of data transformation without changing any SQL queries from the code. This is one of our favorite features. We would point out, though, that there are some limitations with performance using Cloud Data Integration. We experienced some connectivity issues, making things run a bit slow. There are some issues with data residency laws in some countries. More regions should be added. Finally, it would behoove Cloud DataIntegration to consider adding more data sources. Conclusion: These are two great solutions under Informatica Brand which is synonymous with quality, reliability, and price. (Very high price.) Cloud Data is best suited for small to medium organizations and is somewhat limited in the regions it can be used. PowerCenter is most applicable for large enterprises that can more easily absorb the cost and best utilize everything PowerCenter has to offer with their current bundle licensing tier.
Related Categories
Download Free Report
Download our free SSIS Report and get advice and tips from experienced pros sharing their opinions. Updated: September 2022.
633,572 professionals have used our research since 2012.