Refresh destination schema metadata in Integration

Question

I have been working on a huge ETL project with 150+ tables and during the design I had to make a major change on destination column names and data types for a couple of tables.

My problem is that I can't get SSIS to see the new schema for the tables I changed. So I would like to know how can I get SSIS to refresh this schema? I find it kind of ridiculous that there no way to tell SSIS to update the metadata from database schema, especially for database migration.

Recreating the project from scratch is out of question because I already spent some hours on it. Also changing manually the 400+ columns I changed is also not an option.

Answer 1

What about using the Advanced Editor and pressing the Refresh button on the left side below?

Answer 2

I found a way to fix it but that was a bit tricky.

Even thought I was completely removing any references of the table from my packages, I was always getting the old metadata.

I still don't have a clear fix but here is what I did to fix it:

• Removed any reference to concerned source and destination tables
• Deleted obj and bin folder from the project folder
• Saved, closed and then reopened the project
• Created new data flow from scratch and updated metadata was finally there

Don't know where those informations were cached but I suspect that the obj folder keeps a cached copy of your packages or that Visual Studio keeps metadata in memory which is freed when you close it. Anyway, following these steps should fix it.

Answer 3

Following my previous auto-answer, I finally found what was preventing the metadata from being refreshed.

When I originally modified my database, I actually executed another script that was making a DROP on the table and then a CREATE TABLE to recreate the table from scratch. There, SSIS was never able to detect changes and I had to do all the things in my other answer.

Later today I had to make some minor modification and this time I opted for an ALTER TABLE. Magically, this time SSIS detected all the changes even notifying me to refresh columns from the advanced editor, which worked fine.

So basically all these issues has been caused by my poor knowledge about DBA and its best practices.