Saturday, 25 July 2015

Case
I have an Integration Services catalog with multiple projects and one generic environment. This environment contains all possible variables to support all projects.

Multiple project using the same generic environment

How do I deploy my ispac files with PowerShell and automatically create a reference to this generic environment. (All parameter in my project are available as variable in the environment, but not all variables in my generic environment are available as parameter in my project.)

Solution
You can use the following PowerShell scripts as a base. Feel free to post changes in the comments below. This example uses two scripts. The first one is a simple script where you only store parameter values:

IspacFilePath is mandatory. Is contains the full path of the ispac file.

SsisServer is mandatory. It contains the servername (and instance name) of the Sql Server that has the Integration Services catalog.

FolderName is mandatory. It contains the name of the catalog folder. If it doesn't exist then it will be created

ProjectName is optional. If it is empty the filename from the ispac file will be used as projectname. However, unfortunately it must be equal to the internal projectname of the ispac file. The ispac wizard is able to change the projectname, but that doesn't seem to be possible with PowerShell (Edit: rename solution).

EnvironmentName is optional. If it is empty then no environment will be referenced

EnvironmentFolderName is optional. If it is empty then the script will search the environment in the project folder.

The second script is the generic deployment script which is called by the first script. Developers only change the parameters in the first script and pass it through to the server administrator who executes it.