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

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?

我正在研究SSIS包。我在现有的ssis包中增加了一个数据流任务。在添加新任务后，我重新编译了包，它没有任何错误。我需要将其部署到开发服务器吗？

3 个解决方案

#1

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.

Visual Studio中的2012 SSIS项目部署模型包含项目参数，项目级连接管理器，包以及您添加到项目中的任何其他内容的文件。

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.

在下图中，您可以看到我有一个名为Lifecycle的解决方案。该解决方案有一个名为Lifecycle的项目。 Lifecycle项目定义了项目级连接管理器ERIADOR和两个SSIS包：Package00.dtsx和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 bin\Development subfolder for your project.

在运行包时，Visual Studio将首先将所有必需的项目元素构建/编译为可部署的量子，称为ispac（发音为eye-ess-pack，而不是ice-pack）。这将在您项目的bin \ Development子文件夹中找到。

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

Lifecycle.ispac是一个zip文件，其中包含以下内容。

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.

这是什么意思？最大的区别在于，您需要部署整个.ispac，而不仅仅是部署更新的软件包。是的，即使您只更换了一个包，也必须重新部署所有内容。这就是人生。

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

您有一个主机选项，但您需要知道的3件事

• where is my ispac
• 我的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.

这可能是您最常见的选择。在SQL Server数据工具SSDT中，您可以在Configuration Manager级别定义要部署的服务器和文件夹。在我的客户端，我有3个配置：Dev，Stage，Production。一旦定义了这些值，它们就会被保存到.dtproj文件中，然后您可以右键单击并从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.

SSDT实际上只是构建了对ISDeploymentWizard.exe的调用，它出于某种原因有32位和64位版本。

• C:\Program Files\Microsoft SQL Server\110\DTS\Binn\ISDeploymentWizard.exe
• C：\ Program Files \ Microsoft SQL Server \ 110 \ DTS \ Binn \ ISDeploymentWizard.exe
• C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\ISDeploymentWizard.exe
• C：\ Program Files（x86）\ Microsoft SQL Server \ 110 \ DTS \ Binn \ ISDeploymentWizard.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.

.ispac扩展名与ISDeploymentWizard相关联，因此双击即可离开。与使用SSDT接口相比，第一个屏幕是新的，但在此之后，它将是要部署的同一组点击。

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".

使用软件包部署模型的2012版本中他们所做的是清单文件可以以自动方式部署。我有一个解决方法，但它应该是一个标准的“事情”。

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

因此，请仔细查看SSDT或GUI部署中的Review选项卡。那不是美女吗？

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!

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

C:\Program Files\Microsoft SQL Server\110\DTS\Binn\ISDeploymentWizard.exe 
/Silent 
/SourcePath:"C:\Dropbox\presentations\SSISDB Lifecycle\Lifecycle\Lifecycle\bin\Development\Lifecycle.ispac" 
/DestinationServer:"localhost\dev2012" 
/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.

您可以通过SQL Server Management Studio，SSMS或命令行sqlcmd.exe将ispac部署到SQL Server。虽然严格要求SQLCMD，但它简化了脚本。

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

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

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

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

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

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

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.

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

USE SSISDB
GO

-- You must be in SQLCMD mode
-- setvar isPacPath "C:\Dropbox\presentations\SSISDB Lifecycle\Lifecycle\Lifecycle\bin\Development\Lifecycle.ispac"
:setvar isPacPath "<isPacFilePath, nvarchar(4000), C:\Dropbox\presentations\SSISDB Lifecycle\Lifecycle\Lifecycle\bin\Development\Lifecycle.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.

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

[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 = "localhost\dev2012"
    )

    $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:\Dropbox\presentations\SSISDB Lifecycle\Lifecycle\Lifecycle\bin\Development\Lifecycle.ispac"
$folderName = "Folder"
$folderName = "SSIS2012"
$folderDescription = "I am a description"
$serverName = "localhost\dev2012"

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

Deploy-Project $isPac $projectName $catalogFolder

#2

Here is an update on deploying a single package in SSIS 2016 (hope this can be useful).

以下是有关在SSIS 2016中部署单个程序包的更新（希望这可能很有用）。

With the release of SQL Server 2016 and SSDT 2015 the issue of single package deployment is now a thing of the past. There is the new Deploy Package option (VS 2015) that comes up for deploying individual packages within a project deployment model

随着SQL Server 2016和SSDT 2015的发布，单包部署的问题现在已经成为过去。有一个新的Deploy Package选项（VS 2015），用于在项目部署模型中部署单个包

With this new feature, you can also deploy multiple packages, by clicking and holding down the control key (Ctrl) and then choosing the packages you want to deploy.

使用此新功能，您还可以通过单击并按住控制键（Ctrl）然后选择要部署的软件包来部署多个软件包。

Besides the Deploy Package option in Visual Studio 2015, there are some other possibilities you may use to deploy packages, like launching ISDeploymentWizard application or doing Command Line Deployment (this one is necessary when SSIS build and deployment is automated or managed as part of Continuous Integration process). You can learn more by navigating to this article: http://www.sqlshack.com/single-package-deployment-in-sql-server-integration-services-2016/

除了Visual Studio 2015中的“部署包”选项之外，您还可以使用其他一些方法来部署包，例如启动ISDeploymentWizard应用程序或执行命令行部署（当SSIS构建和部署自动化或作为持续集成的一部分进行管理时，这是必需的处理）。您可以通过浏览本文了解更多信息：http：//www.sqlshack.com/single-package-deployment-in-sql-server-integration-services-2016/

#3

If you are using Project Model in SSIS 2012, you have to deploy the project every time you make any change in package. You can simply do is :

如果您在SSIS 2012中使用项目模型，则每次对包进行任何更改时都必须部署项目。你可以简单地做到：

• RIGHT Click on Project and Deploy
• 右键单击Project and Deploy