Passing environment ID to dtexec

  • Hi,

    I've deployed an SSDT project using the project deployment model.
    I've configured two environments Development and Production.
    If I view the environment Identifier by viewing their properties the ID is listed as 5 and 6 respectively.
    I've configured the project to use those two environments.
    I have to launch the packages via Powershell and the dtexec command.

    When I specify the dtexec command as:

    dtexec.exe /server "MYSERVER" /isserver "\SSISDB\MyFolder\MyProject\MyPackage.dtsx" /env 5

    I get this error:

    Failed to execute IS server package because of error 0x80131904. Server: MYSERVER, Package path: \SSISDB\MyFolder\MyProject\MyPackage.dtsx, Environment reference Id: 5.
    Description: The environment reference '5' is not associated with the project.

    Googling around I found this query:

    SELECT reference_id
    FROM SSISDB.[catalog].environment_references er
       JOIN SSISDB.[catalog].projects p ON p.project_id = er.project_id
    WHERE er.environment_name = 'Development'
     AND p.name     = 'MyProject';

    This query returns 11.

    This value works in dtexec.

    What the ...???

  • It's the reference Id that you need, not the environment Id. Simple as that.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Phil Parkin - Tuesday, October 2, 2018 10:55 PM

    It's the reference Id that you need, not the environment Id. Simple as that.

    Is there a way to get the reference id from the GUI?

  • Scott In Sydney - Tuesday, October 2, 2018 11:17 PM

    Phil Parkin - Tuesday, October 2, 2018 10:55 PM

    It's the reference Id that you need, not the environment Id. Simple as that.

    Is there a way to get the reference id from the GUI?

    Good question. I have found no direct way, but you can get it indirectly:

    Create a new random SQL Agent job which executes a package in the project, with the environment reference set (on the Configuration tab for the step). Then click on the Script button – the generated script should include the EnvReference parameter value.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • In SQL Server 2019, this value can be provided by scripting out the addition of the reference, which you create in SSMS (the reference, that is), and rather than clicking on OK, just click on Script (or to the immediate right and then choose whether it's a new query window or clipboard or whatever the 3rd option is), and then Cancel out of the create reference window and just run the script, which will select that value for you after having created the reference by virtue of running the script.

    Steve?(aka sgmunson)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply