December 14, 2018 at 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
December 14, 2018 at 5:04 am
SimonH - Friday, December 14, 2018 3:32 AMHi 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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 14, 2018 at 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?
December 14, 2018 at 6:34 am
SimonH - Friday, December 14, 2018 6:19 AMThanks 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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 14, 2018 at 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.
December 14, 2018 at 6:56 am
SimonH - Friday, December 14, 2018 6:47 AMIn 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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 14, 2018 at 7:01 am
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
December 16, 2018 at 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?
--Jeff Moden
Change is inevitable... Change for the better is not.
December 16, 2018 at 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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 16, 2018 at 12:48 pm
Phil Parkin - Sunday, December 16, 2018 12:15 PMJeff 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
Change is inevitable... Change for the better is not.
February 7, 2019 at 4:27 am
Phil Parkin - Sunday, December 16, 2018 12:15 PMJeff 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?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply