SSIS Package Design - Best Practices for Saving Developer's Time and Work

Best Practices is a very broad term in itself. It can be in the context of performance (mostly), standards and conventions like naming conventions for example etc. Here in this post, I want to put a couple of best practices that I have found useful from a developer's perspective.

While working in development projects or especially on data migration kind of projects, the requirements are very volatile and can change a few times in a single day. There can be flying metadata, i.e. starting from column names to end mapping destination, anything could change. In such an environment, some of the practices which I am listing below have come very useful to me in my personal development experience.

1) Always use annotations, else there is a very good chance that you would forget why you placed a particular control.

2) Always use meaningful names, if possible the name of the column for which transformation is being used. If you are using a Derived column, where you are defining set of columns, add an annotation besides it specifying names of columns.

3) Always respect your source columns, and bear in mind that the joining keys i.e. the primary keys or the lookup keys should of the same/comparable data-type else you might run into serious mess at a later stage. This analysis should be done and reported to your DBA or design architect if there's scope of correction. Else the first step after taking out data from the source should be to make data-types equal.

4) Always put a multicast transformation after the source adapter, and after every stage where data takes a different form or an important column has been derived. You would never know when this source would be required at a different stream or transformation.

5) Before you start your development, find out common columns used across your flow provided your flow has more than one outputs / destinations or you are working in a multi data-flow kind of environment. This would help you centralize the change and require minimum effort for maintenance or change management.

6) Try to keep ValidateExternalMetadata property to False as far as possible during your development, especially where lots of columns are coming from sources (in hundreds) and dozens of transformations are being used to stop your BIDS from hanging. This would be helpful as even if you change a simple column at any level, the entire flow would change and it would go back to your source to validate the metadata, normally in case of a database if the property is set to True.

7) In case if you have developed your package to a great extent, and then you encounter an Einstein with an update that the column name has changed, use the new column and set the Output Alias property as the name of older column name. This would save you from correcting the issues in other transformations due to a new column induced from top to bottom of the flow.

8) Take backup of your package every 2 hrs. If you mess up seriously with your package, there is no Ctrl-Z that really helps.