Category: SSIS

Continuing: Automate SSIS 2012 Project Deployment and Configuration Management using PowerShell – Part 2

Download SSISProject containing:

1. DeploymentDemo SSIS Project
2. SimpleSSISDeploy.ps1

Step 3: Execute SimpleSSISDeploy.ps1

Last and definitely NOT the least, where all the magic happens – SimpleSSISDeploy.ps1.
I put comments where needed, and you can download a zip of all the components, as well as a sample SSIS project at the bottom of this post. 🙂

param ([string]$ConfigurationsPath,
       [string]$IspacPath, 
       [string]$ParametersXml, 
       [string]$Environment)
       
# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

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

       
function Main()
{

    # Input validation
    if (-Not(Test-Path($ParametersXml)))
    {
        throw New-Object System.ArgumentException "Parameters.xml not found"
        return
    }
    
    #Get configurations from *.dtproj.user
    [xml]$configFile =  Get-Content $ConfigurationsPath
    $configurationsNode = $configFile.SelectNodes("/DataTransformationsUserConfiguration/Configurations/Configuration")
    
    # Get configuration for $Environment parameter
    $configurationsNode | % {
        if ($_.Name -eq $Environment)
        {
            $serverName = $_.Options.ServerName
            $pathOnServer = $_.Options.PathOnServer
        }
    }   
    
    if ([string]::IsNullOrEmpty($serverName) -or [string]::IsNullOrEmpty($pathOnServer) -or 
        $serverName -eq $null -or $pathOnServer -eq $null )
    {
        throw New-Object System.ArgumentException "Could not connect to Server: $serverName. Does it really exist?"
        return
    }   
    
    # Get catalog, folder and project name from $pathOnServer
    $catalogConnectionString = "Data Source=" + $serverName + ";Initial Catalog=master;Integrated Security=SSPI;"
    $path = $pathOnServer.Split("/", 4)
    $catalogName = $path[1]
    $folderName = $path[2]
    $projectName = $path[3]
    
    if ([string]::IsNullOrEmpty($catalogName) -or 
        [string]::IsNullOrEmpty($folderName) -or 
        [string]::IsNullOrEmpty($projectName))
    {
        throw New-Object System.ArgumentException "Check that $Environment build configuration is set correctly in the SSIS project."
        return
    }
    
    # Connect to the SSIS Server
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $catalogConnectionString
    $integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection
    
    if ($integrationServices -eq $null)
    {
        Write-Host "Unable to connect to Integration Services Catalog."
        return
    }
    
    # Get the existing catalog if it exists
    if ($integrationServices.Catalogs.Contains($catalogName)) 
    {
        Write-Host "$catalogName catalog found"
        $catalog = $integrationServices.Catalogs[$catalogName]        
    }
    else
    {
        Write-Host "Could not find "$catalogName" Catalog. Are you sure you have the correct name?"
        return
    }
    
    # Get catalog folder
    if ($catalog.Folders.Contains($folderName))
    {
        Write-Host "$folderName catalog folder found"
        $folder = $catalog.Folders[$folderName]
    }
    else
    {
        Write-Host "Could not find $folderName catalog folder. You are almost there."
        return
    }
    
    # Read the project file, and deploy it to the folder
    [byte[]] $projectFile = [System.IO.File]::ReadAllBytes($IspacPath)
    $project = $folder.DeployProject($projectName, $projectFile)
    
    # Get project
    if ($folder.Projects.Contains($projectName)) 
    {
        Write-Host "$projectName project found"
        $project = $folder.Projects[$projectName]
        
    } else {
        Write-Host "$projectName project not found. Sorry :("
        return 
    }
    
    # Get function parameters from file
    Write-Host "Reading from Parameters.xml"    
    [xml]$file = Get-Content $ParametersXml
    
    Update-Parameters $project $file 
} 

function Update-Parameters($project, $file)
{
    # Update Project Parameters
    $projectParameters = $file.SelectNodes("/SSIS/" + $Environment + "/ProjectParameters/Parameter")

    $projectParameters | % {
        
        $parameter = $_.Name
        if ($parameter -eq $null)
        {
            continue
        }
        
        if ($project.Parameters.Contains($parameter))
        {
            Write-Host "$parameter project parameter found"
            $project.Parameters[$_.Name].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$_.InnerText)
            Write-Host "$parameter project parameter value updated"
        }
        else
        {
             Write-Host "$parameter project parameter NOT FOUND"
        }
    }
    
    Write-Host "Updating parameters of" $project.Name "project successful"

    # Update Package Parameters
    $packages = $file.SelectNodes("/SSIS/" + $Environment + "/Packages/Package")

    $packages | % {
        
        $packageName = $_.Name
        $parameters = $_.Parameters.ChildNodes
        
        if ($project.Packages.Contains($packageName))
        {
                Write-Host "$packageName package found"
                $ssisPackage = $project.Packages[$packageName]
                
                foreach ($param in $parameters){
                
                    $paramName = $param.Name
                    $paramValue = $param.InnerText
                    
                    if ($ssisPackage.Parameters.Contains($paramName))
                    {
                        Write-Host "$paramName package parameter found"
                        $ssisPackage.Parameters[$paramName].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$paramValue)
                        Write-Host "$paramName package parameter value updated"
                    }
                    else
                    {
                        Write-Host "$paramName package parameter NOT FOUND"
                    }
                } 
                Write-Host "Updating parameters of $packageName package successful"
                $ssisPackage.Alter() 
                         
        }
        else
        {
            Write-Host "$packageName package NOT FOUND"
        }
        
    }

    $project.Alter()
}

Main

Sample execution code:

.\SimpleSSISDeploy.ps1 -ConfigurationsPath "F:\PROJECTS\SSISProject\DeploymentDemo\DeploymentDemo\DeploymentDemo.dtproj.user" -IspacPath "F:\PROJECTS\SSISProject\DeploymentDemo\DeploymentDemo\bin\Development\DeploymentDemo.ispac" -ParametersXml "F:\PROJECTS\SSISProject\DeploymentDemo\DeploymentDemo\Parameters.xml" -Environment "Development"

And that concludes my solution to an automated SSIS Deployment and configuration management. Feel free to comment if anything is not clear. 🙂