August 16, 2022 at 8:57 am
Ok, so I understand what they are, they're collections of parameter assignments that you can tell a package to use upon execution.
What I'm trying to understand is why or if I need to use them.
I'm migrated a bunch of old SSIS packages using the packages deployment model to some new servers using the project deployment model. I have one project containing about 35 packages. I've created parameters on all my packages, and have a couple of project level parameters for stuff like Server Name etc.
I'm developing on my PC and the packages will have their parameters set to my dev environment settings by default unless I change them.
I'm deploying my packages to 3 servers (Test, UAT, Prod). I deploy them from Visual Studio.
Each server runs an identically scripted SQL job to execute the package.
So now, I need to set my parameters for each environment/server.
Do I need to set up environments, or why can't I just right click - configure my Project in the SSIS Integration Services Catalog on each server, and set the parameters there for the project and each package?
If I create environments, I still need to enter all the parameter values for each server/environment, but then I need to set up the reference between the project and the environment, and set each SQL job to use the relevant environment when executing the job.
Are environments only useful if you have one server, one package catalogue, and one set of SQL Jobs, and you're just using different databases for each environment so you need the environments to toggle between each?
Aren't they overkill if you have your environments on different servers, or am I missing something?
August 16, 2022 at 2:19 pm
We only have one server with one catalogue.
We use it to override settings that may come from deployments. New project -> Configure -> Link to environment. You may not need it if you automatically redeploy when a setting changes
If a connectionstring needs a change -> update the associated variable -> all linked packages will use the new setting without the need to redeploy.
August 16, 2022 at 2:45 pm
We only have one server with one catalogue.
We use it to override settings that may come from deployments. New project -> Configure -> Link to environment. You may not need it if you automatically redeploy when a setting changes
If a connectionstring needs a change -> update the associated variable -> all linked packages will use the new setting without the need to redeploy.
If packages contain shared connection strings, I can make them Project Parameters, right click on the Project in the Catalogue and choose, Configure, and set the Project parameter which all packages on the project will use.
Still not sure I need an environment if I have 3 separate SSISDBs on 3 separate servers.
August 16, 2022 at 6:21 pm
I don't know about setting them directly in the catalog, but you can set the project or package parameters in a sql agent job step. Once a parameter has been been edited from the job step the value turns bold, which means it will not be overwritten next time the project is deployed. This definitely works for configuring specific parameters for each environment.
I have never tried changing the config directly in the catalog, but it looks like it works the same way, although it is made more clear that you are overriding the default value from package.
Once the parameter has been explicitly set it is passed in to the dtexec command. This can sometimes stop it working if the parameter contains an invalid character. If you are simply setting a server or database name it's likely to be fine, but if passing in directory paths you need to be aware of back slashes acting as escape characters (I think that caused the problem I am thinking of).
August 17, 2022 at 7:10 am
If you have things called 'Development and Production' environments and 'Change Control' then SSIS environments can be useful.
A place I used to work had dedicated staff doing the Production implementations. Everything had to be scripted. Ad-hoc changes to code at implementation time got bad marks. Nothing unusual here.
They did a lot of SSIS. Being able to implement package updates from Dev to Prod merely by changing the Environment was acceptable. Changing a host of individual values was not, it was deemed too risky.
Other places may do implementation differently. Another place where I worked did virtually no SSIS were happy to change individual parameters the the once or twice a year a package changed.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
August 17, 2022 at 1:20 pm
Jo Pattyn wrote:We only have one server with one catalogue.
We use it to override settings that may come from deployments. New project -> Configure -> Link to environment. You may not need it if you automatically redeploy when a setting changes
If a connectionstring needs a change -> update the associated variable -> all linked packages will use the new setting without the need to redeploy.
If packages contain shared connection strings, I can make them Project Parameters, right click on the Project in the Catalogue and choose, Configure, and set the Project parameter which all packages on the project will use.
Still not sure I need an environment if I have 3 separate SSISDBs on 3 separate servers.
OK, so now, say, your project contains a dozen or two different parameters that are environment-specific and you need to run your package against a different environment. Which is easier (and less error-prone), changing all of the parameters in the SSIS project, one-by-one, or picking one environment that already has all your parameters set for the specific environment you want to run against?
Environments, at least for me, are also more "discoverable". If I know my packages use environments then I can just go through the environment to see what values are being used, rather than having to dig through the project properties to find that rogue value that's breaking my package because it's got the wrong value.
A project can also reference many environments, so you can easily switch between them if needed (if you're using SQL Agent to run your jobs, say, as Ed B mentioned). It's also possible to override a parameter value using environments (also as Ed B mentioned), but I tend to avoid that, preferring to go back and change the environment (unless it's a one-off test or something).
For me, the most compelling reason to use them is that I can set up an environment once, verify its values are correct, and not have to touch them again. Going through each var and setting at the project level is prone to error, and SSIS is hard enough to debug without having to deal with a break because I fat-fingered a parameter value.
August 17, 2022 at 2:54 pm
planetmatt wrote:Jo Pattyn wrote:We only have one server with one catalogue.
We use it to override settings that may come from deployments. New project -> Configure -> Link to environment. You may not need it if you automatically redeploy when a setting changes
If a connectionstring needs a change -> update the associated variable -> all linked packages will use the new setting without the need to redeploy.
If packages contain shared connection strings, I can make them Project Parameters, right click on the Project in the Catalogue and choose, Configure, and set the Project parameter which all packages on the project will use.
Still not sure I need an environment if I have 3 separate SSISDBs on 3 separate servers.
OK, so now, say, your project contains a dozen or two different parameters that are environment-specific and you need to run your package against a different environment. Which is easier (and less error-prone), changing all of the parameters in the SSIS project, one-by-one, or picking one environment that already has all your parameters set for the specific environment you want to run against?
Packages deployed to each server environment (TEST, UAT, Live), will never run against a different environment. If I want to run a package against a different environment, I just run the package deployed to that server. I'll only ever need to set parameters one by one in the first deployment. So whether I do that on the Project in the catalogue or in the environment makes no difference. I'm never going to go through and repoint package parameters one by one to point at a different environment. I'll simply connect to the SQL Server for that environment and run the package from there.
August 17, 2022 at 3:47 pm
IF you have an SSIS server per environment AND you are sure you'll NEVER need to change the parameters in the future (yeah, right) AND you never need to go through the values to examine/verify validate them AND you have all the access you need to do this (e.g., you have read/write access to your production SSIS environment[!]), then yeah, sure, you probably don't need environments. Are you also the only person who will ever have to work with this?
For your case, I think environments might be more of a convenience. For us, the convenience of having a set of parameters all in one place (environment), easily accessible by other users (in the "Environments" in the SSIS catalog, rather than having to update the project configuration, is a good motivation for using environments We also sometimes have multiple "environments" that we sometimes switch between, and picking an environment (from a drop down list in a SQL agent job) that has all of the correct settings (that I don't have to slog through and update individually, at the risk of fat-fingering a value) is a good reason to use environments.
Anyway, you don't have to argue with me about why you don't want to use environments. If you don't want to use them, if they don't fit your workflow, then don't use them. In our case they are more convenient and more conducive to a supportable environment. I would still use environments in your case, but YMMV, you do you.
August 17, 2022 at 5:39 pm
Heh... when I read through this, my thought was "This is one of the primary reasons I don't use SSIS". 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply