March 27, 2015 at 6:45 pm
I would recommend to use the registry to store a password though the configuration manager. It's a good practice so that the text is not exposed in a file or environmental variable somewhere.
----------------------------------------------------
March 28, 2015 at 5:34 am
MMartin1 (3/27/2015)
I would recommend to use the registry to store a password though the configuration manager. It's a good practice so that the text is not exposed in a file or environmental variable somewhere.
Instead of a 'sensitive' SSISDB parameter? Why?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 30, 2015 at 8:19 am
Hi Phil.
Are you using the project deployment model, or the package deployment model?
You need to use the former if you want to use environments. If you are not, I recommend that you learn about it.
I right click Projects then select 'Import Packages'. There is an option (as you know 🙂 ) to 'Deploy Packages'.
After package import I enter server password for my FTP connection manager. This worked so I stopped at that. Is there a better way or should I rest easy?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 31, 2015 at 1:26 am
2Tall (3/30/2015)
Hi Phil.Are you using the project deployment model, or the package deployment model?
You need to use the former if you want to use environments. If you are not, I recommend that you learn about it.
I right click Projects then select 'Import Packages'. There is an option (as you know 🙂 ) to 'Deploy Packages'.
After package import I enter server password for my FTP connection manager. This worked so I stopped at that. Is there a better way or should I rest easy?
Thanks,
Phil.
I have never used the Import Packages option. The way I do things is to do all my development work in Visual Studio and then to 'Build' the project I want to deploy. This creates an ispac file (somewhere under the project's 'bin' folder).
If you right-click a 'Projects' node in SSISDB, you then select the option to 'Deploy Project' and from there, select Project deployment file and navigate to the ispac you just created. Regarding passwords, at what stage were you prompted to enter this password?
IMO, passwords should be stored as sensitive parameters in an SSISDB environment and passed to the package at runtime. I had a quick look for a good link which describes this but so far haven't found anything great. I will try to find something later.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 31, 2015 at 2:29 am
Hi Phil. I was not prompted for the password. After importing the package into SSIDB I selected configure. I then selected 'Connection Managers'. I selected the connection manager of interest then clicked to set property of ServerPassword (this is masked out).
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 31, 2015 at 2:36 am
2Tall (3/31/2015)
Hi Phil. I was not prompted for the password. After importing the package into SSIDB I selected configure. I then selected 'Connection Managers'. I selected the connection manager of interest then clicked to set property of ServerPassword (this is masked out).Thanks,
Phil.
Got it. That's not a bad solution.
But if you wanted to use the same password in a different project, you'd have to type it in again. And then if the password changes ...
Using an environment allows you to centralise all your config parameters and then reuse them across projects.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 31, 2015 at 2:41 am
Hi Phil. I have attempted your method. I built project then deployed project. I then selected configure 'references' 'Add'. I then selected the environment I created with FTP connection string. Execution of the package failed. It would appear I am not correctly referencing/passing environment.
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 31, 2015 at 3:06 am
Well you learn something new every day. I was having a blonde moment. I have now correctly applied the environment variable.
Many thanks for your input.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 31, 2015 at 4:02 am
Phil one last question. If individual environment variables are created for Server Name / User / Password the subsequent SQL Agent Job appears to be only able to reference a single environment.
Any ideas on how can I use multiple variables and then schedule to run as a job?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 31, 2015 at 6:10 am
2Tall (3/31/2015)
Phil one last question. If individual environment variables are created for Server Name / User / Password the subsequent SQL Agent Job appears to be only able to reference a single environment.Any ideas on how can I use multiple variables and then schedule to run as a job?
Thanks,
Phil.
A single environment can contain many variables.
Projects can include references to multiple environments (not recommended, but could be one for QA and one for Prod, for example), but when you run a package, you can specify only one of these.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 31, 2015 at 7:12 am
Hi Phil. Indeed I can have multiple variables!
I found I had to perform an additional step within the SSIS project. On the FTP Connection Manager I chose Parameterize then created parameters. Within SSMS I mapped the parameter to the environment variable.
It all appears to be working and I am now using variables for passing required values.
Thanks for all your help.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
March 31, 2015 at 7:17 am
2Tall (3/31/2015)
Hi Phil. Indeed I can have multiple variables!I found I had to perform an additional step within the SSIS project. On the FTP Connection Manager I chose Parameterize then created parameters. Within SSMS I mapped the parameter to the environment variable.
It all appears to be working and I am now using variables for passing required values.
Thanks for all your help.
Phil.
No problem – you seem to have made a lot of progress: well done.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
March 31, 2015 at 7:22 am
Old dog, new tricks... 🙂
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply