If I have the option of using .Net and can do data transformations just fine in .Net, when would I need SSIS? Is there a certain task that SSIS would be better for? Are the added benefits of transparency worth it? Is it just what I am more comfortable with? What are the best practices for determining this?
+11
A:
good question.
if the amount of data transfer huge? are you processing multiple data files and need transactions (both at file system level and database level)? are you dealing with multiple data sources at different locations (for eg ftp, local file system, database)?
if answers to above are yes then go ahead with ssis. basically .net is cool with small data import / export jobs, but when you have anything more complex, ssis is a definite winner
the other thing which i look at is - is it worth writing .net code when everything is available inside ssis. (dont mistake me - i love coding) however, anything you code, you need to maintain :-)
Raj
2009-03-27 15:10:51
+4
A:
I guess it depends on what you are doing. SSIS is very powerful, just like old DTS. If you are loading lots of items and expect to have constant change, I would go SSIS all the way. If you are looking to load only a few items and it’s for lots of customers, I would put it in code. I prefer SSIS for in house ETL processes, but I use .Net at client shops when I need to load data from a legacy system into a SQL database. Now as I stated before if you have a lot of transformations and lots of different data silos to load, I think you would be crazy to do this in .Net and I would go SSIS. If you have only a few items to load and it’s for a single application and may be installed as part of an application at various clients, I would go .Net all the way. Just my 2 cents.
Jojo
2009-03-27 15:13:56
+1
A:
SSIS has many built in ways of doing transformations from different data sources and you can string them together in a way that makes it very customizeable. They have built in optimizations that make them fast.
You can also use .NET to make your own custom transformations to take advantage of the speed and repeatability of an SSIS job.
Jeff Martin
2009-03-27 16:34:33
+3
A:
I think project time/budget constraints and the use of a standard tool are some of the biggest arguments for using SSIS. Creating an SSIS package is most of the times way faster than trying to code something similar in .NET.
But with that said, it seems like SSIS have a lot of pain points that sometimes might invalidate this argument. It did for me when developing a solution that needed to run in different environments at many different clients. SSIS simply looked too painful the more I evaluated it for the project. A properly architected .NET solution is easier to deploy, more reliable, more flexible, easier to understand and can also achieve very good performance.
IMHO: consider using SSIS for projects that you only need to deploy to one or maybe two in house SQL Server environments. Otherwise, the .NET approach will quickly become more appealing.
Lars Fastrup
2009-04-18 12:47:20
+1 for pain points, I don't think I'll ever use SSIS for a project again. It's just not a maintainable solution for when requirements change.
Even Mien
2009-12-07 18:47:35
A:
I think main advantage is defining the entire programming construct visually. Any one look at the SSIS package is it pretty much self explainer. The tight integration with the SSIS with SQL allows you to be part of SQL for back up scheduling and huge plus.
As every one explained if you are doing the lot of data manipulation it is good tool. It is free if you have SQL you all set to go and very easy to learn with VS 2008 BIDS