SSIS Maitenance Question - You may know the answer

Question

SSIS Maitenance Question - You may know the answer

Viewing 7 posts - 16 through 21 (of 21 total)

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

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 1

Click on References then Add your environment variables

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

All Set Now ! Thx
I also had forgotten to create a variable. Did that ( see pic below )
and then like you mentioned, I added it to the References and was able to use it.
Thx.

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

mw_sql_developer - Tuesday, May 1, 2018 11:41 AM
All Set Now ! Thx
I also had forgotten to create a variable. Did that ( see pic below )
and then like you mentioned, I added it to the References and was able to use it.
Thx.

Great, well done. Just a quick note: if/when you set up an Agent job to run the package, you will need to select the environment(s) you want to use as part of setting up the job (that's because you can (but I don't recommend it) associate multiple environments with the same project).

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

sgmunson SSC Guru Points: 110640 More actions · Answer 4

mw_sql_developer - Monday, April 30, 2018 2:06 PM
Phil, Yes they are deployed!
RE: If so, have you investigated using SSISDB environments to control such things
Do you mean environment variables ? Like sgmunson mentioned ?
Ok my next question: How do I configure a SQl Task to get a a value from a environment variable. I need some help here. I am not sure how to or where to declare a environment variable ?

The environment variable has to be set at the console of the server with an administrator login, and if you use the GUI to set it, that's one way, or alternately, from the command prompt, you use the SETX command, which you can get help for by typing HELP SETX at that same command prompt. You can decide the name of the environment variable, but be sure to NOT use one that there's already a value for. You can see all of the existing ones just by typing SET and hitting enter at the command prompt. As to then bringing that data into SSIS, you would need a package variable defined with a string data type, and then part of the SSIS configuration you set up (and the first one for each package), is the one that gets an environment variable into a package variable. That package variable then is the value used for the connection string to the SSIS_DB configuration database. From that database, you can then have additional package configuration items that bring in values for any of a number of things. This then allows you to have different values in said database for the different environments that you have, because you'll have a separate database for each environment.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

mw_sql_developer SSCoach Points: 19445 More actions · Answer 5

sgmunson - Tuesday, May 1, 2018 1:20 PM
mw_sql_developer - Monday, April 30, 2018 2:06 PM
Phil, Yes they are deployed!
RE: If so, have you investigated using SSISDB environments to control such things
Do you mean environment variables ? Like sgmunson mentioned ?
Ok my next question: How do I configure a SQl Task to get a a value from a environment variable. I need some help here. I am not sure how to or where to declare a environment variable ?
The environment variable has to be set at the console of the server with an administrator login, and if you use the GUI to set it, that's one way, or alternately, from the command prompt, you use the SETX command, which you can get help for by typing HELP SETX at that same command prompt. You can decide the name of the environment variable, but be sure to NOT use one that there's already a value for. You can see all of the existing ones just by typing SET and hitting enter at the command prompt. As to then bringing that data into SSIS, you would need a package variable defined with a string data type, and then part of the SSIS configuration you set up (and the first one for each package), is the one that gets an environment variable into a package variable. That package variable then is the value used for the connection string to the SSIS_DB configuration database. From that database, you can then have additional package configuration items that bring in values for any of a number of things. This then allows you to have different values in said database for the different environments that you have, because you'll have a separate database for each environment.

Good! Thx, My next queston: I am not sure what Phil is talking about the JOBS. Yes, we have JOBS set up for each package. If I change a package ( I mean if I set it to use the environment variables ( which i will be doing ) ) how do I make changes to the job so that it runs without error. ?

sgmunson SSC Guru Points: 110640 More actions · Answer 6

mw_sql_developer - Tuesday, May 1, 2018 1:27 PM
sgmunson - Tuesday, May 1, 2018 1:20 PM
mw_sql_developer - Monday, April 30, 2018 2:06 PM
Phil, Yes they are deployed!
RE: If so, have you investigated using SSISDB environments to control such things
Do you mean environment variables ? Like sgmunson mentioned ?
Ok my next question: How do I configure a SQl Task to get a a value from a environment variable. I need some help here. I am not sure how to or where to declare a environment variable ?
The environment variable has to be set at the console of the server with an administrator login, and if you use the GUI to set it, that's one way, or alternately, from the command prompt, you use the SETX command, which you can get help for by typing HELP SETX at that same command prompt. You can decide the name of the environment variable, but be sure to NOT use one that there's already a value for. You can see all of the existing ones just by typing SET and hitting enter at the command prompt. As to then bringing that data into SSIS, you would need a package variable defined with a string data type, and then part of the SSIS configuration you set up (and the first one for each package), is the one that gets an environment variable into a package variable. That package variable then is the value used for the connection string to the SSIS_DB configuration database. From that database, you can then have additional package configuration items that bring in values for any of a number of things. This then allows you to have different values in said database for the different environments that you have, because you'll have a separate database for each environment.
Good! Thx, My next queston: I am not sure what Phil is talking about the JOBS. Yes, we have JOBS set up for each package. If I change a package ( I mean if I set it to use the environment variables ( which i will be doing ) ) how do I make changes to the job so that it runs without error. ?

If a package happens to use an SSIS package configuration to pull in an environment variable on the server, just so that is has the correct connection string to the database it needs to access, that has no bearing on the Agent job that runs it.

EDIT: the server that needs that environment variable is the server that the SQL Agent job runs on, which may or may not be the database server,

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Phil Parkin SSC Guru Points: 246961 More actions · Answer 7

mw_sql_developer - Tuesday, May 1, 2018 1:27 PM
sgmunson - Tuesday, May 1, 2018 1:20 PM
mw_sql_developer - Monday, April 30, 2018 2:06 PM
Phil, Yes they are deployed!
RE: If so, have you investigated using SSISDB environments to control such things
Do you mean environment variables ? Like sgmunson mentioned ?
Ok my next question: How do I configure a SQl Task to get a a value from a environment variable. I need some help here. I am not sure how to or where to declare a environment variable ?
The environment variable has to be set at the console of the server with an administrator login, and if you use the GUI to set it, that's one way, or alternately, from the command prompt, you use the SETX command, which you can get help for by typing HELP SETX at that same command prompt. You can decide the name of the environment variable, but be sure to NOT use one that there's already a value for. You can see all of the existing ones just by typing SET and hitting enter at the command prompt. As to then bringing that data into SSIS, you would need a package variable defined with a string data type, and then part of the SSIS configuration you set up (and the first one for each package), is the one that gets an environment variable into a package variable. That package variable then is the value used for the connection string to the SSIS_DB configuration database. From that database, you can then have additional package configuration items that bring in values for any of a number of things. This then allows you to have different values in said database for the different environments that you have, because you'll have a separate database for each environment.
Good! Thx, My next queston: I am not sure what Phil is talking about the JOBS. Yes, we have JOBS set up for each package. If I change a package ( I mean if I set it to use the environment variables ( which i will be doing ) ) how do I make changes to the job so that it runs without error. ?

The confusion here is that Steve and I are talking about different types of environment variables. You should use one or the other. The way Steve describes was the only possible solution until SQL Server 2012 (I think).
When you are using environment variables from within SSISDB to configure the runtime values of package and project parameters and you set up a SQL Agent job to run the package, you need to tell the job which environment to use when running the package. This is done from the Configuration tab of the job step.

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