Project Deployment Model - Multiple Projects in a Solution

Question

Project Deployment Model - Multiple Projects in a Solution

Adam McArdle

Mr or Mrs. 500

Points: 540
More actions
July 27, 2015 at 3:55 am

#319262

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!

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

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

Phil Parkin SSC Guru Points: 246965 More actions · Answer 1

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 ...

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

Adam McArdle Mr or Mrs. 500 Points: 540 More actions · Answer 2

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.

Lowell SSC Guru Points: 323518 More actions · Answer 3

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!

Adam McArdle Mr or Mrs. 500 Points: 540 More actions · Answer 4

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 🙁

Phil Parkin SSC Guru Points: 246965 More actions · Answer 5

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?

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

justin.sherborne Old Hand Points: 309 More actions · Answer 6

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

Adam McArdle Mr or Mrs. 500 Points: 540 More actions · Answer 7

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.

justin.sherborne Old Hand Points: 309 More actions · Answer 8

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 9

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

justin.sherborne Old Hand Points: 309 More actions · Answer 10

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