95

As a .NET developer, for what reasons should I prefer SSIS packages over writing code? We have a ton of packages in production where I currently work, and they're a nightmare to both "write" (perhaps draw?) and maintain. Each package looks like a bowl of multicolored spaghetti with C# and VB.NET scripts mixed in at the points where the abstractions break down. To figure out what each "Execute SQL Task" or "Foreach Loop" does, I have to double click the damned thing and browse through a tree of literal values and expressions, scattered across multiple tabs.

I'm open minded, so I'd like to know if any other good developers find SSIS more productive than just writing some code. If you do find SSIS more productive, please tell me why.

Charles
  • 6,199
  • 6
  • 50
  • 66
  • 4
    don't know how it does it, but SSIS is a lot faster than any manual code i've written for creating a data warehouse. it's a tool designed for the job - try to break down the tasks into child packages that execute from a master package – Mr Shoubs Oct 26 '10 at 15:39
  • 1
    Link to a similar question: http://stackoverflow.com/q/690123/327165 – Ilya Berdichevsky Jan 12 '11 at 15:41
  • 5
    Just came across this. I'm working to maintain some problematic SSIS packages and wrote a decompiler to extract the useful work from them into a C# program. http://code.google.com/p/csharp-dessist/ – Ted Spence Aug 31 '12 at 00:37
  • 5
    From my experience, SSIS can be painful if you have "long" and/or "complex" sripts or many scripts. Debugging a console app is way easier. In SSIS, you can't debug your script on its own. The error messages produced due to a script are cryptic and you cannot see the exact line that caused the error. IMO, if the project needs can be met with standard SSIS components, then SSIS is might be the way to go. But, for that you need to know limitations of SSIS components. Eg.This video shows you why "send mail task" is almost useless - http://www.youtube.com/watch?v=IlUzkMPYDSk – Steam Feb 06 '14 at 23:51
  • 3
    this question has 7 answers, so it didn't solicit debate, arguments, polling, or extended discussion. Why not keep it open? – Michael Freidgeim Jan 03 '17 at 12:11

7 Answers7

96

I use SSIS every day to maintain and manage a large data warehouse and cube. I have been 100% business intelligence and data warehousing for two years. Before that I was a .NET application developer for 10.

The value of SSIS is as a workflow engine to move data from one spot to another with maybe some limited transformation and conditional branching along the way. If your packages contain a lot of script then your team is using SSIS for the wrong tasks or isn't comfortable with SQL or has bought into the hype. SSIS packages are very difficult to debug. Script components are an absolute nightmare and should be used only for formatting, looping, or as a last resort.

  1. Keep your packages simple, sql tasks and data flow tasks.
  2. Do as much work as possible outside of SSIS, preferably in SQL
  3. Keep your variables in a single global scope
  4. Keep your SQL in variables or store procedures, never in-line
  5. Keep your variable values in a configuration store, preferably a SQL database
Kevin D. White
  • 1,213
  • 11
  • 11
  • 1
    With the trouble I've had with SSIS, I would have given a more biased answer (as if you couldn't tell from the tonality of my question `:)`). Nice answer, Kevin. – Charles Sep 19 '10 at 23:07
  • 6
    How did you work with .NET for 10 years if it was released in 2002? – Brady Holt Feb 07 '12 at 17:54
  • 7
    [quote]Microsoft started the development on the .NET Framework in the late 1990s originally under the name of Next Generation Windows Services (NGWS). By late 2000 the first beta versions of .NET 1.0 were released[/quote] That is how, he was probably working with the beta. – nitefrog Jul 18 '12 at 21:46
  • The question was answered in 2010, so take off the two years BI, and then the further 10, gives 1998, two years before the beta release you mention. Otherwise, good answer! : ) – finoutlook Nov 20 '12 at 12:40
  • Yes, the global scope makes sense. If you make it local and want to access it elsewhere, then you have a problem. You cannot simply change the scope of the local to global. You have to a lot of clicks and deletes to instead. If you have even 10-15 locals, this becomes a pain. – Steam Feb 18 '14 at 19:59
53

I tried using SSIS several times, and gave up on it. IMO it is much easier to just do all I need in C#. SSIS is too complex, it has too many gotchas, and it is just not worth it. It is much better to spend more time on improving C# skills than to spend same time on learning SSIS - you'll get much more return on your training.

Also finding and maintaining functionality in a VS solution is so very much easier. Unit testing with VS is easy. All I need to do is to check in the source in Subversion, and verify how it loaded. Unit testing SSIS packages is very involved to put it mildly.

Besides, there were situations when SSIS was silently failing to populate some columns in some rows, just skipping them without raising exceptions. We spent a lot of time troubleshooting and figuring out what is going on. Developing an alternative solution in C# took less than an hour, and works without any problems for two years.

A-K
  • 16,804
  • 8
  • 54
  • 74
  • Thanks for your points Alex. Here is an example of what I think might be a gotcha - https://stackoverflow.com/questions/21616435/assigning-a-sql-null-to-a-ssis-string-a-ssis-flaw. – Steam Feb 18 '14 at 19:51
  • 2
    Is there a list of all the C#/programming topics an ETL developer MUST know ? Eg. LINQ, SqlDataReader, DataTable etc. I too feel that SSIS is not good for complex tasks. If you have a easy "copy-paste" project/task, then SSIS might be the best tool. – Steam Feb 18 '14 at 19:54
  • @blasto have you tried out Rhino ETL: http://ayende.com/blog/3102/rhino-etl-2-0 – A-K Feb 18 '14 at 20:07
  • Alex, Jerome's answer also suggested Rhino ETL. It seems obscure to me. So, I would be hesitant to use it for lack of documentation, support and tutorials. Besides, it looks like only one dev is working on it. That decreases my confidence in the tool. I would try this for fun or out of curiosity, but I cannot use this for a real project. Thanks. – Steam Feb 18 '14 at 20:17
  • If someone wants a tutorial on Rhino ETL (with pure C#) here is one - http://www.codeproject.com/Articles/34556/Write-ETL-jobs-in-pure-C – Steam Feb 18 '14 at 20:17
  • SSIS Package building doesn't seem the right venue for a developer. I'd think it's more for DBAs or competent BAs who are looking to do ETL work. It is possible for a programmer to learn SSIS, but fallacy to think of it like programming. It's a nuanced GUI to ultimately build a script. – baker.nole Jan 29 '16 at 19:20
  • Everyone has their experiences but the problem with SSIS is not that it's too hard to learn. It's the opposite. It has too low of a barrier of entry. It is abused often. And when too much logic gets embedded in there, it just makes life that much more interesting. – sam yi Apr 30 '16 at 02:16
14

In my opinion - SSIS is for ETL operations only and should contain no logic outside that scope.

Christoph
  • 4,251
  • 3
  • 24
  • 38
  • 8
    ETL = Extract Transform Load – Christoph Aug 24 '10 at 15:41
  • 3
    That's pretty much how I feel. In our case, we're using SSIS to do stuff like email (or SFTP) CSVs containing pricing info. The branching, embedded scripts, etc are pretty horrible. If just moved some data around with SSIS, it probably wouldn't be so bad. – Charles Aug 24 '10 at 15:50
  • 1
    I think your answer could have some more depth. – Steam Feb 18 '14 at 20:10
  • 3
    Can the T in ETL not involve some logic? Just a thought ... – cs0815 Sep 13 '14 at 08:06
  • If it's only related to shaping/routing the data, sure. But I would avoid any business logic. – Christoph Sep 18 '14 at 22:36
  • ETL processes sometimes contain business logic, so that is what I would suggest as the only exception. – baker.nole Jan 29 '16 at 19:14
11

I had the unfortunate experience of working on a project where we thought SSIS would be a good enough solution to aggregate and combine data from several sources. The unfortunate thing was that it worked great at first but then requirements changed and we (eventually) realized that it was the wrong tool.

maybe we were just using it incorrectly but we had a lot of difficulty if we ever changed our schema and we eventually just reused our ORM definitions from the front end to write a custom tool in C# to do this. Because we already had the datamodel this was surprisingly easy. obviously YMMV and i am by no means an SSIS expert, but in this one case SSIS caused a lot of duplicate work and headaches when just rolling up our sleeves and 'handcoding' it was easier than expected.

So i would think about flexibility a lot when considering SSIS.

luke
  • 14,518
  • 4
  • 46
  • 57
  • 7
    I share some of the same feelings. It's easy to refactor code... not so much with a visual DSL. – Charles Aug 24 '10 at 15:56
  • Luke, can you please give us an outline of your project requirements ? Thanks. – Steam Feb 18 '14 at 20:04
  • @blasto we were trying to integrate data from several databases and use some of the built in probabilistic string matching utilities to merge data from the different systems (essentially CRM databases). It was 5+ years ago so i don't remember all the details. – luke Feb 19 '14 at 02:00
  • If you are a .net shop and are involved in moving data for data warehousing purposes, SSIS will only help you if you know it well enough. I have seen many people who are .net gurus but fail to completely understand SSIS (and I dont blame them). SSIS sure requires a person who knows it well enough otherwise you will end up writing packages that are inefficient and cant do the right thing. – rvphx Mar 11 '19 at 16:01
6

SSIS has its place, and that place is not general programming or as a replacement for stored procedures. It comes from the ETL school (Extract, Transform, and Load) and that's where its stregnth is.

The old name (DTS, Data Transformation Services) and the new name (SSIS, Sql Server Integration Services) both make clear it's a service (or set of services) designed to manipulate data to integrate the SQL Server database into larger processes.

DaveE
  • 3,579
  • 28
  • 31
  • I don't see how this answer should get so many upvotes. It does not mention why SSIS cannot give you the power of a programming language. It makes no sense to me. One example of where SSIS fails to match a programming lang is debugging. Apparently, SSIS 2012 changes that. So, may be, just may be, the tool is on its way to becoming more programmer friendly. – Steam Feb 18 '14 at 20:07
  • >> One example of where SSIS fails to match a programming lang ... I agree-it's not a programming language. It is a decent ETL tool. – DaveE Feb 19 '14 at 20:36
4

If you want to move your data programmatically, you might want to look at Rhino ETL.

I'm also working on my own framework, Fluent ETL, as I find SSIS a bit too involved for simple data tasks related to development, like loading unit test data from a CSV file.

Jerome
  • 1,162
  • 1
  • 6
  • 12
  • Rhino ETL is obscure and has only 24 questions on SO as of now - http://stackoverflow.com/questions/tagged/rhino-etl. I think that C# would be good enough for ETL, if you have the knowledge and the experience. – Steam Feb 18 '14 at 20:01
  • 1
    Are there any popular alternatives to Rhino ETL ? – Steam Feb 18 '14 at 20:02
3

SSIS is not a program. A lot of things are faster to make in SSIS, and you get very nice detailed progress and error information as admin - which can be very good in the scenarios SSIS is meant to solve, because sometimes things go wrong and the admin needs a lot of information.

That being said, SSIS is not really that useful if you don't have the stuff self explanatory - they are meant for something.

Talha
  • 524
  • 1
  • 6
  • 22
TomTom
  • 61,059
  • 10
  • 88
  • 148
  • 2
    Can you give us an example of how SSIS can hasten development in one scenario and slow in the others ? – Steam Feb 18 '14 at 20:12