I have used SQL Server integration Services (SSIS) since it was first released in SQL Server 2005. I have used a lot of ETL tools over the years, and in my opinion, SSIS is equal to the best of them. It is amazing some of the features included such as fuzzy logic. And the number of data connections it is able to consume is hard to match.
When I started learning Test Driven development methods I found it difficult to apply them to SSIS. Granted, test driven development tends to be applied more often to object oriented languages. I have found it works well with procedural languages as well, even TSQL. But SSIS is another beast and difficult to harness.
SSIS does support Dot Net assemblies which allows you to write code that may be tested externally, and then include it in your SSIS workflow. When I was reviewing a project written by another developer, embedded code in SSIS was difficult to read and follow, and impossible to test independent of the overall process.
I have found that SSIS works great for getting code up and working quickly. But the bigger it grows, and the longer you are away from the projects you have written, the more difficult it is to maintain. If I can write a routine effectively without re-creating all of the gold built into SSIS I have found that there are times when writing ETL processes in managed code, and calling them from the SSIS engine is a more manageable design. Performance can be equal or better depending on your expertise using parallel libraries or threading techniques.
What is your take on SSIS as an ETL tool? Does the fact that you can only view code reasonably in the GUI a hindrance or a help? Do you find it difficult to test routines? How do you know that your flow is working accurately? Share your thought here or by email to btaylor@sswug.org.
Cheers,
Ben