CODEWITHSUNDEEP
sql-serverssis
sivaramakrishna
sivaramakrishna

Reputation: 676

What is the current trend for SQL Server Integration Services?

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?

Upvotes: 2

Views: 959

Answers (5)

Akabelle
Akabelle

Reputation: 398

I would list as benefits:

  • you use SSIS for bigger projects, probably/preferably once or in one run, and then use the integration project for many months with minor changes; the tasks, packages and everything in general is easily readable (of course, depends on perspective)
  • the tool itself handles the scheduled runs, sending you mails with the logs, and - as long as my experience reaches - it communicates very well with all the other tools (such as SSAS, SQL Server Management Studio, Microsoft Office Excel, Access etc., and other, non-Microsoft tools)
  • the manually, in-detail configured tasks seem to take over the responsibility in all ways, letting only small chance for errors
  • as also mentioned above, there are many former problems corrected in the new versions

I would recommend it for ETL, especially if you would continue with analytical processes, since the SSIS, SSAS and SSRS tools blend together quite smoothly.

Drawback: debugging/looking for errors is a bit harder until you get used to it.

Upvotes: 1

river0
river0

Reputation: 494

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.

Upvotes: 2

Edmund
Edmund

Reputation: 10819

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.

Upvotes: 5

cjk
cjk

Reputation: 46465

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.

Upvotes: 2

ashish jaiman
ashish jaiman

Reputation: 389

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

Upvotes: 1

Related Questions