October 2, 2018 at 10:31 pm
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 ...???
October 2, 2018 at 10:55 pm
It's the reference Id that you need, not the environment Id. Simple as that.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 2, 2018 at 11:17 pm
Phil Parkin - Tuesday, October 2, 2018 10:55 PMIt'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?
October 3, 2018 at 7:05 am
Scott In Sydney - Tuesday, October 2, 2018 11:17 PMPhil Parkin - Tuesday, October 2, 2018 10:55 PMIt'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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
October 2, 2020 at 3:24 pm
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) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply