Automation · Continuous Integration · SQL Server · SSIS · Technology

Automate SSIS 2012 Project Deployment and Configuration Management using PowerShell – Part 3

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. 🙂

2 thoughts on “Automate SSIS 2012 Project Deployment and Configuration Management using PowerShell – Part 3

  1. Hello Kababayan thank you for sharing. I’m new to SSIS and I developed a “parameterized” SSIS package, the goal is to have the parameter for the data source and destination variables in a single package so that in the future if there are new sources or destinations it won’t require any rebuild. It was successfully deployed on the SSIS Catalog and I created several environments to pass the parameter values. The problem now is that, we need to automate the manual configuration process and utilizes the SQL Agent to schedule the job. In your deployment solution, did you use the SQL Agent or the Windows Task Scheduler? What are your thoughts? I am trying to re-create your solution. Salamat 😉

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s