Configuring Connection Managers For different DBs in different jobs

  • dndaughtery

    SSChampion

    Points: 11276

    My team has just learned that using one db for all clients will not work and all clients need their own database. We have several packages deployed in one project and the project is configured using a reference to one enviroment. Id like to create copies of the DB for each client and use the same packages but create new jobs for calling the packages for different clients and configure the database for the connection mangers in the SQL Agent job. How could I go about doing this?

  • Phil Parkin

    SSC Guru

    Points: 244732

    There is more than one way, but you could do the following:

    1. Create an SSISDB environment for each client and put all of the client-specific variables in them, keeping the variable names consistent across environments..
    2. Add references from your SSISDB project to all of the environments.
    3. When creating the multiple SQL Agent jobs, be sure to configure them to point to the relevant environment.

     

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • dndaughtery

    SSChampion

    Points: 11276

    Ok, thanks Phil.

  • Mike01

    SSChampion

    Points: 11284

    How many clients are you talking about?  it could get unwieldy pretty quickly.  Why did the multi-tenant db not work?

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • dndaughtery

    SSChampion

    Points: 11276

    Multi tenant didnt work due to business contract rules. We only have 3 clients. I do have a question though. Would using the other enviroment in the SQL agaent job step override the ssis project referenced enviroment?

  • Phil Parkin

    SSC Guru

    Points: 244732

    dndaughtery wrote:

    Multi tenant didnt work due to business contract rules. We only have 3 clients. I do have a question though. Would using the other enviroment in the SQL agaent job step override the ssis project referenced enviroment?

    Please check out this link. It explains the process in detail.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • dndaughtery

    SSChampion

    Points: 11276

    Thanks Phil, I've been looking for a resource like that...lol

    • This reply was modified 1 month, 3 weeks ago by  dndaughtery.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply