Update Schema in SSIS Export Package When Source Schema Changes

Question

I have servers with lots of client databases, all with very similar, but not necessarily identical schemae.

Some customers want their data in MSAccess on a monthly basis. This was getting tedious to do on a manual basis, so I figured I would create SSIS packages for these exports, add them to a job, and be done with it.

I was hoping I could just create one DTSX file in the filesystem and change the database name, but I ran into schema differences between instances already so that won't work.

One issue that occurred to me is that when our developers change the source database schema, I'm guessing the SSIS packages won't know about the new tables.

Is there an easy way to deal with this?

Answer 1

Easy? Not really - SSIS is very picky about the metadata that is used in the data flow, so changing schema is challenging to deal with.

My typical solution for these types of scenarios is to generate the packages through either the API, or using Biml (BI Markup Language). {Disclaimer: My employer (Varigence) created Biml.}

The API is a fair amount of work, and assumes you are comfortable with .NET development. Biml is an alternative that doesn't require as much .NET knowledge. You can use Biml through BIDS Helper, which is a free download.