SsisPackageExport

1.0.0.1

SsisPackageExport.psm1

#
# SsisPackageExport.psm1
#

[Void] [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") ;
[Void] [Reflection.Assembly]::LoadWithPartialName('System.IO.Compression.FileSystem') ;
[Void] [Reflection.Assembly]::LoadWithPartialName('System.IO.Compression');

# THIS ONLY WORKS FOR 32-bit POWERSHELL !
[Void] [Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.Dts.Runtime');
[Void] [Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.ManagedDTS')
# THIS ONLY WORKS FOR 32-bit POWERSHELL !

#Set-StrictMode -Version Latest


function Test-ServerNameCharacters {
    [CmdletBinding()]
    [OutputType([bool])]

    param (

    [parameter(Mandatory=$true,position=0)]
    [string] $SqlServer
    )
    [bool] $success = $false;

    if (    $SqlServer -imatch "^[A-Z0-9-\(\)\[\].\\]+$" `
       -and $SqlServer -inotmatch "^.*\\.*\\.*$" 
        ) 
        { $success = $true }
    return $success;
}

function Get-SSISPackageProjectItemFromByteStream {

[CmdletBinding()]
[OutputType([String])]

param(

    [parameter(Mandatory=$true,position=0)]
    [string] $PackageName,

    [parameter(Mandatory=$true,position=1)]
    [byte[]] $ProjectByteStream

)
    [bool]     $success  = $false;
    [string]   $contents = "" ;

    try
    {
        $stream     = new-object System.IO.MemoryStream(,$ProjectByteStream);
        $zip        = New-Object System.IO.Compression.DeflateStream($stream,[System.IO.Compression.CompressionMode]::Decompress);
        $ZipArchive = New-Object System.IO.Compression.ZipArchive($zip.BaseStream);

        $file       = $ZipArchive.GetEntry($packageName);
        $reader     = $file.Open();
        [byte[]]      $bytes = new-object byte[]($file.Length);
        $x =$reader.Read($bytes,0,$file.Length);
        $contents   = [System.Text.Encoding]::ASCII.GetString($bytes);
        $success    = $true;
    }
    catch {
        Write-Host $_;
    }
    finally
    {
    }
    if ( ! $success) {
        $contents = $null;
    }
    $contents
}

<#
.SYNOPSIS
    Extracts Folders, Projects, Packages and Project Parameters from a SSIS Catalog.
.DESCRIPTION
    Extracts Folders, Projects, Packages and Project Parameters from a SSIS Catalog to a file system folder.
    It creates file-system folders for the SSIS Folder and projects within SSIS Folders. They do not need to
    exists beforehand. It extracts packages as textual *.dtsx files and project parameters as the text file
    Project.params; project connections are also extracted.
    Environment variables are not extracted as there is no natural textual representation, ie
    one which equates to a Visual Studio Integration Services project item.
.EXAMPLE
    Copy-SSISObjectsToFileSystem . t:\temp
    Copy the whole catalog to the temp folder on the t:\ drive.
.EXAMPLE
    Copy-SSISObjectsToFileSystem . t:\temp TestPackages
    Copy the contents of the folder TestPackages to temp folder on the t:\ drive.
.EXAMPLE
    Copy-SSISObjectsToFileSystem . t:\temp TestPackages
    Copy the projects in the folder TestPackages to temp folder on the t:\ drive.
    Create sub-folders for each of the folders in the Integration Services catalog.
.EXAMPLE
    Copy-SSISObjectsToFileSystem . t:\temp TestPackages TestProjectForLocalDeployTestData
    Copy the project TestProjectForLocalDeployTestData under the folder TestPackages to temp folder on the t:\ drive.
    Does not create a sub-folder TestPackages in temp.
.EXAMPLE
    Copy-SSISObjectsToFileSystem . t:\temp TestPackages TestProjectForLocalDeployTestData TestPackage.dtsx
    Copy just the package TestPackage.dtsx in the project TestProjectForLocalDeployTestData under the folder TestPackages to temp folder on the t:\ drive.
    Does not create a sub-folder TestPackages in temp.
.PARAMETER SqlServer
    The name of the Integration Server instance. Mandatory.
.PARAMETER OutputFolder
    The base pathname for the extracted files. Mandatory.
.PARAMETER Folder
    The base Integration Services folder to extract. Optional.
.PARAMETER Project
    The Project in the Integration Services folder to extract. Optional. Requires Folder.
.PARAMETER Project
    The Package in the project in Integration Services folder to extract. Optional. Requires Folder and Project.
#>
function Copy-SSISObjectsToFileSystem {
    [CmdletBinding()]
    [OutputType([void])]

    param (
        [parameter(Mandatory=$true,position=0)]
        [string] $SqlServer,
        [parameter(Mandatory=$true,position=1)]
        [string] $OutputFolder,
        [parameter(position=2)]
        [string] $Folder,
        [parameter(position=3)]
        [string] $Project,
        [parameter(position=4)]
        [string] $Package
    )

    if ((Test-ServerNameCharacters $SqlServer) -ne $true) { throw "Invalid Server Name Format" }
       [string] $sqlConnectionString = "Data Source=${SqlServer}; Initial Catalog=SSISDB; Integrated Security=SSPI";

    [bool] $success  = $false;
    [byte[]]$results = @();

    # Extract Specific package
    if ($Package) {
        [byte[]] $bytes       = Get-SSISProjectByteStream $sqlConnectionString $Folder $Project
        [String] $packageText = Get-SSISPackageProjectItemFromByteStream $Package $bytes
        Set-Content "${OutputFolder}\${Package}" $packageText
    }
    # Else extract all packages for a Specific pproject
    elseif ($Project) {
        [byte[]]  $bytes        = Get-SSISProjectByteStream $sqlConnectionString $Folder $Project
        [string[]]$packageNames = Get-SSISISPacEntryNamesFromByteStream $bytes | where {$_ -imatch ".dtsx$" -or $_ -imatch "^Project.params$" -or $_ -imatch ".conmgr$"}  ;
        foreach ($Package in $packageNames){
            [String] $packageText = Get-SSISPackageProjectItemFromByteStream $Package $bytes
            Set-Content "${OutputFolder}\${Package}" $packageText
        }
    }
    elseif ($Folder) {
        $x=New-Item "${OutputFolder}\${Folder}" -type directory -force | Out-Null
        foreach ( $Project in ( Get-SSISProjectsFromFolder $sqlConnectionString $Folder ) ) {
            $x=New-Item "${OutputFolder}\${Folder}\${Project}" -type directory -force | Out-Null
            [byte[]]  $bytes        = Get-SSISProjectByteStream $sqlConnectionString $Folder $Project
            [string[]]$packageNames = Get-SSISISPacEntryNamesFromByteStream $bytes | where {$_ -imatch ".dtsx$" -or $_ -imatch "^Project.params$" -or $_ -imatch ".conmgr$"}  ;
            foreach ($Package in $packageNames){
                [String] $packageText = Get-SSISPackageProjectItemFromByteStream $Package $bytes
                 Set-Content "${OutputFolder}\${Folder}\${Project}\${Package}" $packageText
# if ($Package -eq "Project.params") {
# Set-Content "${OutputFolder}\${Folder}\${Package}" $packageText
# }
# else {
# Set-Content "${OutputFolder}\${Folder}\${Project}\${Package}" $packageText
# }
            }
        }
    }
    # Else dump everything
    else {
        foreach ( $Folder in ( Get-SSISFoldersFromCatalog $sqlConnectionString ) ) {
            $x=New-Item "${OutputFolder}\${Folder}" -type directory -force | Out-Null
            foreach ( $Project in Get-SSISProjectsFromFolder $sqlConnectionString $Folder ) {
                $x=New-Item "${OutputFolder}\${Folder}\${Project}" -type directory -force | Out-Null
                [byte[]]   $bytes        = Get-SSISProjectByteStream $sqlConnectionString $Folder $Project
                [string[]] $packageNames = Get-SSISISPacEntryNamesFromByteStream $bytes | where {$_ -imatch ".dtsx$" -or $_ -imatch "^Project.params$" -or $_ -imatch ".conmgr$"}  ;

                foreach ($Package in $packageNames){
                    [String] $packageText = Get-SSISPackageProjectItemFromByteStream $Package $bytes
                     Set-Content "${OutputFolder}\${Folder}\${Project}\${Package}" $packageText
# if ($Package -eq "Project.params") {
# Set-Content "${OutputFolder}\${Folder}\${Package}" $packageText
# }
# else {
# Set-Content "${OutputFolder}\${Folder}\${Project}\${Package}" $packageText
# }
                }
            }
        }
    }
}

function Get-SSISISPackageObjectFromDTSXFileData {
    [CmdletBinding()]
    [OutputType([string[]])]
    param (
        [parameter(Mandatory=$true,position=0)]
        [String] $DTSXContents
    )

    [bool]     $success  = $false;
    $pkg       = $null;
    try
    {
        $pkg        = New-Object 'Microsoft.SqlServer.Dts.Runtime.Package';
        $pkg.LoadFromXML($DTSXContents,$null)
        $success    = $true;
    }
    catch {
        Write-Host $_;
    }
    finally
    {
    }
    if ( $success) {
        return $pkg;
    }
   $null;
}

function Get-SSISISPacEntryNamesFromByteStream {
    [CmdletBinding()]
    [OutputType([string[]])]
    param (
        [parameter(Mandatory=$true,position=0)]
        [byte[]] $ProjectByteStream
    )

    [bool]     $success  = $false;
    [string[]] $contents = @();

    try
    {
        $stream     = new-object System.IO.MemoryStream(,$ProjectByteStream);
        $zip        = New-Object System.IO.Compression.DeflateStream($stream,[System.IO.Compression.CompressionMode]::Decompress);
        $ZipArchive = New-Object System.IO.Compression.ZipArchive($zip.BaseStream);
        $contents   = $ZipArchive.Entries.Name;
        $success    = $true;
    }
    catch {
        Write-Host $_;
    }
    finally
    {
    }
    if ( $success) {
        return $contents;
    }
   $null;
}

function Get-SSISProjectByteStream {
    [CmdletBinding()]
    [OutputType([byte[]])]
    param (
        [parameter(Mandatory=$true,position=0)]
        [string] $sqlConnectionString,
        [parameter(Mandatory=$true,position=1)]
        [string] $Folder,
        [parameter(Mandatory=$true,position=2)]
        [string] $Project
    )

    [bool] $success = $false;
    [byte[]]$results = @();

    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection
    $sqlConnection.ConnectionString = $sqlConnectionString;

    if ($sqlConnection.State -eq "Closed")
    {
        $sqlConnection.Open()
    }

    try
    {
        $cmd             = New-Object System.Data.SqlClient.SqlCommand
        $cmd.CommandType = "StoredProcedure"
        $cmd.connection  = $sqlConnection;
        $cmd.CommandText = "SSISDB.Catalog.get_project"
        $cmd.Parameters.Add("@folder_name", $Folder)   | out-null;
        $cmd.Parameters.Add("@project_name", $Project) | out-null;
        $results = $cmd.ExecuteScalar();
        $success = $true;
    }
    catch {}
    finally
    {
        if ($sqlConnection.State -eq "Open")
        {
            $sqlConnection.Close()
        }
    }
    if ( $success) {
        return $results;
    }
   $null;
}

function Get-SSISPackageProjectItemFromByteStream {

[CmdletBinding()]
[OutputType([String])]

param(

    [parameter(Mandatory=$true,position=0)]
    [string] $PackageName,

    [parameter(Mandatory=$true,position=1)]
    [byte[]] $ProjectByteStream

)
    [bool]     $success  = $false;
    [string]   $contents = "" ;

    try
    {
        $stream     = new-object System.IO.MemoryStream(,$ProjectByteStream);
        $zip        = New-Object System.IO.Compression.DeflateStream($stream,[System.IO.Compression.CompressionMode]::Decompress);
        $ZipArchive = New-Object System.IO.Compression.ZipArchive($zip.BaseStream);

        $file       = $ZipArchive.GetEntry($packageName);
        $reader     = $file.Open();
        [byte[]]      $bytes = new-object byte[]($file.Length);
        $x =$reader.Read($bytes,0,$file.Length);
        $contents   = [System.Text.Encoding]::ASCII.GetString($bytes);
        $success    = $true;
    }
    catch {
        Write-Host $_;
    }
    finally
    {
    }
    if ( ! $success) {
        $contents = $null;
    }
    $contents
}

function Get-SSISFoldersFromCatalog {

[CmdletBinding()]
[OutputType([String[]])]

param(
    [parameter(Mandatory=$true,position=0)]
    [string] $sqlConnectionString
)


$sqlConnection            = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
$ISNamespace            = "Microsoft.SqlServer.Management.IntegrationServices"
$ssis                    = New-Object $ISNamespace".IntegrationServices" $sqlConnection
$cat                    = $ssis.Catalogs["SSISDB"]
$cat.Folders.Name;
}

function Get-SSISProjectsFromFolder {

[CmdletBinding()]
[OutputType([String[]])]

param(
    [parameter(Mandatory=$true,position=0)]
    [string] $SqlConnectionString,
    [parameter(Mandatory=$true,position=1)]
    [string] $Folder
)


$sqlConnection            = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString
$ISNamespace            = "Microsoft.SqlServer.Management.IntegrationServices"
$ssis                    = New-Object $ISNamespace".IntegrationServices" $sqlConnection
$cat                    = $ssis.Catalogs["SSISDB"]
#$cat.Folders.Projects.Name | %{ where $_.Parent.Name -eq $Folder };
$cat.Folders.Projects | where { $_.Parent.Name -eq $Folder } | %{ $_.Name }
}

#Export-ModuleMember -Function Test-ServerNameCharacters, Get-SSISPackageFromCatalog, Get-SSISPackageProjectItemFromByteStream ,Get-SSISProjectByteStream, Get-SSISISPacEntryNamesFromByteStream, Get-SSISPackageProjectItemFromByteStream, Copy-SSISObjectsToFileSystem, Get-SSISISPackageObjectFromDTSXFileData,Get-SSISFoldersFromCatalog, Get-SSISProjectsFromFolder