December 20, 2021 at 10:14 pm
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?
December 21, 2021 at 10:01 am
I could probably help if I fully understood the question 🙂
Are the following statements true?
?
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
December 21, 2021 at 1:46 pm
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.
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.
December 21, 2021 at 1:57 pm
OK, got it. You can't do that. Options:
(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
December 21, 2021 at 3:20 pm
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.
December 21, 2021 at 4:08 pm
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
December 21, 2021 at 6:07 pm
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.
December 21, 2021 at 6:55 pm
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.
December 21, 2021 at 7:28 pm
Thanks for your input, conceptually I am having a block in what is being suggested vs my SSIS Development experience.
December 22, 2021 at 7:36 am
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
December 22, 2021 at 4:18 pm
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.
December 22, 2021 at 5:25 pm
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
December 22, 2021 at 5:50 pm
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