powershell 如何在控制台日志中查看SSIS异常

k2arahey  于 12个月前  发布在  Shell
关注(0)|答案(1)|浏览(225)

我有windows server的Jenkins slave。我正在运行SSIS deploy命令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超时,但它不工作,以及。任何想法?
我发现的最相关的线程是2015年的this,另一种方法建议here也是2015年的

rqcrx0a6

rqcrx0a61#

ISDeploymentWizard做它做的事情。它是一个预构建的可执行文件,看起来你不能对错误处理做太多的事情。
我建议采用不同的方法,使用Managed Object Model或TSQL部署路由。这样,您可以控制错误条件发生的情况。

托管对象模型

我的PS部署代码看起来像这样。它不包括参数等,但它是一个开始,你可以尝试/捕获DeployProject部分

[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

TSQL部署

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命令,所以这些命令应该可以工作,并且给予您更大的灵活性来检测部署问题,然后解决它们。

相关问题