What is the current trend for SQL Server Integration Services?

Question

Could anybody tell me what the current trend for SQL Server Integration Services is? Is it better than other ETL tools available in market like Informatica, Cognos, etc?

Answer 1

disclaimer - i work for microsoft

now the answer

SSIS or SQL Server Integration services is a great tool for ETL operations, there is a lot of uptake in the market place. there is no additional cost other than licensing SQL server and you can also use .Net languages to write tasks. http://www.microsoft.com/sqlserver/2008/en/us/integration.aspx http://msdn.microsoft.com/en-us/library/ms141026.aspx

Answer 2

SSIS isn't great for production applications from my experience for the following reasons:

1. To call an SSIS package remotely, you have to call a stored procedure, that calls a job, that calls the SSIS
2. Using the above method, you can't pass in parameters.
3. Passing parameters means you have to call the SSIS on a local server - meaning code running on a remote server will have to call code running on the SQL server to execute the package.

I would always rather write specific code to handle ETL and use SSIS for one off transforms.

Answer 3

I was introduced to SSIS a couple of weeks ago. Executive summary: I am unlikey to consider it for future projects.

1. I'm pretty sure flow charts (i.e. non-structured) were discredited as an effective programming paradigm a long time, except in a tiny minority of cases.

2. There's no point replacing a clean textual (source code) interface with a colourful connect-the-dots one if the user still needs to think like a programmer to know where to drag the arrows.

3. A program design that you can't access (e.g. fulltext search, navigate using alternative methods, effectively version control, ...) except by one prescribed method is a massive productivity killer. And a wonderful source of RSI.

It's possible there is a particular niche where it's just right, but I imagine most ETL tasks would outgrow it pretty quickly.

Answer 4

In my opinion it's quite good platform, and I see a good progress on it. Many of the drwabacks that 2005 version had and that the community complained about, have been corrected on 2008.

From my point of view, the best thing is that you can extend and complement it with SQL or .NET code in an organized way as much as you want.

For instance, you can decide if in your solution you want 80% of c# code and 20% of ETL componenets or 5% of c# code and 95% of ETL components.

Answer 5

A great alternative: Alteryx.

I used it on a project which required a lot of data processing and geocoding. It has a similar drag & drop interface to setting up pipelines, without all the confusion of SSIS. It requires a lot less configuration and it doesn't break nearly as easily.

Disclaimer: Just my opinion - I am not affiliated with the company in any way.