How to overcome vs_needsnewmetadata error in Data Flow task?

Question

I have an SSIS package that copies the data in a table from one SQL Server 2005 to another SQL Server 2005. I do this with a "Data Flow" task. In the package config file I expose the destination table name.

Problem is when I change the destination table name in the config file (via notepad) I get the following error "vs_needsnewmetadata". I think I understand the problem... the destination table column mapping is fixed when I first set up the package.

Question: what's the easiest way to do the above with an ssis package?

I've read online about setting up the metadata programmatically and all but I'd like to avoid this. Also I wrote a C# console app that does everything just fine... all tables etc are specified in the app.config ... but apparently this solution isn't good enough.

Solution 1

Have you set DelayValidation to False on the Data Source Destination properties? If not, try that.

Edit: Of course that should be DelayValidation to True, so it just goes ahead and tries rather than checking. Also, instead of altering your package in Notepad, why not put the table name in a variable, put the variable into an Expression on the destination, then expose the variable in a .DtsConfig configuration file? Then you can change that without danger.

Solution 2

Matching source destination column with case sensitive has done the work for me.

Like in my case SrNo_prod was column in dev and using it we developed the dtsx, while it is been created as SrNo_Prod in prod, after making case change from P to p, we got successful execution of package.

Solution 3

Check if the new destination table has the same columns as the old one.

I believe the error occurs if the columns are different, and the destination can no longer map its input columns to the table columns. If two tables have the same schema, this error should not occur.