Friday, October 19, 2007

SSIS External Metadata Refresh

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.

SSIS Restore Invalid Column References Editor Screen

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:

Swami Polnati said...

Thanks! This is helpful...

Anonymous said...

oh ssis, can you possibly get any more frustrating ?

Dean Heard said...

Yes, yes it can :)

Anonymous said...

How about
- right-clicking the box with the warning sign,
- selecting "Show Advanced Editor",
- activating "Column Mappings"
- and then clicking "Refresh"?

Anonymous said...

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.

HLIA said...

thank! was really helpful

Joseph said...

Thanks heaps. Of all the online posts relating to this issue, this was the most helpful and concise.

Kenny said...

Anonymous @ 9:16 posted the easiest solution.

Nicholas said...

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.

Dean Heard said...

@Kenny, if that method works for you then yes it is the easiest. In my experience it doesn't always solve the problem.

Anonymous said...

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.

thegreatone0381 said...

Very useful. Thank you!

Jonathan Divakar Ingilala said...

Thanks alot, due to this post I resolved the issue. Impressive post.

@Divakar Ingilala