October 21, 2022 at 4:56 pm
No. I mean that as I cannot get the environmental parameters to work, I have to actually change the server names in the BIDS environment to either the dev server or prod server and then deploy to that server. That's not without risk. If I understand the environmental variable correctly, I should be able to create variables in the connection string, set them on the respective servers and then simply deploy the result without needing to change the server. That would eliminate the risk of accidentally deploying to the wrong server.
October 22, 2022 at 1:43 pm
Indeed. I use localhost for my SQL server connections while developing and that is what gets checked in.
After deploying to another server, it is straightforward (once you know what you are doing) to map the contents of a variable in an SSISDB environment to the connection string within the package.
When executing the package, you reference the environment whose (mapped) variables you wish to use.
Regarding this text: "I should be able to create variables in the connection string, set them on the respective servers and then simply deploy the result without needing to change the server." You don't need to create any variables at all. SQL Server automatically exposes project and package connection strings (assuming you are not creating them programmatically), ready for them to be overridden.
I think that the SSIS stairway demonstrates this process, but I can write out a more detailed set of instructions if that's not clear enough.
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
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply