CODEWITHSUNDEEP
sql-serverssissql-server-2012
sagi
sagi

Reputation: 40481

SSIS Catalog environment variables

I'm trying to use environment variables to define parameters for my connection strings at run time.

I tried to follow some guides online . So I defined the parameter in the package:

enter image description here

Now to the problem :
The guide works, the only problem is that every time a package is executed, I need to manually choose the environment folder I want to use, although I already did it in the package.

enter image description here

SSIS doesn't presume to know which environment to use (even if only one exists)

How do I override this?

I saw I can use a scheduled Job, and tell it which environment to use, but that's not helpfull for me since I'm running my package via a service and a procedure . I need some way of telling it which environment to choose.

Upvotes: 0

Views: 3769

Answers (1)

PacoDePaco
PacoDePaco

Reputation: 699

You cannot set one environment for good - the idea for environments is that you choose which one you want to be used during the execution. As you pointed out, you can do that in a job. So does that mean that agent jobs have super-natural powers? No. If you script a job with a step which executes a package referencing an environment, you will see a familiar dtexec command, e.g.:

    /ISSERVER "\"\SSISDB\test\test1\Incremental.dtsx\"" /SERVER "\"DESKTOP-NT4K1HQ\"" 
/ENVREFERENCE 1 /Par "\"$ServerOption::LOGGING_LEVEL(Int16)\"";1 
/Par "\"$ServerOption::SYNCHRONIZED(Boolean)\"";True /CALLERINFO SQLAGENT /REPORTING E

What I would like to highlight here is the /ENVREFERENCE 1 parameter - here is how the agent job knows which environment to choose.

So the answer to your question is that you always need to choose an environment while executing a package (of course if you want one to be used). In case of a catalog procedure you would do something like (per this article):

    EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Child1.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'TestDeply4', @project_name=N'Integration Services Project1', 
@use32bitruntime=False, 
@reference_id=1

Where @reference_id = 1 is your environment reference. In order to find your environment's ID you have to query the [SSISDB].[catalog].[environments] view.

Did I answer your question or do you execute your packages in a different way? If you are sure that you will never need more than one environment then that woulld be a sign that you do not need environment variables at all.

Upvotes: 4

Related Questions