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.
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.
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.
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.
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.
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.
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...
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.
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.
How has automated parameter-file handling performed more recently? What has or has not changed?
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.
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.
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.
"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.
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
PowerCenter - no contest. But it comes at a price.