May 21, 2015 at 8:16 am
I'm trying to create an SSIS package that will do a straight data copy between databases. The problem is that the underlying schema of the origin may change and the requirement is that the transfer be table driven. i.e. the tables that are copied are listed in a table and there should be no human intervention when the schema changes.
I'm moving data between SQL Server and SQL Azure, so backup and restore doesn't work. Has to be an SSIS package.
What's the best way to deal with a changing schema in an SSIS package? Can I delete and rewrite the underlying XML for any tables that change? Do I need to do it programmatically with C#? Do I need to create the package from scratch each time?
If you could point me at some possibilities, I'd appreciate it. I'm having trouble googling this because I'm not sure what to call it.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
May 21, 2015 at 9:39 am
Quick thought, normally use sys.dm_exec_describe_first_result_set and build the dataflow based on the output.
😎
May 27, 2015 at 12:08 am
SSIS is metadata-driven and as you have found an out of the box setup needs to be modified each time the set of source or destination columns that need to be sent through a Data Flow are changed.
If you're open to a third-party tool and some experimenting this tries to address your issue:
http://www.cozyroc.com/ssis/data-flow-task
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply