SSIS Package Puzzle

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.

Answer 1

If all you are doing is copying data from one SQL2005 server to another I would just create a Linked Server and use a stored proc to copy the data. An SSIS package is overkill.

How to Create linked server

Once the linked server is created you would just program something like...

INSERT INTO server1.dbo.database1.table1(id,name)
SELECT id, name FROM server2.dbo.database1.table1

As far the SSIS package I have always had to reopen and rebuild the package so that the meta data gets updated when modifying the tables column properties.

Answer 2

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.

Answer 3

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.