Sunday, 14 May 2017

Import and export SSIS Catalog Environments with JSON

Case
I want to import and export Environments to/from my SSIS Catalog. Doing it manually in SSMS takes ages. How can you do that more quickly?

I want to export this environment

Solution
I will ago I created a couple of PowerShell scripts to deploy environments to your SSIS Catalog with a CSV file, database table or an array as source. The script below is a follow up that allows you to export one or more environments as json files, but also has an import method to deploy those exported environments to a Catalog: Get-CatalogEnvironment and Set-CatalogEnvironment.

This is an example of how you execute the two methods. It first starts with importing a separate script file with various methods and then you can either execute the Get or the Set method:

Later on I will add various extra methods for example to test the existence of an environment, to delete an environment, to move an environment, to copy an environment, to rename an environment or to connect an environment to a project. Please let me know if you have any suggestions for extra functionality or improvements!

6 comments:

Thanks a lot for sharing your script. I did some testing and it turns out te be helpfull. However, I did run into one minor issue in copying a Boolean variable with configured value False. The Set-CatalogEnvironment module (line 429) evaluates this as if no value was provided and therefor skips it. Of course the script should simply copy the boolean value False.

Switching from the values value (!$_.Value) to the values length ($_.Value.ToString().Length -eq 0) could solve this.

When it comes to improvements, parametrizing the sql versions might be a handy one.

Hi Joost, today I used your script once more and found a small typo in the Set-CatalogEnvironment function. The Write-Verbose in line 305 should use variable $DeleteExistingEnvironment instead of $ImportFilePath. Right ?Anyway, thnx again and all the best. Cees