This past week has held many issues with a recently released application under the purview of the DBA team. What has really separated this from a host of other jobs and procedures which they watch over is that this application was written in SSIS. Although all of the DBAs have had some experience with the tool, it is mostly cursory at best with only one or two who have had any extensive exposure.
In any case, the controlling package was written in such a way as to use the full power of the tool: configurations, expressions, dynamic queries, event handlers, etc. It checks to see that the source is available and that the data is from the correct processing period before moving on. If the data is not correct, or if the source is not available, it does not continue. Furthermore, it also checks to make sure that the process was running within the processing window – and more than that, that it is expected to finish within the window. All of which was a lot to interpret at 3 in the morning when an error was received and reported. The DBA on call was not responsible for having written the packages, nor had he had much experience with SSIS. Eventually, I was contacted and the problem was resolved. But, this brings up the question: How complicated should a package be?
It is my opinion that all of the power of a tool should be used when developing. However, I can understand that being able to maintain the package by a DBA who is not an expert with SSIS is necessary. And I think that most people would agree that the ability to triage any errors is essential. I suppose that it is necessary to find a happy median on the scale of complexity when identifying what is or is not easily understood when you are woken up in the middle of the night.