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

问题描述

我有 windows server 的 Jenkins 奴隶.我正在运行 SSIS deploy 命令，认为 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屋！