SSIS development notes

Mirror any settings you change in the package in your development package configuration. Otherwise you risk the config values blatting your changes.

Click OK to save transformation changes, don't just close the editor!

When tidying up the dtsConfig xml formatting, don't put newlines and tabs between the ConfiguredValue tags.

This error:

Error at Create x Table [DTS.Pipeline]: The index is not valid.ADDITIONAL INFORMATION:Exception from HRESULT: 0xC0048004 (Microsoft.SqlServer.DTSPipelineWrap)

just means that SSIS is playing up. Recreate the offending data source (I wonder if any of the service packs fix this?)

When copying and pasting data flow elements, unselect the copied elements before pasting.

You've added a dozen package variables in the wrong scope, and the scope can't be edited in the variable view. Aaargh! Do you have to delete and recreate them? No, you can open the code designer view of the package, find the xml for the variables and move them to the appropriate place. It's not really much easier I'm afraid.

When columns are added to the data source, the Union All transformation fails to recognise their existence. This transformation has to be recreated.

If you're developing on a virtual machine that has been created by restoring from a windows backup, the first tab of the Regional and Language Options control panel may revert back to US English. Changing all the settings on the front tab will get your usual desktop apps behaving properly, but SSIS may still be confused! If you have a SQL task that calls a stored procedure with a date parameter, you may find that the sp will receive a date in the wrong format. The way to fix this is to select the Default User Account Settings tick box on the Advanced tab of the Regional and Language Options control panel.

When executing solutions or packages within Visual Studio I get duplicate designer windows opening up. The icons for these dupes are the same as for miscellaneous files. The main nuisance is if after execution I decide to make further changes and I mistakenly change the dupes instead of the originals. What are these windows for?