This website requires JavaScript.

从SSIS Catalog中获取SSIS包

我们会碰到把SSIS包丢失的情况 本文介绍三种方法重新创建SSIS项目。前提是你的SSIS包发布到了SSIS Catalog。 Integration Services Catalog ** A. Integration Service Import Project Wizard B. Export in SSMS C. PowerShell to the rescue **

SSIS服务项目导入向导

如果你机器上装了SSDT,并且可以连接SQL Server的Catalog。那么本方法是最简单的。 1) Create a new Integration Services project 与标准的创建方法不同,我们需要选择Integration Services Import Project Wizard Integration Service Import Project Wizard 2) Wizard 向导会有SSIS Catalog项目导出的选项。一步步点下去即可。 Animated GIF of Wizard

从SSMS中导出

如果你无法从VS里面直接连,那么还有一个方法,通过SSMS把SSIS Catalog里面的项目导出成ISPAC文件。 1) Right click your project and choose Export 右击你的项目选择导出,然后放到本地磁盘或者网盘,并以Catalog里面的项目名命名。 Export from SSMS 1b) Integration Service Import Project Wizard 如果你存放的地方,没法用方法A的向导方式处理。那么可以采用下面这个步骤。 2) Extract ISPAC file 如果你保存的文件为xxx.ispac那么把后缀名.ispac改为.ZIP.然后你就可以从里面获得所有文件了。 Unzip the ISPAC file 3) Create new SSIS project 因为以上的方式是包含解决项目文件的,因此你得重新创建一个项目。 项目名称你应该跟Catalog里面的项目名称一样。solution名使用SSIS catalog里面的 项目目录名。 Create new SSIS project in SSDT/Visual Studio 4) Add items from unzipped ISPAC 添加解压缩的ISPAC文件,首先把自动生成的package.dtsx删掉,然后右击项目选择Add-->Existing Item。然后选择你ISPAC的解压目录,除了@Project.manifest[Content_Types].xml全部导入。 如果你导入Project.params文件,他会提示你已经有一个了,此时替换并重新加载即可。 Project.params reload 最后你只要进行版本控制以及检查保护级别就可以了。 Add project/solution to source control

PowerShell

如果你的ispace文件有很多,此时就不易一个个手动操作了,可以使用PowerShell。下载样例

