如何在 sql server 2012 中部署现有的 SSIS 包?

2021-12-26 00:00:00 deployment tsql sql-server sql-server-2012 ssis

我正在处理 SSIS 包.我向现有的 ssis 包添加了另一个数据流任务.添加新任务后，我重建了包，它成功了，没有任何错误.我需要将它部署到开发服务器吗?

解决方案

背景

Visual Studio 中的，但它应该是一个标准东西".

因此，请仔细查看 SSDT 或 GUI 部署中的审核"选项卡.这不是美女吗?

使用相同的可执行文件 ISDeploymentWizard，我们可以为 .ispac(s) 安装有人值守和无人值守的安装程序.突出显示那里的第二行，复制粘贴，现在您可以进行持续集成！

C:Program FilesMicrosoft SQL Server110DTSBinnISDeploymentWizard.exe/沉默的/SourcePath:"C:DropboxpresentationsSSISDB LifecycleLifecycleLifecycleinDevelopmentLifecycle.ispac"/DestinationServer:"localhostdev2012"/DestinationPath:"/SSISDB/Folder/Lifecycle"

TSQL

您可以通过 SQL Server Management Studio、SSMS 或命令行 sqlcmd.exe 将 ispac 部署到 SQL Server.虽然 SQLCMD 不是必需的，但它简化了脚本.

您必须使用 Windows 帐户来执行此操作，否则您会收到以下错误消息.

<块引用>

使用 SQL Server 身份验证的帐户无法启动该操作.使用使用 Windows 身份验证的帐户开始操作.

此外，您还需要能够对 SSISDB 数据库执行批量操作(以序列化 .ispac)和 ssis_admin/sa 权限.

这里我们使用带有 BULK 选项的 OPENROWSET 将 ispac 读入 varbinary 变量.如果文件夹不存在，我们通过 catalog.create_folder 创建一个文件夹然后使用 catalog.deploy_project 实际部署项目.完成后，我喜欢检查操作消息表以验证事情是否按预期进行.

使用 SSISDB去-- 您必须处于 SQLCMD 模式-- setvar isPacPath "C:DropboxpresentationsSSISDB LifecycleLifecycleLifecycleinDevelopmentLifecycle.ispac":setvar isPacPath ""宣布@folder_name nvarchar(128) = 'TSQLDeploy', @folder_id bigint = NULL, @project_name nvarchar(128) = 'TSQLDeploy', @project_stream varbinary(max), @operation_id bigint = NULL;-- 从源文件中读取 zip (ispac) 数据选择@project_stream = T.stream从(选择*从OPENROWSET(BULK N'$(isPacPath)', SINGLE_BLOB ) AS B) AS T (流);-- 测试目录是否存在如果不存在(选择名称从目录.文件夹 AS CF在哪里CF.name = @folder_name)开始-- 为我们的项目创建文件夹执行 [目录].[create_folder]@文件夹名称，@folder_id 输出；结尾-- 实际部署项目执行 [目录].[deploy_project]@文件夹名称，   @项目名, @project_stream，@operation_id 输出；-- 检查是否有问题选择哦.*从catalog.operation_messages AS OM在哪里OM.operation_message_id = @operation_id;

你的妈妈

如，您的托管对象模型提供了一个用于部署包的 .NET 接口.这是用于部署 ispac 以及创建文件夹的 PowerShell 方法，因为 ISDeploymentWizard 不支持该选项.

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") |出空#this 允许显示调试消息$DebugPreference = "继续"# 检索 2012 Integration Services CatalogFolder 对象# 如果没有找到就创建一个函数 Get-CatalogFolder{参数([字符串] $folderName, [字符串] $folderDescription, [string] $serverName = "localhostdev2012")$connectionString = [String]::Format("Data Source={0};Initial Catalog=msdb;Integrated Security=SSPI;", $serverName)$connection = 新对象 System.Data.SqlClient.SqlConnection($connectionString)$integrationServices = 新对象 Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices($connection)# 一个，唯一的SSISDB目录$catalog = $integrationServices.Catalogs["SSISDB"]$catalogFolder = $catalog.Folders[$folderName]if (-not $catalogFolder){Write-Debug([System.string]::Format("Creating folder {0}", $folderName))$catalogFolder = 新对象 Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder($catalog, $folderName, $folderDescription)$catalogFolder.Create()}返回 $catalogFolder}# 部署一个ispac文件到SSISDB目录中功能部署-项目{参数([字符串] $projectPath, [字符串] $projectName, $目录文件夹)# 测试以确保文件存在if (-not $projectPath -or -not (Test-Path $projectPath)){Write-Debug("找不到文件 $projectPath")返回}写调试($catalogFolder.Name)写调试(部署 $projectPath")# 将数据读入字节数组[byte[]] $projectStream = [System.IO.File]::ReadAllBytes($projectPath)# $ProjectName 必须与 .ispac 文件中的值匹配# 否则你会看到# 部署项目失败.修复问题，稍后重试.:指定的项目名称，test，与部署文件中的项目名称不匹配.$projectName = "生命周期"$project = $catalogFolder.DeployProject($projectName, $projectStream)}$isPac = "C:DropboxpresentationsSSISDB LifecycleLifecycleLifecycleinDevelopmentLifecycle.ispac"$folderName = "文件夹"$folderName = "SSIS2012"$folderDescription = "我是描述"$serverName = "localhostdev2012"$catalogFolder = Get-CatalogFolder $folderName $folderDescription $serverName部署项目 $isPac $projectName $catalogFolder

