This venture was first inspired by watching and reading Steve Jones’ (Blog|Twitter) PowerShell challenge. I always knew that PowerShell was a powerful tool and one that I needed to jump into at some point in my career. I dabbled with it some last year and did some small things, but it became more evident heading into this year that I needed to get more accustomed to all the intricacies and the usefulness I could gain from it as a data professional.

One major paint point in automation portion of deploys was the fact of deploying .ispacs in SSIS 2012. Now granted the actual deploy is not cumbersome, in my opinion, Microsoft has done a decent job this go around with how to deploy with the wizard etc. However, a good developer friend of mine decided that he wanted to start out on the PowerShell venture as well and has since taught me a few things regarding deployments and how to utilize power shell. Who knew that developers and DBA’s could actually work on something and an idea together; just another step in bridging that gap.

Keep in mind that the below code has dummy environmental variable and parameters for obvious reasons. This is just to provide an example and for one tweak it in hopes it may help another data professional at a future time.

The Code

First line is the most important for this venture. It sets the parameter of what action will be taken in the script.

param([Int32]$step = 1)

1. If the value is 1 then the process will create the folder, deploy the package, set up the environment variable and parameter, reference parameters (do everything)
2. If the value is 2 then the process will just re-deploy the package
3. If the value is 3 then the process will change the environment variable/parameter
4. If the value is 4 then the process will re-deploy the package and change the environment variable

After setting the variable for what action is being taken the next few steps will depict variable setup, loading the integration services assembly, storing the integration services assembly to avoid typing it every time, creating the connection to the deployed server, and creating the integration services object.

# Variables
$ProjectFilePath = "This is the location of where the ispac is located. For me it is getting the latest in TFS and then found on my C:\"
$ProjectName = "Insert the project name"
$FolderName = "Folder that the package will reside"
$EnvironmentName = "The environment name of the package"
$RPTServerName = "Server being deployed to"

# Load the IntegrationServices Assembly
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null;

# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

Write-Host "Connecting to server ..."

# Create a connection to the server
$sqlConnectionString = "Data Source=$RPTServerName;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection
$catalog = $integrationServices.Catalogs["SSISDB"]

Create the folder, deploy the package, set up the environment variable and parameter, reference parameters (do everything)

