How do I script the creation of a job to use environments?
-
I have been googling and googling and I have yet to find a way to check the environment box when configuring an SQL Job using an SSIS package. Specifically, when you configure a step of a job, there is a checkbox to use an Environment as shown in the image attached
I have been looking for a way to have that box checked in a script to create the job with SQL not manually coding it. There are times when we have to recreate jobs and the configuration goes away that we want to script out instead of manually configuring each one. In addition, the same job may exist on up to 6 servers so its reconfiguring a job x6 which adds a lot of overhead.
Anyhow, I was hoping someone had some insight into how to do this. I did try to set /ENVREFERENCE but I believe that just sets the reference, not actually checking the box. I believe the box has to already be checked for that to work. Any help would be greatly appreciated!
-
Have you tried creating a script from an already existing job. As that might outline how to set the value, since everything about job should be contained within a system created script.
-
not as easy as that. the environment within a SQL job step is a number related to the number of the environment on that particular server.
trying to recreate the same job on another server (or even on the same server if environments are dropped/recreated) would result on wrong environment being used.
the correct way is to generate the job, and then manually change the environment id to be retrieved from the SSIS catalog by name, and most likely as a parameter/variable to the script if environment names are different from server to server (most of mine reflect if environment is DEV/FIT/TST/PROD)
-
Okay so you have a script that returns you the current environment for the job and plug it into before deploying the new job -- sometimes for some things -- like this the manual process works -- I mean it is a job, how often do you change it -- if a lot maybe you should reconsider how you define the job -- maybe call an external Stored Procedure that is associated with the environment you need it to be associate rather than putting the code directly into the job.
Not seeing your job or knowing what you have already tried, does make it a lot harder to make a quality suggestion
-
This was removed by the editor as SPAM
-
This was removed by the editor as SPAM
-
This is exactly what I realized yesterday just before I left work for the day. I was thinking it was looking for the environment id not for the package but for the server. I realized this would need a query to identify what value is needed for each step of the job.
Thank you for your input!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply