PoshCode Archive  Artifact [878945de7a]

Artifact 878945de7afa9d2cd2368ee43892de948873d30ebb00af5e4ddaff682c753a17:

  • File RunSSIS.ps1 — part of check-in [31c36d4caa] at 2018-06-10 14:26:01 on branch trunk — Executes a SQL Server Integrations Services package for both server and file system storage types. Also includes optional processing of external configuration file. (user: Chad Miller size: 6001)

# encoding: utf-8
# api: powershell
# title: RunSSIS
# description: Executes a SQL Server Integrations Services package for both server and file system storage types. Also includes optional processing of external configuration file.
# version: 0.1
# type: script
# author: Chad Miller
# license: CC0
# function: New-ISApplication
# x-poshcode-id: 889
# x-archived: 2017-05-22T02:27:56
# x-published: 2009-02-23T08:34:00
#
#
# ---------------------------------------------------------------------------
### <Script>
### <Author>
### Chad Miller 
### </Author>
### <Description>
### Executes SSIS package for both server and file system storage types.
### </Description>
### <Usage>
###  -------------------------- EXAMPLE 1 --------------------------
### ./RunSSIS.ps1 -path Z002_SQL1\sqlpsx -serverName 'Z002\SQL1'
###
### This command will execute package sqlpsx on the server Z002\SQL1
###
###  -------------------------- EXAMPLE 2 --------------------------
### ./RunSSIS.ps1 -path Z002_SQL1\sqlpsx -serverName Z002\SQL1 -configFile 'C:\SSISConfig\sqlpsx.xml' 
###
### This command will execute the package as in Example 1 and process and configuration file
###
###  -------------------------- EXAMPLE 3 --------------------------
### ./RunSSIS.ps1 -path 'C:\SSIS\sqlpsx.dtsx'
###
### This command will execute the package sqlpsx.dtsx located on the file system
###
###  -------------------------- EXAMPLE 4 --------------------------
### ./RunSSIS.ps1 -path 'C:\SSIS\sqlpsx.dtsx -nolog
###
### This command will execute the package sqlpsx.dtsx located on the file system and skip Windows Event logging
###
### </Usage>
### </Script>
# ---------------------------------------------------------------------------

param($path=$(throw 'path is required.'), $serverName, $configFile, [switch]$nolog)

# Note: SSIS is NOT backwards compatible. At the beginning of the script you’ll need to comment/uncomment the specific assembly
# to load 2005 or 2008. Default of the script is set to 2005 
[reflection.assembly]::Load("Microsoft.SqlServer.ManagedDTS, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") > $null
#[Reflection.Assembly]::LoadFile("C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll") > $null
#[reflection.assembly]::Load("Microsoft.SqlServer.ManagedDTS, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91") > $null
#[Reflection.Assembly]::LoadFile("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SQLServer.ManagedDTS.dll") > $null
#[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ManagedDTS") > $null

$myName = 'RunSSIS.ps1'

#######################
function New-ISApplication
{
   new-object ("Microsoft.SqlServer.Dts.Runtime.Application") 

} #New-ISApplication

#######################
function Test-ISPath
{
    param([string]$path=$(throw 'path is required.'), [string]$serverName=$(throw 'serverName is required.'), [string]$pathType='Any')

    #If serverName contains instance i.e. server\instance, convert to just servername:
    $serverName = $serverName -replace "\\.*"

    #Note: Don't specify instance name

    $app = New-ISApplication

    switch ($pathType)
    {
        'Package' { trap { $false; continue } $app.ExistsOnDtsServer($path,$serverName) }
        'Folder'  { trap { $false; continue } $app.FolderExistsOnDtsServer($path,$serverName) }
        'Any'     { $p=Test-ISPath $path $serverName 'Package'; $f=Test-ISPath $path $serverName 'Folder'; [bool]$($p -bor $f)}
        default { throw 'pathType must be Package, Folder, or Any' }
    }

} #Test-ISPath

#######################
function Get-ISPackage
{
    param([string]$path, [string]$serverName)

    #If serverName contains instance i.e. server\instance, convert to just servername:
    $serverName = $serverName -replace "\\.*"

    $app = New-ISApplication

    #SQL Server Store
    if ($path -and $serverName)
    { 
        if (Test-ISPath $path $serverName 'Package')
        { $app.LoadFromDtsServer($path, $serverName, $null) }
        else
        { Write-Error "Package $path does not exist on server $serverName" }
    }
    #File Store
    elseif ($path -and !$serverName)
    { 
        if (Test-Path -literalPath $path)
        { $app.LoadPackage($path, $null) }
        else
        { Write-Error "Package $path does not exist" }
    }
    else
    { throw 'You must specify a file path or package store path and server name' }
    
} #Get-ISPackage

#######################
#MAIN

Write-Verbose "$myName path:$path serverName:$serverName configFile:$configFile nolog:$nolog.IsPresent"

if (!($nolog.IsPresent))
{ 
    $log = Get-EventLog -List | Where-Object { $_.Log -eq "Application" }
    $log.Source = $myName 
    $log.WriteEntry("Starting:$path",'Information') 
}

$package = Get-ISPackage $path $serverName

if ($package)
{

    if ($configFile)
    { 
        if (test-path -literalPath $configFile)
        { $package.ImportConfigurationFile("$configFile") }
        else
        {
            $err = "Invalid file path. Verify configFile:$configFile"
            if (!($nolog.IsPresent)) { $log.WriteEntry("Error:$path:$err",'Error') }
            throw ($err)
            break
        }
    }

    $package.Execute()
    $err = $package.Errors | foreach { $_.Source.ToString() + ':' + $_.Description.ToString() }

    if ($err)
    { 
        if (!($nolog.IsPresent)) { $log.WriteEntry("Error:$path:$err",'Error') }
        throw ($err)
        break
    }
    else
    {
        if (!($nolog.IsPresent)) { $log.WriteEntry("Completed:$path",'Information') }
    }
}
else
{ 
    $err = "Cannot Load Package. Verify Path:$path and Server:$serverName"
    if (!($nolog.IsPresent)) { $log.WriteEntry("Error:$path:$err",'Error') }
    throw ($err)
    break
}
#MAIN
#######################