July 19, 2018 at 8:02 am
Hopefully I'll explain this to make sense.........
Trying to help an organization with migrating to using SSIS Catalog.
In their current packages, they have Connection Managers with Expressions for InitialCatalog and ServerName .
One of the goals is to eliminate using Config Files that have the values for the variables used in the expressions.
I can make variables in the Catalogs Environment, but can't see how to have the system use those.
When I deploy the package, the Initial Catalog and Server Name are not Properties you can alter (since they have Expressions on them).
ConnectionString is visable and can be altered, but my testing has shown the Expression values trump whatever changes you make to the ConnectionString.
I know I can go into an Agent job and do an Override Property and alter the package variable.
But, we/they want to do something on a global level so there's only one location these need to be altered, which the Catalog Environment variable seems the best place.
The only thing I can think of it to remove the Expressions in the packages so they become visable in the Catalog and can be altered.
I am wondering of there is some other method from where things are now (basically not having to alter each existing package) that can accomplish migrating into the
Catalog world, but not requiring changing the existing packages too much.
TIA.
July 19, 2018 at 8:18 am
Sounds like what you want to use is a package parameter, rather than a variable. Then, prior to execution, you can define the value of that parameter. That will, however, require a little bit of changing of the package(s) I'm afraid.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 19, 2018 at 8:23 am
Yeah, I played around with that and know that's an option.
I know I can get it to work.........it's really a question of how to do it with the least impact to their current setup.
Maybe another way to ask it..........is there a way to get a Catalog's Environment variable applied to a variable that's in the package?
I don't see there's a way to do that..........again, I know a Property override on the Agent job can be done to the package variable, but trying to avoid that.
Thanks.
July 19, 2018 at 8:34 am
Does anyone know why does the InitialCatalog and ServerName info ovverride the ConnectionString ?
Must be some prioritization of how the system applies properties.
Is there a way to have the ConnectionString take priority? Since it is exposed in the Catalog I can control its setting.
My testing has just shown that it doesn't matter what it is set to when there are Expressions on the InitialCatalog and ServerName in the package.
July 19, 2018 at 8:35 am
doughora - Thursday, July 19, 2018 8:23 AMYeah, I played around with that and know that's an option.I know I can get it to work.........it's really a question of how to do it with the least impact to their current setup.
Maybe another way to ask it..........is there a way to get a Catalog's Environment variable applied to a variable that's in the package?
I don't see there's a way to do that..........again, I know a Property override on the Agent job can be done to the package variable, but trying to avoid that.Thanks.
You could add an Expression in the package to set the value of the variable to the parameter's value.
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.
July 19, 2018 at 8:35 am
doughora - Thursday, July 19, 2018 8:23 AMYeah, I played around with that and know that's an option.I know I can get it to work.........it's really a question of how to do it with the least impact to their current setup.
Maybe another way to ask it..........is there a way to get a Catalog's Environment variable applied to a variable that's in the package?
I don't see there's a way to do that..........again, I know a Property override on the Agent job can be done to the package variable, but trying to avoid that.Thanks.
Only via a Project or Package Parameter. Variables are encapsulated within the package. Just like you can't set the value of a variable in a stored procedure in T-SQL, only the parameters, the same applies to packages within SSISDB. The Parameters might then define the value of a variable, but that's the Stored Procedure/Package doing that, not the user executing it.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 20, 2018 at 1:45 pm
Here is how I do it.
CATALOG
Once the projects are added to the catalog, add the Environments i.e. PROD, TEST
Right click on the packages and select CONFIGURE, SELECT REFERENCE and add the environments
VISUAL STUDIO
Add a project.param CALLED Environment, data type = string, required = true, PUT PROD/TEST as the value (this is good for unit testing, the sql agent job will tell it what to use)
ADD parameter for a Server variable
ADD parameter for a Database variable
ADD Expressions to these variables with server name, database name
Database expression:
@[$Project::Environment]=="PROD"?"PRODDBNAME":"DEVDBNAME"
Server expression:
@[$Project::Environment]=="PROD"?"PRODSERVERNAME":"DEVSERVERNAME"
Create a OLE Connection
Click on properties, create an expression for Connection String
USE THIS:
"Data Source=" + @[User::Server variable] + ";Initial Catalog=" + @[User::Database variable] + ";Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"
SQL AGENT
Set a JOB, add a step to run the package
Package Source = SSIS Catalog
Server = put in name of catalog server
Choose appropriate authentication
select the package using the ... fro the catalog
CLICK ON Configuration
SELECT Checkbox for environment, select which one you want to use
That's the abbreviated version
July 20, 2018 at 1:47 pm
Sorry meant variable:
ADD variable for a Server variable
ADD variable for a Database variable
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply