Project Deployment Model - Multiple Projects in a Solution

  • We've recently upgraded to SQL2014 and we're keen to move over to Project Deployment Model for our SSIS Packages.

    One thing which is slightly off-putting is that you have to deploy all packages in a project together, rather than being able to deploy a single package. We have several hundred packages, and the idea of deploying them all just to make a small change seems a bit overkill. So we've decided to split them into multiple projects based on their functionality. This means we can only deploy a small number of related packages with each deployment.

    This is working well, with one exception. Opening up the Solution is a giant pain in the ***, as each of our projects is password protected. This means we get prompted for the password for every project in the solution, each time the Solution loads. This seems like a massive usability oversight, does anyone have any solution to it?

    It's simply not sustainable to be typing in dozens of passwords each time we open the Solution, which leaves us with three options:

    1. Go back to Package Deployment Model, and lose all the benefits of SSISDB and Project Parameters etc.

    2. Put everything in one Project, and end up having to deploy hundreds of packages each time

    3. Put everything in separate solutions, meaning we have to open multiple instances of Visual Studio.

    None of those seem particularly good, so I'm hoping that someone has a solution!

  • Adam McArdle (7/27/2015)


    We've recently upgraded to SQL2014 and we're keen to move over to Project Deployment Model for our SSIS Packages.

    One thing which is slightly off-putting is that you have to deploy all packages in a project together, rather than being able to deploy a single package. We have several hundred packages, and the idea of deploying them all just to make a small change seems a bit overkill. So we've decided to split them into multiple projects based on their functionality. This means we can only deploy a small number of related packages with each deployment.

    This is working well, with one exception. Opening up the Solution is a giant pain in the ***, as each of our projects is password protected. This means we get prompted for the password for every project in the solution, each time the Solution loads. This seems like a massive usability oversight, does anyone have any solution to it?

    It's simply not sustainable to be typing in dozens of passwords each time we open the Solution, which leaves us with three options:

    1. Go back to Package Deployment Model, and lose all the benefits of SSISDB and Project Parameters etc.

    2. Put everything in one Project, and end up having to deploy hundreds of packages each time

    3. Put everything in separate solutions, meaning we have to open multiple instances of Visual Studio.

    None of those seem particularly good, so I'm hoping that someone has a solution!

    You have not explained why there is password protection on the project files ...

    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.

  • Phil Parkin (7/27/2015)


    You have not explained why there is password protection on the project files ...

    We have our packages password-protected because they have credentials in them, and Project Deployment Model requires that the Project and the Packages have the same protection level.

  • Adam McArdle (7/27/2015)


    Phil Parkin (7/27/2015)


    You have not explained why there is password protection on the project files ...

    We have our packages password-protected because they have credentials in them, and Project Deployment Model requires that the Project and the Packages have the same protection level.

    should the data sources be using windows authentication as a best practice? are some of the connections going to non-sql server connections?

    if you used windows authentication, you would not need to encrypt the projects and data sources, and i think the problem would go away, right?

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (7/27/2015)


    if you used windows authentication, you would not need to encrypt the projects and data sources, and i think the problem would go away, right?

    The vast majority of them are Windows Auth, but there's one connection which we have to access via Username/Password. The same thing crossed my mind, but this connection gets used often enough that it still leaves quite a few projects requiring encryption 🙁

  • Adam McArdle (7/27/2015)


    Lowell (7/27/2015)


    if you used windows authentication, you would not need to encrypt the projects and data sources, and i think the problem would go away, right?

    The vast majority of them are Windows Auth, but there's one connection which we have to access via Username/Password. The same thing crossed my mind, but this connection gets used often enough that it still leaves quite a few projects requiring encryption 🙁

    For production, this info should be set via 'Sensitive' parameters in an SSISDB environment and should not be in a package at all.

    For development, can you use different (ie, non-production) credentials?

    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.

  • Hi,  I have the same problem.
    In my case I only have 6 projects in the one solution and they are all 'encrypt sensitive with password'.  So I only have to enter the password 6 times which is not too cumbersome yet seems totally unnecessary.
    From my understanding I can't use 'encrypt sensitive with user key' because then other users can't check out projects make changes and then execute them.  Other users need to be able to check out projects make changes, execute and test, then these changes will be merged with the master solution and deployed from there.

    So that leaves 'do not save sensitive' as the protection level.  Are you suggesting I use this.

    Thanks in advance

  • We've ended up just typing in the password multiple times when we load the solutuion. As you say, User Key is no use with multiple developers, Do Not Save isn't much use either so we've gone with Encrypt With Password as the least bad option.

  • Thanks Adam,  I am going to do this as well for now until I found out anything better otherwise

  • Adam McArdle - Monday, July 27, 2015 3:55 AM

    We've recently upgraded to SQL2014 and we're keen to move over to Project Deployment Model for our SSIS Packages. One thing which is slightly off-putting is that you have to deploy all packages in a project together, rather than being able to deploy a single package. We have several hundred packages, and the idea of deploying them all just to make a small change seems a bit overkill. So we've decided to split them into multiple projects based on their functionality. This means we can only deploy a small number of related packages with each deployment.This is working well, with one exception. Opening up the Solution is a giant pain in the ***, as each of our projects is password protected. This means we get prompted for the password for every project in the solution, each time the Solution loads. This seems like a massive usability oversight, does anyone have any solution to it?It's simply not sustainable to be typing in dozens of passwords each time we open the Solution, which leaves us with three options:1. Go back to Package Deployment Model, and lose all the benefits of SSISDB and Project Parameters etc.2. Put everything in one Project, and end up having to deploy hundreds of packages each time3. Put everything in separate solutions, meaning we have to open multiple instances of Visual Studio.None of those seem particularly good, so I'm hoping that someone has a solution!

    I am not clear on why you would need to open every project in Visual Studio every time.  If you setup separate solutions that only have specific related projects - then you should only be opening the solution you need to work on at that time.

    With that said - you really should be upgrading to SQL Server 2016 or later as that would allow you to deploy individual packages to the Integration Services Catalog without changing the deployment model to package deployment.  Although I have not tested - it may be possible to use SSDT 2015 (or later) to deploy to a 2014 target with the same ability.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thanks Jeffrey,
    After doing a bit of research I found out it is best not to add the .suo and .dtproj.user files to the version control repositories. 
    I think for us we can now just keep the whole solution in one project and each developer (only 3) can have a subversion working copy.  As long as we don't work on the same packages at the same time I don't see any conflict issues when we come to merge changes.

    Thanks for the posts

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

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