Geek Answers Handbook

Update Destination Schema Metadata in Integration Services

I am working on a huge ETL project with over 150 tables, and during development I had to make significant changes to the destination column names and data types for several tables.

My problem is that I cannot get SSIS to see the new schema for the tables that I changed. So I would like to know how can I get SSIS to update this scheme? It seems to me ridiculous that there is no way to tell SSIS to update metadata from the database schema, especially for database migration.

It is not possible to play the project from scratch because I spent several hours on it. Also, manually changing, the 400+ columns that I changed are also not parameters.

+8
refresh metadata ssis
source share
3 answers

Following my previous autoresponse, I finally found out that it prevented me from updating metadata.

When I initially modified my database, I actually ran another script that did DROP on the table, and then CREATE TABLE to recreate the table from scratch. There SSIS could not detect the changes, and I had to do all this in my other answer.

Later today I had to make minor changes, and this time I chose ALTER TABLE . Magically, this time SSIS found that all the changes even notified me about updating the columns from the advanced editor, which worked fine.

So basically all of these problems were caused by my poor knowledge of DBA and its best practices.

+1
source share

How about using Advanced Editor and clicking the Refresh button on the left side below?

+16
source share

I found a way to fix this, but it was a bit complicated.

I even thought that I completely delete any links from the table from my packages, I always received the old metadata.

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

  • Removed a link to the corresponding source and destination tables.
  • Remote obj and bin folder from project folder
  • Saved, closed, and then reopened the project
  • Created a new data stream from scratch and updated metadata, finally there

I don’t know where this data was saved, but I suspect that a cached copy of your packages is stored in the obj folder or that Visual Studio stores metadata in memory that is freed when it is closed. In any case, after these steps you should fix it.

+1
source share

More articles:


All Articles