#PowerShell: DownloadIspac.ps1
################################
########## PARAMETERS ##########
################################ 
# Change Server, folder, project and download folder
$SsisServer = "MyServer\SQL2014" # Mandatory
$FolderName = "MyFolder"         # Can be empty to download multiple projects
$ProjectName = "MyProject"       # Can be empty to download multiple projects
$DownloadFolder = "D:\MyIspacs\" # Mandatory
$CreateSubfolders = $true        # Mandatory
$UnzipIspac = $false             # Mandatory
 
 
#################################################
########## DO NOT EDIT BELOW THIS LINE ##########
#################################################
clear
Write-Host "========================================================================================================================================================"
Write-Host "== Used parameters =="
Write-Host "========================================================================================================================================================"
Write-Host "SSIS Server             :" $SsisServer
Write-Host "Folder Name             :" $FolderName
Write-Host "Project Name            :" $ProjectName
Write-Host "Local Download Folder   :" $DownloadFolder
Write-Host "Create Subfolders       :" $CreateSubfolders
Write-Host "Unzip ISPAC (> .NET4.5) :" $UnzipIspac
Write-Host "========================================================================================================================================================"
 
 
##########################################
########## Mandatory parameters ##########
##########################################
if ($SsisServer -eq "")
{
    Throw [System.Exception] "SsisServer parameter is mandatory"
}
if ($DownloadFolder -eq "")
{
    Throw [System.Exception] "DownloadFolder parameter is mandatory"
}
elseif (-not $DownloadFolder.EndsWith("\"))
{
    # Make sure the download path ends with an slash
    # so we can concatenate an subfolder and filename
    $DownloadFolder = $DownloadFolder = "\"
}
 
 
############################
########## SERVER ##########
############################
# Load the Integration Services Assembly
Write-Host "Connecting to server $SsisServer "
$SsisNamespace = "Microsoft.SqlServer.Management.IntegrationServices"
[System.Reflection.Assembly]::LoadWithPartialName($SsisNamespace) | Out-Null;
 
# Create a connection to the server
$SqlConnectionstring = "Data Source=" + $SsisServer + ";Initial Catalog=master;Integrated Security=SSPI;"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnectionstring
 
# Create the Integration Services object
$IntegrationServices = New-Object $SsisNamespace".IntegrationServices" $SqlConnection
 
# Check if connection succeeded
if (-not $IntegrationServices)
{
    Throw [System.Exception] "Failed to connect to server $SsisServer "
}
else
{
    Write-Host "Connected to server" $SsisServer
}
 
 
#############################
########## CATALOG ##########
#############################
# Create object for SSISDB Catalog
$Catalog = $IntegrationServices.Catalogs["SSISDB"]
 
# Check if the SSISDB Catalog exists
if (-not $Catalog)
{
    # Catalog doesn't exists. Different name used?
    Throw [System.Exception] "SSISDB catalog doesn't exist."
}
else
{
    Write-Host "Catalog SSISDB found"
}
 
 
############################
########## FOLDER ##########
############################
if ($FolderName -ne "")
{
    # Create object to the folder
    $Folder = $Catalog.Folders[$FolderName]
    # Check if folder exists
    if (-not $Folder)
    {
        # Folder doesn't exists, so throw error.
        Write-Host "Folder" $FolderName "not found"
        Throw [System.Exception] "Aborting, folder not found"
    }
    else
    {
        Write-Host "Folder" $FolderName "found"
    }
}
 
 
#############################
########## Project ##########
#############################
if ($ProjectName -ne "" -and $FolderName -ne "")
{
    $Project = $Folder.Projects[$ProjectName]
    # Check if project already exists
    if (-not $Project)
    {
        # Project doesn't exists, so throw error.
        Write-Host "Project" $ProjectName "not found"
        Throw [System.Exception] "Aborting, project not found"
    }
    else
    {
        Write-Host "Project" $ProjectName "found"
    }
}
 
 
##############################
########## DOWNLOAD ##########
##############################
Function DownloadIspac
{
    Param($DownloadFolder, $Project, $CreateSubfolders, $UnzipIspac)
    if ($CreateSubfolders)
    {
        $DownloadFolder = ($DownloadFolder + $Project.Parent.Name)
    }
 
    # Create download folder if it doesn't exist
    New-Item -ItemType Directory -Path $DownloadFolder -Force > $null
 
    # Check if new ispac already exists
    if (Test-Path ($DownloadFolder + $Project.Name + ".ispac"))
    {
        Write-Host ("Downloading [" + $Project.Name + ".ispac" + "] to " + $DownloadFolder + " (Warning: replacing existing file)")
    }
    else
    {
        Write-Host ("Downloading [" + $Project.Name + ".ispac" + "] to " + $DownloadFolder)
    }
 
    # Download ispac
    $ISPAC = $Project.GetProjectBytes()
    [System.IO.File]::WriteAllBytes(($DownloadFolder + "\" + $Project.Name + ".ispac"),$ISPAC)
    if ($UnzipIspac)
    {
        # Add reference to compression namespace
        Add-Type -assembly "system.io.compression.filesystem"
 
        # Extract ispac file to temporary location (.NET Framework 4.5) 
        Write-Host ("Unzipping [" + $Project.Name + ".ispac" + "]")
 
        # Delete unzip folder if it already exists
        if (Test-Path ($DownloadFolder + "\" + $Project.Name))
        {
            [System.IO.Directory]::Delete(($DownloadFolder + "\" + $Project.Name), $true)
        }
 
        # Unzip ispac
        [io.compression.zipfile]::ExtractToDirectory(($DownloadFolder + "\" + $Project.Name + ".ispac"), ($DownloadFolder + "\" + $Project.Name))
 
        # Delete ispac
        Write-Host ("Deleting [" + $Project.Name + ".ispac" + "]")
        [System.IO.File]::Delete(($DownloadFolder + "\" + $Project.Name + ".ispac"))
    }
    Write-Host ""
}
 
 
#############################
########## LOOPING ##########
#############################
# Counter for logging purposes
$ProjectCount = 0
 
# Finding projects to download
if ($FolderName -ne "" -and $ProjectName -ne "")
{
    # We have folder and project
    $ProjectCount++
    DownloadIspac $DownloadFolder $Project $CreateSubfolders $UnzipIspac
}
elseif ($FolderName -ne "" -and $ProjectName -eq "")
{
    # We have folder, but no project => loop projects
    foreach ($Project in $Folder.Projects)
    {
        $ProjectCount++
        DownloadIspac $DownloadFolder $Project $CreateSubfolders $UnzipIspac
    }
}
elseif ($FolderName -eq "" -and $ProjectName -ne "")
{
    # We only have a projectname, so search
    # in all folders
    foreach ($Folder in $Catalog.Folders)
    {
        foreach ($Project in $Folder.Projects)
        {
            if ($Project.Name -eq $ProjectName)
            {
                Write-Host "Project" $ProjectName "found in" $Folder.Name
                $ProjectCount++
                DownloadIspac $DownloadFolder $Project $CreateSubfolders $UnzipIspac
            }
        }
    }
}
else
{
    # Download all projects in all folders
    foreach ($Folder in $Catalog.Folders)
    {
        foreach ($Project in $Folder.Projects)
        {
            $ProjectCount++
            DownloadIspac $DownloadFolder $Project $CreateSubfolders $UnzipIspac
        }
    }
}
 
###########################
########## READY ##########
###########################
# Kill connection to SSIS
$IntegrationServices = $null
Write-Host "Finished, total downloads" $ProjectCount

Output Example

0条评论
avatar