如何在我的控制台日志中出现SSIS异常

问题描述

我有 windows服务器的Jenkins奴隶.我正在运行 SSIS 部署命令，想到了 Powershell :

I have Jenkins slave of windows server. I'm running SSIS deploy command thought Powershell:

 $ISDeploymentWizard = Start-Process -FilePath ISDeploymentWizard.exe -ArgumentList '/Silent','/ModelType:Project','/SourcePath:"Integration Services\\bin\\Development\\Integration Services.ispac"',"/DestinationServer:${Env}",'/DestinationPath:"/SSISDB/TEST/DEVOPS"' -wait -PassThru -Credential $cred
    $ISDeploymentWizard.WaitForExit()
    $ISDeploymentWizard

问题是当我遇到错误时，在 Jenkins 控制台中看不到任何内容，因为 silent 模式对于 ISDeploymentWizard不能很好地工作.语法中的任何错误都将导致带有错误的远程弹出窗口.有什么办法可以使它也出现在控制台中吗?在当前情况下，这项工作只是停留在这个阶段，我必须手动中止它.我也尝试过使用 powershell 超时，但是效果不佳.有什么主意吗?

The issue is when I have an error, I don't see anything in my Jenkins console as the silent mode is not working very well for ISDeploymentWizard. Any error in the syntax is causing a pop up window in the remote with the error. Is there any idea how can I make it appears also in my console? In the current situation, the job is just stuck at this stage, and I have to abort it manually.I also tried to use powershell timeout, but it's not working as well. any idea?

我找到的最相关的线程是此一项，始于2015年，另一种替代方法建议此处也是2015年

The most relevant thread I've found is this one from 2015, and one alternative way suggested here also form 2015

推荐答案

ISDeploymentWizard会执行其操作.这是一个预先构建的可执行文件，似乎您不能对错误进行太多处理.

The ISDeploymentWizard does what it does. It's a prebuilt executable and it appears you can't do much with the error handling.

我建议您采用其他方法，并使用 Managed Object Model或TSQL部署路由.这样，您就可以控制发生错误情况时发生的情况.

I would advise taking a different approach and use the Managed Object Model or a TSQL deploy route. That way, you can control what happens with your error conditions.

我的PS部署代码如下所示.它不涉及参数等，但这只是一个开始，您可以尝试/捕获 DeployProject 部分

My PS deploy code looked like this. It doesn't cover parameters and such but it's a start and you can try/catch the DeployProject portion

[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()
    }
    else
    {
        $catalogFolder.Description = "Modified for SO2"
        $catalogFolder.Alter()
        Write-Debug([System.string]::Format("Existing folder {0}", $folderName))
    }

    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 = "HR Import Raw"
    $projectName = "SSIS2012"

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


$isPac = "C:\Dropbox\Sandbox\SSIS2012\SSIS2012\bin\DEV2012\SSIS2012.ispac"
$folderName = "SSIS2012"
$folderDescription = "Prod deployment check"

$serverName = "localhost\dev2012"

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

Deploy-Project $isPac $projectName $catalogFolder

https://techcommunity.microsoft.com/t5/sql-server-integration-services/a-glimpse-of-the-ssis-catalog-managed-object-model/ba-p/387892

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'C:\sandbox\SSDTDeploy\TSQLDeploy\bin\Development\TSQLDeploy.ispac', 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_id = @operation_id;

我认为jenkins允许使用PS或sql命令，因此它们应该可以工作，并在检测部署问题然后解决它们方面为您提供更大的灵活性.

I assume jenkins allows for PS or sql commands so these should work and give you more flexibility with regard to detecting deployment issues and then resolving them.

这篇关于如何在我的控制台日志中出现SSIS异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！