I am working on SSIS Package .I added one more data flow task to existing ssis package.After complition of adding new task i rebuilded the Package it was suceed with out any errors . Do i need to deploy it to Development server?

解决方案

Background

The 2012 SSIS Project Deployment model in Visual Studio contains a file for project parameters, project level connection managers, packages and anything else you've added to the project.

In the following picture, you can see that I have a Solution named Lifecycle. That solution has a project named Lifecycle. The Lifecycle project has a Project Level Connection Manager ERIADOR defined and two SSIS packages: Package00.dtsx and Package01.dtsx.

When you run a package, behind the scenes Visual Studio will first build/compile all the required project elements into a deployable quantum called an ispac (pronounced eye-ess-pack, not ice-pack). This will be found in the binDevelopment subfolder for your project.

Lifecycle.ispac is a zip filed with the following contents.

What's all this mean? The biggest difference is that instead of just deploying an updated package, you'll need to deploy the whole .ispac. Yes, you really have to redeploy everything even though you only changed one package. Such is life.

How do I deploy packages using the SSIS Project Deployment model?

You have a host options available to you but at the 3 things you will need to know are

where is my ispac
what server am I deploying to
what folder does this project to

SSDT

This will probably be your most common option in the beginning. Within SQL Server Data Tools, SSDT, you have the ability to define at the Configuration Manager level what server and what folder things are deployed to. At my client, I have 3 configurations: Dev, Stage, Production. Once you define those values, they get saved into the .dtproj file and you can then right click and deploy to your heart's content from visual studio.

ISDeploymentWizard - GUI flavor

SSDT is really just building the call to the ISDeploymentWizard.exe which comes in 32 and 64 bit flavors for some reason.

C:Program FilesMicrosoft SQL Server110DTSBinnISDeploymentWizard.exe
C:Program Files (x86)Microsoft SQL Server110DTSBinnISDeploymentWizard.exe

An .ispac extension is associated to the ISDeploymentWizard so double click and away you go. The first screen is new compared to using the SSDT interface but after that, it will be the same set of clicks to deploy.

ISDeploymentWizard - command line flavor

What they got right with the 2012 release that sucked with the package deployment model was that the manifest file can be deployed in an automated fashion. I had a workaround but it should have been a standard "thing".

So look carefully at the Review tab from either the SSDT or GUI deploy. Isn't that a beauty?

Using the same executable, ISDeploymentWizard, we can have both an attended and unattended installer for our .ispac(s). Highlight the second line there, copy paste and now you can have continuous integration!

C:Program FilesMicrosoft SQL Server110DTSBinnISDeploymentWizard.exe 
/Silent 
/SourcePath:"C:DropboxpresentationsSSISDB LifecycleLifecycleLifecycleinDevelopmentLifecycle.ispac" 
/DestinationServer:"localhostdev2012" 
/DestinationPath:"/SSISDB/Folder/Lifecycle"

TSQL

You can deploy an ispac to SQL Server through SQL Server Management Studio, SSMS, or through the command line, sqlcmd.exe. While SQLCMD is not strictly required, it simplifies the script.

You must use a windows account to perform this operation though otherwise you'll receive the following error message.

The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.

Furthermore, you'll need the ability to perform bulk operations (to serialize the .ispac) and ssis_admin/sa rights to the SSISDB database.

