March 28, 2014 at 3:00 am
Hi - I would be interested to know whether any people have worked out how to get their SSISDB environment variables scripted and into source control.
Looking forward to any responses.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 30, 2014 at 8:51 pm
Environment variables are actually stored in the registry. So, exporting them using something like "regedt32" will give you a ".reg" file that you can save into source control.
The export function of regedt32 will export all environment variables. You can edit the ".reg" file with something like notepad and delete the bits you don't want.
Depending on whether you create user or system environment variables, you will need to export from
e.g. HKLM\System\ControlSet001\Session Manager\Environment (well this is where I found my system environment variables - not sure if this is actually the correct spot)
and
HKCU\Environment (for my user environment variables)
March 31, 2014 at 12:53 am
happycat59 (3/30/2014)
Environment variables are actually stored in the registry. So, exporting them using something like "regedt32" will give you a ".reg" file that you can save into source control.The export function of regedt32 will export all environment variables. You can edit the ".reg" file with something like notepad and delete the bits you don't want.
Depending on whether you create user or system environment variables, you will need to export from
e.g. HKLM\System\ControlSet001\Session Manager\Environment (well this is where I found my system environment variables - not sure if this is actually the correct spot)
and
HKCU\Environment (for my user environment variables)
Wrong type of environment variable: you are talking about the one stored in the OS (and your explanation is probably correct, I have never verified it). Phil is however talking about the SSIS variables stored in SSIS environments in the SSIS catalog. Unfortunately Microsoft in its infinite wisdom has given them the same names...
@Phil: the variables themselves are not objects but rows inside a table. So you can maybe script them out and add the scripts as miscellaneous files in Visual Studio? Seems like extra work though.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 31, 2014 at 3:59 am
Koen Verbeeck (3/31/2014)
happycat59 (3/30/2014)
Environment variables are actually stored in the registry. So, exporting them using something like "regedt32" will give you a ".reg" file that you can save into source control.The export function of regedt32 will export all environment variables. You can edit the ".reg" file with something like notepad and delete the bits you don't want.
Depending on whether you create user or system environment variables, you will need to export from
e.g. HKLM\System\ControlSet001\Session Manager\Environment (well this is where I found my system environment variables - not sure if this is actually the correct spot)
and
HKCU\Environment (for my user environment variables)
Wrong type of environment variable: you are talking about the one stored in the OS (and your explanation is probably correct, I have never verified it). Phil is however talking about the SSIS variables stored in SSIS environments in the SSIS catalog. Unfortunately Microsoft in its infinite wisdom has given them the same names...
@Phil: the variables themselves are not objects but rows inside a table. So you can maybe script them out and add the scripts as miscellaneous files in Visual Studio? Seems like extra work though.
Thanks Koen, yes, I already knew that was the case. However, I am not sure that SSISDB would be completely happy if I simply UPSERT rows into that table without using the various procs (like this one, for example).
So at the moment, I think that scripting out the variables would provide only a reference, not something that can be executed.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 31, 2014 at 4:00 am
So at the moment, I think that scripting out the variables would provide only a reference, not something that can be executed.
Apologies - I should have mentioned that this is a requirement in the original post.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 31, 2014 at 4:06 am
Phil Parkin (3/31/2014)
So at the moment, I think that scripting out the variables would provide only a reference, not something that can be executed.
Apologies - I should have mentioned that this is a requirement in the original post.
Doesn't scripting out the variables give you the execution script with all those stored procedure calls?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 31, 2014 at 5:47 am
Koen Verbeeck (3/31/2014)
Phil Parkin (3/31/2014)
So at the moment, I think that scripting out the variables would provide only a reference, not something that can be executed.
Apologies - I should have mentioned that this is a requirement in the original post.
Doesn't scripting out the variables give you the execution script with all those stored procedure calls?
I don't know how to do that - will investigate further when I have an SSISDB to play with.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 31, 2014 at 5:54 am
Phil Parkin (3/31/2014)
Koen Verbeeck (3/31/2014)
Phil Parkin (3/31/2014)
So at the moment, I think that scripting out the variables would provide only a reference, not something that can be executed.
Apologies - I should have mentioned that this is a requirement in the original post.
Doesn't scripting out the variables give you the execution script with all those stored procedure calls?
I don't know how to do that - will investigate further when I have an SSISDB to play with.
It's possible you can only do this when you are actually creating a variable through the GUI and then you choose to script your actions.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 31, 2014 at 8:13 am
To tell the truth, we are currently using a couple of environment variables, and will be using a max of 3 environments, so we were not planning to source control these.
It looks to me like if you wanted to do this, you'd just source control exec statements of the pre-built stored procedures.
But that would not be ideal for sensitive variables, as the script would then be storing them in plain text.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply