Should programmers use SSIS, and if so, why?

Question

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.

Solution 1

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

Solution 2

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.

Solution 3

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

Solution 4

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.

Solution 5

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.

View more solutions