SSIS Components not updating data types properly

You may have come across this scenario – you’ve changed the length of a column in your database – say shrunk a varchar from 50 to 25, and some SSIS components downstream stubbornly refuse to change the length through the normal practice of opening the component, reviewing mappings and closing down again. You look at the metadata on the data flow going in, and its length 25. You look at the metadata going out and its length 50, giving you errors such as truncation warnings downstream.

The Union All component is a particularly consistent offender in this case, and not unfairly – it has to allow for the fact that other input sizes may not have changed down as well. However it doesn’t have any advanced editor to change the output flow column size.

So how to fix? Fortunately this is where you can take advantage of the fact that SSIS packages are stored as XML. From the toolbar, choose “View -> Code” and BIDS will open up the XML. Start hunting for and replacing the offending value, save and close the code window and the problem will be fixed. I strongly recommend that you carefully consider each replacement – find and replace all will do horrendous damamage to your package as the XML contains lots of numeric values.

This can also apply to stubborn components – such as Aggregate – which won’t let you change the output column size in the Advanced Editor.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>