Automaticaly Build and Deploy SSIS packages.

Question

Automaticaly Build and Deploy SSIS packages.

Simon Hundleby

SSCarpal Tunnel

Points: 4564
More actions
December 14, 2018 at 3:32 am

#376655

Hi we're trying to automatically build and deploy an SSIS package up to the Integration Services Catalogue when we check changes into TFS.
We're using Devenv to build the solution via the cmd line, the problem lies in the Encryption Level at build time.
We can't use DontSaveSensitive as we have SQL logins with passwords.
We can't use EncryptSensitivedateWithUSerKey as the agent job that builds it would have to put the password in.
We would like to use EncryptSensitiveDataWithPassword but there doesn't seem to be a way to pass the password in as a parameter
during the build process.
Anyone have any experience of this.
Any help would be appreciated.
Regards,
SImon

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: 246960 More actions · Answer 1

SimonH - Friday, December 14, 2018 3:32 AM
Hi we're trying to automatically build and deploy an SSIS package up to the Integration Services Catalogue when we check changes into TFS.
We're using Devenv to build the solution via the cmd line, the problem lies in the Encryption Level at build time.
We can't use DontSaveSensitive as we have SQL logins with passwords.
We can't use EncryptSensitivedateWithUSerKey as the agent job that builds it would have to put the password in.
We would like to use EncryptSensitiveDataWithPassword but there doesn't seem to be a way to pass the password in as a parameter
during the build process.
Anyone have any experience of this.
Any help would be appreciated.
Regards,
SImon

DontSaveSensitive is the way to go, in my opinion.
Sensitive params can then be configured to accept their values from sensitive SSISDB environment variables.

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

Simon Hundleby SSCarpal Tunnel Points: 4564 More actions · Answer 2

Thanks Phil,

That's pretty much how we did it, although we just passed the sensitive parameters in the project
configuration. Maybe I should use Environment Variables, but I've never really using them, what's the benefit?

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

SimonH - Friday, December 14, 2018 6:19 AM
Thanks Phil,
That's pretty much how we did it, although we just passed the sensitive parameters in the project
configuration. Maybe I should use Environment Variables, but I've never really using them, what's the benefit?

Can you expand on this, please? I can see no way of passing sensitive params using the project config.

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

Simon Hundleby SSCarpal Tunnel Points: 4564 More actions · Answer 4

In the connection manager properties at project level in the Information Service Catalogue,
We can set the Username and Password for each of the connections,
note the other thing we did slightly differently was to set the Encryption Level to
EncryptSensitiveWithUserKey .

We've tested this a number of times and as these values are persisted it worked after a number of deployments.

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

SimonH - Friday, December 14, 2018 6:47 AM
In the connection manager properties at project level in the Information Service Catalogue,
We can set the Username and Password for each of the connections,
note the other thing we did slightly differently was to set the Encryption Level to
EncryptSensitiveWithUserKey .
We've tested this a number of times and as these values are persisted it worked after a number of deployments.

I think you mean the Integration Services Catalog.

A better way of doing this is to create an environment containing all of the variables and values you need.

Then configure the project to reference the environment.

Then map the environment variables to the project connections and parameters.

A big benefit of doing this is that you only need create the variables once â€“ from there you can map them to as many projects/packages as you want. Have a read here to get started with SSISDB environments.

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

Simon Hundleby SSCarpal Tunnel Points: 4564 More actions · Answer 6

from there you can map them to as many projects/packages as you want

That's going to be a massive benefit, We have 100's of packages, that we are going to add to the process, We can then just
map them all to the same (1 or 2) different environments, Definitely the way to go.

Thanks

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

@Phil...
As you know, I'm far from even being a casual user of SSIS never mind an authority of any kind on the subject but...Isn't the OP going to have to do a one time modification of each of his "100's of packages" to take advantage of the variable/mapping that you speak of?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 8

Jeff Moden - Sunday, December 16, 2018 10:17 AM
@Phil...
As you know, I'm far from even being a casual user of SSIS never mind an authority of any kind on the subject but...Isn't the OP going to have to do a one time modification of each of his "100's of packages" to take advantage of the variable/mapping that you speak of?

If all that we are talking about is setting run-time overrides of the values of certain existing parameters and connection strings within SSIS packages and projects, no code changes will be required to the packages.

But ... there is a lot of one-off config work to do to create the mappings from SSISDB environment variables to those parameters and connection strings. With care, this can all be scripted, but the scripting support is not as good as it might be.

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

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

Phil Parkin - Sunday, December 16, 2018 12:15 PM
Jeff Moden - Sunday, December 16, 2018 10:17 AM
@Phil...
As you know, I'm far from even being a casual user of SSIS never mind an authority of any kind on the subject but...Isn't the OP going to have to do a one time modification of each of his "100's of packages" to take advantage of the variable/mapping that you speak of?
If all that we are talking about is setting run-time overrides of the values of certain existing parameters and connection strings within SSIS packages and projects, no code changes will be required to the packages.
But ... there is a lot of one-off config work to do to create the mappings from SSISDB environment variables to those parameters and connection strings. With care, this can all be scripted, but the scripting support is not as good as it might be.

Thanks, Phil.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Simon Hundleby SSCarpal Tunnel Points: 4564 More actions · Answer 10

Phil Parkin - Sunday, December 16, 2018 12:15 PM
Jeff Moden - Sunday, December 16, 2018 10:17 AM
@Phil...
As you know, I'm far from even being a casual user of SSIS never mind an authority of any kind on the subject but...Isn't the OP going to have to do a one time modification of each of his "100's of packages" to take advantage of the variable/mapping that you speak of?
If all that we are talking about is setting run-time overrides of the values of certain existing parameters and connection strings within SSIS packages and projects, no code changes will be required to the packages.
But ... there is a lot of one-off config work to do to create the mappings from SSISDB environment variables to those parameters and connection strings. With care, this can all be scripted, but the scripting support is not as good as it might be.

Hi Phil,
Is there anyway to reference the Environment variables prior to deployment, Assuming the Environment Variable Exists.
Hence in BIDS reference the environment variable, Then once deployed we would not need to do the mapping?