Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

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.

share|improve this answer
    
Thanks. Let me ask the question a different way. What's the simplest and easiest to maintain way to generate say a monthly export of a bunch of MSSQL databases to Access when the schemae will get changed by the developers on a periodic basis. We will want to filter out certain tables as they're not needed or don't work in Access. –  Brian Spolarich Jul 5 '11 at 16:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.