if ($step -eq 1)

            {
                if ($catalog.Folders[$FolderName]) {Write-Host "SSIS folder $FolderName already exists"}
                else {
            Write-Host "Creating folder $FolderName ..."
            $folder = New-Object $ISNamespace".CatalogFolder" ($catalog, $FolderName, "Folder description")
            $folder.Create()

            Write-Host "Deploying " $ProjectName " project ..."
             # Read the project file, and deploy it to the folder
             [byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
             $folder.DeployProject($ProjectName, $projectFile)

            Write-Host "Creating environment ..."
            $environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, $EnvironmentName, "Description")
            $environment.Create()

            Write-Host "Adding server variables ..."
            # Adding variable to our environment
            # Constructor args: variable name, type, default value, sensitivity, description
            $environment.Variables.Add(“Env1”, [System.TypeCode]::Int32, 0, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env2”, [System.TypeCode]::Int32, 0, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env3”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env4”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env5”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env6”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env7”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env8”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“DatabaseEnv1”, [System.TypeCode]::String, "DatabaseName1", $false, "Database1")
            $environment.Variables.Add(“DatabaseEnv2”, [System.TypeCode]::String, "DatabaseName2", $false, "Database2")
            $environment.Variables.Add(“DatabaseEnv3”, [System.TypeCode]::String, "DatabaseName3", $false, "Database3")
            $environment.Variables.Add(“Reporting_Deployed_Server”, [System.TypeCode]::String, $RPTServerName, $false, "Reporting_Deployed_Server")
            $environment.Alter()

            Write-Host "Adding environment reference to project ..."

            # making project refer to this environment
            $project = $folder.Projects[$ProjectName]
            $project.References.Add($EnvironmentName)
            $project.Alter()

            Write-Host "Adding reference to variables ..."

            $project.Parameters["Paramater1"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Paramater1")
            $project.Parameters["Paramater2"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Paramater2")
            $project.Parameters["Paramater3"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Paramater3")
            $project.Parameters["Paramater4"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Paramater4")
            $project.Parameters["Paramater5"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Paramater5")
            $project.Parameters["Paramater6"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Paramater6")
            $project.Parameters["Paramater7"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Paramater7")
            $project.Parameters["Paramater8"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Paramater8")
            $project.Parameters["DatabaseParamater1"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParamater1")
            $project.Parameters["DatabaseParamater2"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParamater2")
            $project.Parameters["DatabaseParamater3"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParamater3")
            $project.Parameters["Reporting_Deployed_Server"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Reporting_Deployed_Server")
            $project.Alter()
}
   Write-Host "All done." }

Redeploying the package

 elseif ($step -eq 2) # If you want to just Re-deploy the package
        {

                if (-not $catalog.Folders[$FolderName]) {Write-Host "SSIS folder $FolderName doesn't exists. SSIS folder $FolderName must exist before package can be deployed "}
                else {

    Write-Host "Deploying " $ProjectName " project ..."

    $folder = $catalog.Folders[$FolderName]
    $project = $folder.Projects[$ProjectName]
    # Read the project file, and deploy it to the folder
    [byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
    $folder.DeployProject($ProjectName, $projectFile)

    Write-Host $project.Name "was deployed with"
    Write-Host "Description: " $project.Description
    Write-Host "ProjectID: " $project.ProjectID
    Write-Host "All done."
      }
}

Changing the environment variable/parameter

   elseif ($step -eq 3) # If you want to change the environment variable/parameter
{

if (-not $catalog.Folders[$FolderName]) {Write-Host "SSIS folder $FolderName must exist before add/modify parameters and environment variables "}
                else {

$catalog = $integrationServices.Catalogs["SSISDB"]
$folder = $catalog.Folders[$FolderName]
$project = $folder.Projects[$ProjectName]

#Drop an environment if already exists
Write-Host "Drop Environment Variable ..."
if ($folder.Environments[$EnvironmentName]) { $folder.Environments[$EnvironmentName].Drop() }

#Create an environment
Write-Host "Creating environment ..."
$environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, $EnvironmentName, "Description")
$environment.Create()

Write-Host "Adding server variables ..."
# Adding variable to our environment
# Constructor args: variable name, type, default value, sensitivity, description
            $environment.Variables.Add(“Env1”, [System.TypeCode]::Int32, 0, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env2”, [System.TypeCode]::Int32, 0, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env3”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env4”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env5”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env6”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env7”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env8”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“DatabaseEnv1”, [System.TypeCode]::String, "Reporting_TRS_IN", $false, "DatabaseEnv1")
            $environment.Variables.Add(“DatabaseEnv2”, [System.TypeCode]::String, "Reporting_TRSODS", $false, "DatabaseEnv2")
            $environment.Variables.Add(“DatabaseEnv3”, [System.TypeCode]::String, "ReportingAdmin", $false, "DatabaseEnv3")
            $environment.Variables.Add(“Reporting_Deployed_Server”, [System.TypeCode]::String, $RPTServerName, $false, "Reporting_Deployed_Server")
            $environment.Alter()

Write-Host "Adding environment reference to project ..."

<# making project refer to this environment $project = $folder.Projects[$ProjectName] $project.References.Add($EnvironmentName) $project.Alter()#>

Write-Host "Adding reference to variables ..."

            $project.Parameters["Parameter1"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter1")
            $project.Parameters["Parameter2"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter2")
            $project.Parameters["Parameter3"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter3")
            $project.Parameters["Parameter4"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter4")
            $project.Parameters["Parameter5"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter5")
            $project.Parameters["Parameter6"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter6")
            $project.Parameters["Parameter7"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter7")
            $project.Parameters["Parameter8"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter8")
            $project.Parameters["DatabaseParam1"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParam1")
            $project.Parameters["DatabaseParam2"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParam2")
            $project.Parameters["DatabaseParam3"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParam3")
            $project.Parameters["Reporting_Deployed_Server"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Reporting_Deployed_Server")
            $project.Alter()

Write-Host "All done."
}
}

Redeploy the package and change the environment variable\parameter

 elseif ($step -eq 4) # If you want to Re-deploy the package and change the environment variable/parameter
 {
if (-not $catalog.Folders[$FolderName]) {Write-Host "SSIS folder $FolderName must exist before add/modify parameters and environment variables or deploy package "}
                else {

            $catalog = $integrationServices.Catalogs["SSISDB"]
            $folder = $catalog.Folders[$FolderName]
            $project = $folder.Projects[$ProjectName]

            if ($folder.Environments[$EnvironmentName]) { $folder.Environments[$EnvironmentName].Drop() }

             Write-Host "Deploying " $ProjectName " project ..."
             # Read the project file, and deploy it to the folder
             [byte[]] $projectFile = [System.IO.File]::ReadAllBytes($ProjectFilePath)
             $folder.DeployProject($ProjectName, $projectFile)

            Write-Host "Creating environment ..."
            $environment = New-Object $ISNamespace".EnvironmentInfo" ($folder, $EnvironmentName, "Description")
            $environment.Create()

            Write-Host "Adding server variables ..."
            # Adding variable to our environment
            # Constructor args: variable name, type, default value, sensitivity, description
            $environment.Variables.Add(“Env1”, [System.TypeCode]::Int32, 0, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env2”, [System.TypeCode]::Int32, 0, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env3”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env4”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env5”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env6”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env7”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“Env8”, [System.TypeCode]::Int32, 1, $false, "1=run 0=do not run")
            $environment.Variables.Add(“DatabaseEnv1”, [System.TypeCode]::String, "Reporting_TRS_IN", $false, "DatabaseEnv1")
            $environment.Variables.Add(“DatabaseEnv2”, [System.TypeCode]::String, "Reporting_TRSODS", $false, "DatabaseEnv2")
            $environment.Variables.Add(“DatabaseEnv3”, [System.TypeCode]::String, "ReportingAdmin", $false, "DatabaseEnv3")
            $environment.Variables.Add(“Reporting_Deployed_Server”, [System.TypeCode]::String, $RPTServerName, $false, "Reporting_Deployed_Server")
            $environment.Alter()

            Write-Host "Adding reference to variables ..."
            $project.Parameters["Parameter1"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter1")
            $project.Parameters["Parameter2"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter2")
            $project.Parameters["Parameter3"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter3")
            $project.Parameters["Parameter4"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter4")
            $project.Parameters["Parameter5"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter5")
            $project.Parameters["Parameter6"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter6")
            $project.Parameters["Parameter7"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter7")
            $project.Parameters["Parameter8"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Parameter8")
            $project.Parameters["DatabaseParam1"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParam1")
            $project.Parameters["DatabaseParam2"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParam2")
            $project.Parameters["DatabaseParam3"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "DatabaseParam3")
            $project.Parameters["Reporting_Deployed_Server"].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Referenced, "Reporting_Deployed_Server")
            $project.Alter()
}
   Write-Host "All done." }

Conclusion

With any type of code you find on the internet it’s imperative that you test this on local machines or in a Dev sandbox before thinking of running in a QA, UAT, or Prod environment. What this has done is cut down my deployment times significantly and has helped improve efficiency. I always like a challenge and combing over this project and testing and re-testing proved fruitful in the end. For me it was worth it; hope others can get something out of it as well.

A big thanks to Steven Robinson who was the developer mentioned on this project; Steven is one of the hardest working developers I’ve been around and the communication between the two units played a key roll in achieving in the end success.