As anyone who has been using SQL Server Integration Services (SSIS) for a while will know there are lots and lots of knobs within a package that can be tweaked to alter behaviour. Many of these aren't obvious and the steep learning curve that many people attribute to SSIS education is, in my opinion, down to knowing about these little nuances that can affect your package development. I'm thinking of things like:
- The need to set DelayValidation=TRUE on any data-flow that contains a Raw File Source Adapter.
- Sometimes when developing you need to set DisableEventHandlers=TRUE in order to unit test some functionality. It is easy to forget to set it back to FALSE.
- When accessing some relational data sources (particularly Oracle) you need to set AlwaysUseDefaultCodePage=TRUE in order to suppress a warning.
- Setting BypassPrepare=TRUE on the Execute SQL Task is sometimes a pre-requisite to your SQL statement being validated
- In order for a package that uses checkpoints to restart from the previously failing task (rather than the subsequent task), the task in question needs to have FailPackageOnFailure=TRUE.
- Packages that have ProtectionLevel='DontSaveSensitive' need to use configurations in order for passwords to be used at execution-time
- Columns in the data-flow that are not used will cause warnings at execution-time. Those warnings are useful, but annoying.
- You need to manually set IsSorted=TRUE if you know that data in a data-path is sorted and there is no SORT component in the data-path.
- Setting IsSorted=TRUE will not sort the data for you
- [Can you think of any more? There must be loads]
These aren't things that are generally documented, they are just things that you pick up through using the product.
I'm interested in ways that these problems can be solved. I have a few ideas myself but first of all I'd like to know the opinion of the community. Is it worth trying to solve these problems or should we just accept that this is just the way things are with SSIS and we have to get on with it? If we should try and solve it, how should we go about it? Should the SSIS team be building automatic checks into the Designer?
Let me know in the comments section below.
-Jamie