Wednesday, August 29, 2012

SSIS 2012 Gotcha: DTExec.exe and the Environment Reference ID

Microsoft dropped the ball on this one. The DTExec.exe utility (MSDN docs) takes a parameter called EnvReference or Env for short. You would think that you could pass the name of your SSIS environment or maybe even the path to the environment, especially since the only way to find the ID is to query the catalog database. You would think.

Read along for a PowerShell function that will get the Environment Reference ID for you. In a future post I will put this together with another chunk of PowerShell that you can use to run packages from the standard command prompt or a PowerShell command prompt.

The function below figures out the appropriate SSIS environment based on the Environment Name chosen by the user (for example, Development, QA, Production, etc.) and the project/package path.

If the user chooses DEF as the Environment Name (or doesn't specify one), the script will look for a System/Machine Environment Variable called ENVIRONMENT_ID and use that. This allows you, the administrator, to specify the ENVIRONMENT_ID for each server and use the same script regardless of which environment the server is in.

Each project in the SSIS Catalog has it's own set of environment references. The script handles this by taking the path of the project or package and parses it on the backslashes. The first two segments, a blank and the reference to SSISDB, are ignored. The next two segments, the folder name and the project name, are used in a query against the SSISDB database to find the right reference ID.

If an environment is not found that matches the parameters provided, an exception is thrown.

Try it for yourself. Just paste the following PowerShell code into a .ps1 file to use it in your scripts.

Import-Module SQLServer Function Get-EnvironmentReferenceId (
[string]$EnvironmentName = "DEF", 
 [parameter(Mandatory = $true)][string]$ProjectPath) { If ($EnvironmentName -eq "DEF") 
# Get the default Environment for the current server environment { $EnvironmentName = [Environment]::GetEnvironmentVariable( 
"ENVIRONMENT_ID", "Machine") } $ProjectPathParts = $ProjectPath.Split("\") $ReferenceIdQuery = " SELECT er.reference_id FROM [internal].[folders] AS f JOIN [internal].[projects] AS p ON f.folder_id = p.folder_id JOIN [internal].[environment_references] AS er ON p.project_id = er.project_id WHERE = '{0}' AND = '{1}' AND er.environment_name = '{2}'" 
-f $ProjectPathParts[2], $ProjectPathParts[3], $EnvironmentName #$ReferenceIdQuery $QueryResults = Get-SqlData 
-SqlServer localhost 
-dbname SSISDB 
-qry $ReferenceIdQuery If ($QueryResults -eq $Null) { Throw New-Object System.ArgumentException 
"Environment '$EnvironmentName' is not configured for use with '$ProjectPath' on the this server.", 
"EnvironmentName" } Return $QueryResults[0] }

I hope you find this useful. If you find anything wrong with the script or have any helpful suggestions please share them in the comments.


  1. Thanks - I'm trying to execute a package from SQL agent and ran into the same issue with the environment reference.

  2. How about providing an example invocation? And output?