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.

1.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.

 

2.

OK.... FINALLY got it all working. I just wanted to update with what fixed it in case anyone happened across this thread when searching the particular error I was getting.

 

First, the computer with Visual Studio where you compile the packages and build the manifest has to be on the same version of SSIS as the server you are deploying them to. I updated with SP2 and a hotfix to remedy that problem.

 

Second you have to set the protection level of the package to "dontsavesensitive". Neither of these fixes worked alone. I had at first changed just the protection level and that didn't work, so I changed it back, then I updated my SSIS version and the package still was giving the same error. Then after updating my version, I tried the protection level setting again and then it worked.

 

Hope it works for you if you have the same issue!