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

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, privacy policy and cookie policy

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