如何在sql server 2012中部署现有的SSIS包？ [英] How to deploy a existing SSIS Package in sql server 2012?

问题描述

我正在使用SSIS软件包。我向现有的ssis软件包添加了一个数据流任务。在添加新任务之后，我重新构建了该软件包，从而导致任何错误。
我需要将其部署到开发服务器？

解决方案

背景

Visual Studio中的，但它应该是标准的东西。

所以请仔细阅读SSDT或GUI部署的审阅选项卡。这不是一个美丽吗？

使用相同的可执行文件ISDeploymentWizard，我们可以同时拥有.ispac（s）的有挂载和无人值守安装程序。突出显示第二行，复制粘贴，现在您可以持续整合！

  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 /文件夹/生命周期
  pre> 
 
 
 TSQL 
 
 
 
您可以通过SQL Server Management Studio，SSMS或通过命令行，sqlcmd.exe。虽然不严格要求 SQLCMD ，但它简化了脚本。 p> 
 
 
您必须使用Windows帐户执行此操作，否则您将收到以下错误消息。
 
 
该操作无法由使用SQL Server身份验证的帐户启动。使用Windows身份验证的帐户开始操作。
 
 
此外，您需要执行批量操作（序列化.ispac）和SSISDB数据库的ssis_admin / sa权限。
 
 
 
这里我们使用带有BULK选项的OPENROWSET将ispac读入varbinary变量。我们通过 catalog.create_folder 创建一个文件夹，如果它还没有存在，然后实际使用 catalog.deploy_project 部署项目。一旦完成，我喜欢检查操作信息表，以确认事情按预期方式进行。
 使用SSISDB 
 GO 
 
  - 您必须在SQLCMD模式
  -  setvar isPacPathC：\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; 
 
  - 从源文件中读取zip（ispac）数据
 SELECT 
 @project_stream = T.stream 
 FROM 
（
 SELECT 
 * 
 FROM 
 OPENROWSET（BULK N'$（isPacPath）'，SINGLE_BLOB）AS B 
）AS T（stream）; 
 
  - 测试目录存在
如果不存在
（
 SELECT 
 CF.name 
 FROM 
 catalog.folders AS CF 
 WHERE 
 CF.name = @folder_name 
）
 BEGIN 
  - 为我们的项目创建文件夹
 EXECUTE [catalog]。[create_folder ] 
 @folder_name 
，@folder_id OUTPUT; 
 END 
 
  - 实际部署项目
 EXECUTE [catalog]。[deploy_project] 
 @folder_name 
，@project_name 
，@ project_stream 
，@operation_id OUTPUT; 
 
  - 检查是否有事情发生错误
 SELECT 
 OM。* 
 FROM 
 catalog.operation_messages AS OM 
 WHERE 
 OM.operation_message_id = @operation_id; 
  
 
 
 
您的MOM 
 
 
 
如同，您的管理对象模型提供了一个用于部署软件包的.NET接口。这是一种用于部署ispac的PowerShell方法，同时创建该文件夹，这是ISDeploymentWizard不支持的选项。
  Reflection.Assembly] :: LoadWithPartialName（Microsoft.SqlServer.Management.IntegrationServices）| Out-Null 
 
 #this允许显示调试消息
 $ DebugPreference =Continue
 
＃检索2012 Integration Services CatalogFolder对象
 ＃创建一个如果没有找到
功能Get-CatalogFolder 
 {
 param 
（
 [string] $ folderName 
，[string] $ folderDescription 
，[string] $ serverName =localhost\dev2012
）
 
 $ connectionString = [String] :: Format（Data Source = {0}; Initial Catalog = msdb ;集成安全性= 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 = New-Object Microsoft.SqlServer.Management.IntegrationServices.CatalogFolder（$ catalog，$ folderName ，$ folderDescription）
 $ catalogFolder.Create（）
} 
 
返回$ catalogFolder 
} 
 
＃将一个ispac文件部署到SSISDB目录
功能部署项目
 {
 param 
（
 [string] $ projectPath 
，[string] $ projectName 
，$目录Folder 
）
 
＃测试以确保文件存在
如果（-not $ projectPath -or -not（Test-Path $ projectPath））
 {
 Write-Debug（File not found $ projectPath）
 return 
} 
 
写入调试（$ catalogFolder.Name）
写入调试（部署$ projectPath）
 
＃将数据读入字节数组
 [byte []] $ projectStream = [System.IO.File] :: ReadAllBytes（$ projectPath）
 
＃$ Pr ojectName必须匹配.ispac文件中的值
＃否则您将看到
＃部署项目失败。解决问题，然后再试一次。指定的项目名称，测试与部署文件中的项目名称不匹配。 
 $ projectName =生命周期
 
 $ project = $ catalogFolder.DeployProject（$ projectName，$ projectStream）
} 
 
 
 
 
 $ isPac =C：\Dropbox\presentations\SSISDB Lifecycle\Lifecycle\Lifecycle\bin\Development\Lifecycle.ispac
 $ folderName =文件夹
 $ folderName =SSIS2012
 $ folderDescription =我是一个描述
 $ serverName =localhost\dev2012
 
 $ 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 bin\Development 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 Files\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.

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

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:\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.
[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


                        
这篇关于如何在sql server 2012中部署现有的SSIS包？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！