Post reply

Configuring Connection Managers For different DBs in different jobs

  • July 27, 2020 at 1:18 pm

    #3774963

    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?

  • July 27, 2020 at 1:25 pm

    #3774968

    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.

     

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 27, 2020 at 1:27 pm

    #3774969

    Ok, thanks Phil.

  • July 27, 2020 at 1:41 pm

    #3774975

    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/

  • July 27, 2020 at 1:45 pm

    #3774976

    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?

  • July 27, 2020 at 1:48 pm

    #3774980

    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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 27, 2020 at 6:59 pm

    #3775085

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

    • This reply was modified 4 years, 4 months ago by  dndaughtery.

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

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