Running an SSIS package using PowerShell.

Without going into the reasons for it, I was asked to create a process for running SSIS packages, both on schedule and on demand, not using Sql Agent.

To do that I created a PowerShell Function that could take not just the usual string parameters for server, project & package names, but also any parameters to be passed at the project or package level. To do that I used hashtable parameters.

The function can be run by simply passing the server, folder, project and package names. However, if there are parameters to be passed to the SSIS package, then named hashtables can be created and passed to the function. eg:

Run-SSISPackage -SSISServer $SsisServer -FolderName $FolderName -ProjectName $ProjectName -PackageName $PackageName -ProjectParams $ProjectParams -PackageParams $PackageParams

Here's a full example:

$SsisServer = 'T01\TEST'
$FolderName = 'Data_Sync'
$ProjectName = 'Reporting'
$PackageName = 'ImportDataInto.dtsx'


$ProjectParams = @{
    BLMCornerstoneCommandConnectionString = 'Data Source=xxxx;Initial Catalog=xxxxx;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'
    BulkInsertUser = '[email protected]'
    ErrorLogFile = 'D:\xxxxx\\'
    MissingClientFileLocation = 'D:\xxxx\\'
    MissingSecurityGroupAccountErrorMessage = 'The provided security group for that account is not in xxxxx.'
    SMTPConnectionManager = 'SmtpServer=xxxxxxx;UseWindowsAuthentication=False;EnableSsl=False;'
    SuiteCRMConnectionString = 'server=xxxxxxx;uid=UserTest;Dsn=Test_DSN32;Driver={MySQL ODBC Unicode 5.3 Driver};database=test;'
    TalendConnectionString = 'server=xxxxxx;uid=user;Dsn=Test_DSN32;Driver={MySQL ODBC Unicode 5.3 Driver};database=xxxxxxx;'
    ToEmailAddresses = '[email protected]'
    UserRole = 'BSA, PA'
    V2CommandConnectionString = 'Data Source=xxxxxxxx;Initial Catalog=xxxxxxxx;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;'
}


$PackageParams = @{
    SQL2005InstanceMasterDBConnectionString = 'Data Source=xxxxxx;Initial Catalog=master;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False'
    V1ConnectionString = 'Data Source=xxxxxxx;Initial Catalog=xxxxxx;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False'
}




Run-SSISPackage `
-SSISServer $SsisServer `
-FolderName $FolderName `
-ProjectName $ProjectName `
-PackageName $PackageName `
-ProjectParams $ProjectParams `
-PackageParams $PackageParams

The Run-SSISPackage function:

Function Run-SSISPackage
{
    [cmdletbinding()]
    Param (
    [Parameter(Mandatory=$True)]
    [ValidateSet('D01\DEV','T01\TEST','P01\PROD','DRP01\DRPROD')]
    [string]$SSISServer ,
    [Parameter(Mandatory=$True)] 
    [string]$FolderName , 
    [Parameter(Mandatory=$True)]
    [string]$ProjectName ,
    [Parameter(Mandatory=$True)]
    [string]$PackageName ,
    [Parameter(Mandatory=$False)]
    [hashtable]$ProjectParams ,
    [Parameter(Mandatory=$False)]
    [hashtable]$PackageParams ,
    [switch]$Test
    )


BEGIN {
    [System.Reflection.Assembly]::Load("Microsoft.SQLServer.Management.IntegrationServices, "+
    "Version=12.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=MSIL") | Out-Null


    $sqlConnectionString = `
    "Data Source=" + $SSISServer + ";Initial Catalog=master;Integrated Security=SSPI;"
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
    $SSISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
    $SSIS = New-Object $SSISNamespace".IntegrationServices" $sqlConnection
    $catalog = $SSIS.Catalogs["SSISDB"]


    $PackageParams
}
PROCESS {


    $ssisProject = $catalog.Folders[$FolderName].Projects[$ProjectName]
    $ssisPackage = $ssisProject.Packages[$PackageName]


   if($ProjectParams) {
       $ProjectParams.GetEnumerator() | %{
        $Name = $_.Key
        $Value = $_.value 
        "Inserting $Name with value $Value"
        $ssisProject.Parameters[$Name].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$Value)
       }
       $ssisProject.Alter()
   }
   
   if($PackageParams) {
        $PackageParams.GetEnumerator() | %{
        $Name = $_.Key
        $Value = $_.value 
        "Inserting $Name with value $Value"
        $ssisPackage.Parameters[$Name].Set([Microsoft.SqlServer.Management.IntegrationServices.ParameterInfo+ParameterValueType]::Literal,$Value)
        }
        $ssisPackage.Alter()
    }
    
    try {
    $JobId = $ssisPackage.Execute("false", $null)
    }catch {
        $error[0] | fl -force
    }
}
END {
    Return $JobId 
}


}