Here we use the OPENROWSET with the BULK option to read the ispac into a varbinary variable. We create a folder via catalog.create_folder if it doesn't already exist and then actually deploy the project with catalog.deploy_project. Once done, I like to check the operations messages table to verify things went as expected.

USE SSISDB
GO

-- You must be in SQLCMD mode
-- setvar isPacPath "C:DropboxpresentationsSSISDB LifecycleLifecycleLifecycleinDevelopmentLifecycle.ispac"
:setvar isPacPath "<isPacFilePath, nvarchar(4000), C:DropboxpresentationsSSISDB LifecycleLifecycleLifecycleinDevelopmentLifecycle.ispac>"

DECLARE
    @folder_name nvarchar(128) = 'TSQLDeploy'
,   @folder_id bigint = NULL
,   @project_name nvarchar(128) = 'TSQLDeploy'
,   @project_stream varbinary(max)
,   @operation_id bigint = NULL;

-- Read the zip (ispac) data in from the source file
SELECT
    @project_stream = T.stream
FROM
(
    SELECT 
        *
    FROM 
        OPENROWSET(BULK N'$(isPacPath)', SINGLE_BLOB ) AS B
) AS T (stream);

-- Test for catalog existences
IF NOT EXISTS
(
    SELECT
        CF.name
    FROM
        catalog.folders AS CF
    WHERE
        CF.name = @folder_name
)
BEGIN
    -- Create the folder for our project
    EXECUTE [catalog].[create_folder] 
        @folder_name
    ,   @folder_id OUTPUT;
END

-- Actually deploy the project
EXECUTE [catalog].[deploy_project] 
    @folder_name
,   @project_name
,   @project_stream
,   @operation_id OUTPUT;

-- Check to see if something went awry
SELECT
    OM.* 
FROM
    catalog.operation_messages AS OM
WHERE
    OM.operation_message_id = @operation_id;

Your MOM

As in, your Managed Object Model provides a .NET interface for deploying packages. This is a PowerShell approach for deploying an ispac along with creating the folder as that is an option the ISDeploymentWizard does not support.

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null

#this allows the debug messages to be shown
$DebugPreference = "Continue"

# Retrieves a 2012 Integration Services CatalogFolder object
# Creates one if not found
Function Get-CatalogFolder
{
    param
    (
        [string] $folderName
    ,   [string] $folderDescription
    ,   [string] $serverName = "localhostdev2012"
    )

    $connectionString = [String]::Format("Data Source={0};Initial Catalog=msdb;Integrated Security=SSPI;", $serverName)

    $connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)

    $integrationServices = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices($connection)
    # The one, the only SSISDB catalog
    $catalog = $integrationServices.Catalogs["SSISDB"]

    $catalogFolder = $catalog.Folders[$folderName]

    if (-not $catalogFolder)
    {
        Write-Debug([System.string]::Format("Creating folder {0}", $folderName))
        $catalogFolder = New-Object Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder($catalog, $folderName, $folderDescription)
        $catalogFolder.Create()
    }

    return $catalogFolder
}

# Deploy an ispac file into the SSISDB catalog
Function Deploy-Project
{
    param
    (
        [string] $projectPath
    ,   [string] $projectName
    ,   $catalogFolder
    )

    # test to ensure file exists
    if (-not $projectPath -or  -not (Test-Path $projectPath))
    {
        Write-Debug("File not found $projectPath")
        return
    }

    Write-Debug($catalogFolder.Name)
    Write-Debug("Deploying $projectPath")

    # read the data into a byte array
    [byte[]] $projectStream = [System.IO.File]::ReadAllBytes($projectPath)

    # $ProjectName MUST match the value in the .ispac file
    # else you will see 
    # Failed to deploy the project. Fix the problems and try again later.:The specified project name, test, does not match the project name in the deployment file.
    $projectName = "Lifecycle"

    $project = $catalogFolder.DeployProject($projectName, $projectStream)
}




$isPac = "C:DropboxpresentationsSSISDB LifecycleLifecycleLifecycleinDevelopmentLifecycle.ispac"
$folderName = "Folder"
$folderName = "SSIS2012"
$folderDescription = "I am a description"
$serverName = "localhostdev2012"

$catalogFolder = Get-CatalogFolder $folderName $folderDescription $serverName

Deploy-Project $isPac $projectName $catalogFolder

相关文章