Bruce Hendry (3/29/2013)
So in SSIS 2012 (VS2010 using SSDT) I am noticing an issue with metadata refresh that no longer responds to the tricks described in this post, and worse still, the metadata retains uncommon columns, as well. I am curious if anyone else is seeing this, and more curious if there is a workaround. It renders leveraging the copy & pasting of source and destination components fairly useless.Example:
Package A loads Customer data with OLE DB Source and OLE DB Destination. Package is copied and pasted, guid's reset, renamed to Package B.
OLE DB Source is updated to point to Account table. Refresh metadata via pointing to a table/statement with no common columns still seems to work for source, columns are reset and ordered as expected.
Destination updated but still retains ALL columns from Customer table, regardless of pointing to new destination with no common columns and then pointing back. I have tried a bunch of different fake outs; only thing that works is dropping the OLE DB Destination and adding a new one.
Anyone else noticed this behavior?
This change is by design. They redesigned SSIS in SQL Server 2012 and the way it handles metadata in the dataflow. It now "remembers" metadata, so you can build your dataflow from destination to source, instead of the usual way around. So that's the problem you are seeing: it remembers columns it shouldn't remember because the source has changed. Make sure your dataflow is fully connnected from source to destination, double click on an arrow and map the columns. There should be a checkbox about dropping unmatched columns or something like that.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP