If you change an underlying query that your SQL Server Integration Services (SSIS) package is relying on, you get the following message:
The external metadata column collection is out of synchronization with the data source columns. The column "<column name>" needs to be updated in the external metadata column collection.
When this happens, the only way to fix it at the moment (SQL Server 2005 SP1) is to edit your OLE DB Source and click on the Columns item, then untick the columns causing the error. Click OK, then edit again and add the columns that you just removed back in. Click OK again to persist the changes. This will cause SSIS to change the underlying lineage ID for the column and you will then get a validation error.
To fix the validation error, edit the OLE DB Destination and the "Restore Invalid Column References Editor" screen will appear.
Set the Available Columns item to "<Delete invalid column reference>" for all the entries and click OK. You should then be able to continue and remap the columns between source and destination.
13 comments:
Thanks! This is helpful...
oh ssis, can you possibly get any more frustrating ?
Yes, yes it can :)
How about
- right-clicking the box with the warning sign,
- selecting "Show Advanced Editor",
- activating "Column Mappings"
- and then clicking "Refresh"?
I just had to completely build a package from scratch because I could not get SSIS to recognise that a destination column was no longer unique. With DTS this was not an issue. MS giveth with one hand and taketh with the other.
thank! was really helpful
Thanks heaps. Of all the online posts relating to this issue, this was the most helpful and concise.
Anonymous @ 9:16 posted the easiest solution.
Another option is to insert a new column that delegates what records you are inserting so you can remove the inserted records with ease. Once you achieve this, be sure to keep track of what rows you are adding to your import/export function. If your metadata isn't refreshing, it could be because you are doing it during a busy time of day and the table could be in use. I found that if I try changing my import/export SSIS package later in the evening, say 5pm or later, I get no problems and my metadata refreshes even if I change my SQL 2008 data column type from ntext to nvarchar. If i try changing this during the day, no meta refresh.
@Kenny, if that method works for you then yes it is the easiest. In my experience it doesn't always solve the problem.
I got this probelm and the only way I could solve to make sure the new column in the database is placed as the last column. If you put the column in between the other columns in your SQL table it does not work. Strange.
Very useful. Thank you!
Thanks alot, due to this post I resolved the issue. Impressive post.
@Divakar Ingilala
Post a Comment