如何在我的控制台日志中出现 SSIS 异常 [英] How to have SSIS exception in my console log

问题描述

我有 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?

我发现的最相关的主题是 this 一种来自 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.

我建议采用不同的方法并使用 托管对象模型 或 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 = "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()
    }
    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:DropboxSandboxSSIS2012SSIS2012inDEV2012SSIS2012.ispac"
$folderName = "SSIS2012"
$folderDescription = "Prod deployment check"

$serverName = "localhostdev2012"

$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:sandboxSSDTDeployTSQLDeployinDevelopmentTSQLDeploy.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 异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！