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 am looking for best practices to separate data from a single database into multiple client specific databases. Currently my application dumps data for all the customers into single database and I need to separate data for each client(for security reasons I can't use single database) into respective client databases.

Can we use SSIS effectively to perform above task? Is there any way this SSIS package will understand that new customer is added (New ClientID) and create a new database for it?

share|improve this question
3  
SSIS isn't magic; you'll have to build in that type of logic yourself. But I would probably work on changing the application if you're in control of the source code. –  Jon Seigel Jan 27 at 14:38

1 Answer 1

I think you could definitely use SSIS to do this task.

I would start by selecting your distinct ClientID's into an object variable and then using a ForEachLoop with a Foreach ADO Enumerator and then specify that variable and load each row one at a time into a string variable. You could then used an Execute SQL Task that would check to see if a database exists for that ClientID and, if not, create the database and subsequent tables.

Then you could dynamically build your destination connection based on that ClientID as the database name. You also would want to dynamically build your SQL for your extract from your source and then do a simple extract and load into your new or existing database.

share|improve this answer

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.