SQL Agent Job Use Different Environment Variable
-
Hi,
Using SQL2019, we have multiple projects and packages in our SSISDB. They all reference a single shared Environment which holds our Connection strings. I have a package using a project level Connection. This package by default uses Connection1, so configuring the sql agent job is simple and this job runs weekly. I want to create an sql agent job to run monthly and using the same Environment use Connection2. When configuring the sql agent job the Connection manager gives a free-form text field instead of the environment variable drop-down list. Is there any way to type in a value so the job know to use the other Connection String?
-
I could probably help if I fully understood the question 🙂
Are the following statements true?
- You have created an environment in SSISDB and associated that environment with multiple projects.
- You have created a SQL Agent job to run a package, using its default connection (that is, the one which it was configured to use during development in SSDT)
- You wish to modify the SQL Agent job so that the package connection is assigned from an environment variable, rather than using the default connection.
?
If so, it should be as simple as associating the environment with the job and then assigning the connection manager environment variable to the relevant connection in the 'Connection Managers' section.
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 -
It's a little different,
2. You have created a SQL Agent job to run a package, using its default connection (that is, the one which it was configured in a different environment variable in the same environment) e.g.
- agent1 runs with environment1 variablevalue1
- agent2 runs with environment1 variablevalue2
I've thought about having the all of the connection managers configured as project connections and then using a package parameter setup at the agent level to say use connection1, connection2. But I can't figure how to change the connection manager for a dataflow.
-
OK, got it. You can't do that. Options:
- Type in the connection string within the Agent job (ie, don't use an environment variable)
- Create another SSISDB environment containing the different variable values
- Add some logic in your package (effectively, IF Environment = ...... use ConnectionString1 ELSE use ConnectionString2, where both connections are stored in SSISDB variables)
(1) is the easiest – especially if this is a one-off. (2) is probably a non-starter, as you'll have lots of duplication between the environments & that's no fun to maintain. (3) might be best if this is something which you're going to be doing a lot.
If you're doing this because you want to run jobs in multiple environments (eg, DEV and PROD) from the same SSIS instance, take option (4): Create a new SSISDB instance and use that instead.
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 -
- I'd prefer not to do this as our best practice is to have a single spot with the connection strings.
- This would be a non-started because of duplication
- Can you speak about this more...I think this would work, I'm trying to think about how.
Another approach is that I can create multiple project connections, connection1, connection2 and assign environment variables to those. But when package runs tell it which one to use based on a package parameter, however it seems like connections can't be set on data flow. So I was thinking of setting up a dummy package connections that would be assigned to the data flow and then setting these connection strings at run time, but I'd have to read the project connection strings. Maybe create a script task to do that.
Basically what is being done here is we have a monthly snapshot and daily snapshot of our OLTP Database and the package needs to hit these. Same logic and processing but just different connections.
-
Somehow when the package runs, it needs to know what 'mode' it's in (monthly or daily).
Consider adding an ExecutionMode (M or D) parameter to the package, which is passed to the package by the Agent job (directly, not by an SSISDB variable).
With this in place, you should be able to use a combination of variables and expressions to modify the connection manager you are interested in.
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 -
Thanks yes I think this is the route, using that input parameter, I like the name of it as well. The only challenge I face is reading those package connection managers. I don't know how to do that at this point. If M then use connection1 if D then use connection2. Expressions are available on sql tasks but not for this on data flow. I don't want to make any assumptions about those connection managers except that they are of the same type, Oracle/OLE DB, like trying to build those strings in variables if M then the connection string used by the package will always be 123, it needs to pick that up from the project connection, which is set by the environment variable.
-
you don't change the connection manager - you change the connection string that the connection manager uses.
only case where this is not possible is when your connection is a flat file with different layouts - and on this case you will need 2 dataflows and you use one or the other based on the parameter Phil mentioned.
-
Thanks for your input, conceptually I am having a block in what is being suggested vs my SSIS Development experience.
- I'm good with this part: have a variable e.g. @[User::oracleConnectionString] that holds the connection string and using an expression on the connection manager update/override that connection string with said variable.
- How do I set the variable @[User::oracleConnectionString] with the appropriate value without hard-coding anticipated values? These values need to come from a single-list of environment variables deployed on the server.
-
OK, let's assume that the ExecutionMode parameter is set up.
Let's also assume that you have two connection string parameters set up:
ConnectionD and ConnectionM
These are passed into the package at run time from your SSISDB environment.
Within your package, create a String variable, SelectedConnection, and configure that variable as an Expression. The rough syntax you want is along the lines of
ExecutionMode=="D" ? ConnectionD : ConnectionM
With this in place, we know that our SelectConnection variable will contain the right connection string at runtime.
Now edit the properties for your Connection Manager (not on the data flow or ExecuteSQL task, but on the connection itself). Click on Expressions and assign the variable to the ConnectionString property.
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 -
so I think the difference is that I don't have these connection string parameters in use, I'm setting the environment variables on the actual project connections, connections tab in the SSISDB.
-
OK, that makes it more tricky. It should be possible to extract those connection strings into a package-scoped variable, but I think you would need to add a Script Task to do that. This is not something I have done, so I cannot guarantee it.
Another less-complex option is to have two data flow tasks, one targeting the M table and one targeting the D table. Use a precedence constraint to drive the processing down one path or the other, depending on the value of ExecutionMode.
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 -
I am doing the less complex option right now.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply