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?

  • 4
    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, 2014 at 14:38
  • Are these different databases on the same server? or are they on different servers?
    – Nick.Mc
    Aug 25, 2014 at 11:37

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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