SSIS 互联网连接问题 [英] SSIS internet connectivity issue

查看:44
本文介绍了SSIS 互联网连接问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

所以我们有多个 SSIS 包,它们并行运行以对多个多维数据集执行增量加载.这个包每晚运行.有时我们会遇到网络问题.现在,如果 SSIS 断开连接,即使是片刻,它也会使所有包失败.

So we have an multiple SSIS packages which are run parallel to perform incremental loads to the multiple cubes. This package runs every night. Occasionally we run into network issues. Now if SSIS looses the connection for even a moment, it fails all the packages.

即使连接变为活动状态并再次可用,它也不会自动尝试重新连接.它使用在执行开始期间获取的相同连接信息继续执行包.现在由于这个原因,所有剩余的包都失败了.为了再次连接,允许完成执行,然后有人必须手动重新处理包以获取新的连接信息.

It does not automatically try to re connect even if the connection become active and is available again. It continues to execute the packages using the same connection info that it acquired during the start of execution. Now due to this all remaining packages are failed. In order to connect again, it is allowed to finish execution and then someone has to manually re process the packages in order to acquire new connection info.

所以我的问题是,我能否实现一个系统,如果 SSIS 失去网络连接,它会尝试自动检查连接在处理过程中是否可用并尝试重新连接而不是使所有包失败?

So my question is, can i implement a system in which if SSIS looses network connection, it tries to automatically check if the connection becomes available during processing and try to reconnect rather than failing all packages?

推荐答案

一种实现包依赖和可重启性的方法,作者 @billinkc

An approach for package dependency and restartability, by @billinkc

我喜欢执行框架的一种非常精简的方法——它是一个两表系统,但已经足够了.在每个包的开头,都会触发一个 Execute SQL Task 来启动日志记录.当 SSIS 包成功完成后,最后一步是关闭进程的审计行 - 另一个执行 SQL 任务.

I favor a very lean approach for execution frameworks - it's a two table system but it's enough. At the beginning of each package, an Execute SQL Task fires that starts a log record. When the SSIS package completes successfully, the final step is to close the audit row for the process - another Execute SQL Task.

由于没有关闭时间以及 SSIS 现在没有运行的事实,我知道包异常结束.也许它抛出了外键违规,也许网络掉线了.我真的不在乎这张桌子.唯一的功能是完成/未完成.如果它没有完成,那么当我们重新启动它时,它是一个待处理的候选对象.

By the absence of a close time and the fact that SSIS isn't running right now, I know the package ended abnormally. Maybe it threw a foreign key violation, maybe the network dropped. I don't really care for this table. The only function is Done/Not Done. If it's Not Done, then when we restart it's a candidate for processing.

你说的候选处理?是的,worker 包是愚蠢的——他们只做我上面概述的事情.编排器/父/主包有责任考虑哪些包需要运行以及以什么顺序运行.如果子包开始运行,那是因为它被告知要运行.它不知道"它是否已经完成它的工作.

Candidate for processing you say? Yes, the worker packages are dumb - they only do what I outlined above. It is the responsibility of the orchestrator/parent/master package to worry about what packages need to run and in what order. If a child package starts running, it's because it was told to. It doesn't "know" whether it's already done it's work or not.

包执行历史全部在一张表中完成.它不是替代 SSIS 将记录到的 sysdtslog90/sysssislog/catalog.operation_messages 表.根据您的版本,这些表将包含有关包启动但未完成的原因的实际详细信息.

The package execution history is all done in one single table. It is not a replacement for the sysdtslog90/sysssislog/catalog.operation_messages tables(s) that SSIS will log to. Those tables, depending on your version, will hold the actual details about why the package started but didn't complete.

要让master知道什么没有运行,它需要知道三件事

For the master to know what hasn't run, it needs to know three things

  • 什么是刷新间隔
  • 应该运行哪些进程
  • 什么已经成功运行.

刷新间隔 - 您的业务需求和/或数据可用性有助于决定这一点,但我认为它需要每天运行一次.然而,细节决定成败,你如何定义那一天——从午夜开始,或者在过去的 24 小时内,或者其他一些规则.无论是什么,您都需要知道在部分重试变为中止并执行完整运行之前需要多长时间.

Refresh interval - your business requirements and/or data availability help dictate this but I assume it needs to run once a day. Devil's in the detail though, how do you define that day - since midnight, or in the past 24 hours, or some other rule. Whatever it is, you need to know how long before a partial retry turns into an abort and do a full run.

需要运行什么 - 至少,这是父程序将调用的所有子程序包的列表.我更喜欢做的事情听起来与您的方法相似,我并行运行许多包,但在该并行块中串行执行.我在那里得到了很好的并行化,而不会陷入困境.

What needs to run - At a minimum, this a list of all the child packages the parent will call. What I prefer to do sounds similar to your approach, I run lots of packages in parallel but a serial execution within that parallel block. I get good parallelization in there without swamping the boxes.

什么已经成功运行 - 你已经知道答案了!我们查看所有开始运行但没有结束日期的包的包执行历史记录.这些记录中的任何一个都需要重新启动(减少刷新间隔所需的摆弄).

What has run successfully - you already know the answer! We look at our package execution history for any packages that started to run but don't have an end date on them. Any of those records are what need to be restarted (less the fiddling required for refresh interval).

这是高级概念,希望不是 tl;dr

That's the high level concepts, hopefully wasn't tl;dr

选择一个数据库,任何保存此信息的数据库.有些地方我创建了一个专用的 SSISAudit 数据库,其他时候我将它与其他元数据存储库相结合.

Pick a database, any database to hold this information. Some places I create a dedicated SSISAudit database, other times I dovetail it in with other metadata repositories.

这将在您的数据库中创建 3 个对象(不包括表的插入/更新/删除方法).这两个表和一个确定需要运行哪些包的存储过程.向过程提供主包名称您需要为其工作的容器,然后输出需要工作的包的记录集(也就是自午夜以来尚未成功运行).

This will create 3 objects in your database (not included are insert/update/delete methods for the tables). The two tables and a stored procedure that determines what packages need to be run. Feed the procedure the master package name the container you need to get work for and out comes a recordset of packages requiring work (aka have not run successfully since midnight).

-- This is my package execution history table
CREATE TABLE 
    dbo.PackageHistory
(
    PackageName sysname NOT NULL
,   PackageID uniqueidentifier NOT NULL
,   ParentPackageID uniqueidentifier NULL
,   ExecutionID bigint NULL
,   StartTime datetime NOT NULL
,   StopTime datetime NULL
,   Duration_s AS (DATEDIFF(SECOND, StartTime, StopTime))
);

CREATE TABLE dbo.PackageDependency
(
    MasterPackageName sysname NOT NULL
,   ContainerName sysname NOT NULL
,   ChildPackageName sysname NOT NULL
,   ExecutionOrderSequence int NOT NULL
,   CONSTRAINT PK_PackageDependency PRIMARY KEY 
    CLUSTERED
    (
        MasterPackageName ASC
    ,   ContainerName ASC
    ,   ChildPackageName ASC
    )
);

GO
-----------------------------------------------------------------------------
-- Function: AUD.PackageProcesssingControlListGet
-- Author: Bill Fellows
-- Date: 2016-01-18
--
-- This procedure will return a list of outstanding pacakges that need to 
-- be processed for a given container. Assumes that processing is done daily
--
-- Recordsets:
-- 0 to N rows will be returned to the caller
--
-- Side-effects:
-- None
--
-- See also:
--
-- Modified:
--
-----------------------------------------------------------------------------
CREATE PROCEDURE dbo.PackageProcesssingControlListGet
(
    @MasterPackageName sysname
,   @ContainerName sysname
)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT
        PD.ChildPackageName
    FROM
        dbo.PackageDependency AS PD
        LEFT OUTER JOIN
            -- Find packages that have not completed
            AUD.PackageControl AS PC
            ON PC.PackageName = PD.ChildPackageName
            -- Since midnight
            AND PC.StartTime > CAST(CURRENT_TIMESTAMP AS date)
            -- StopTime is only populated when package completes successfully
            AND PC.StopTime IS NOT NULL
    WHERE
        PD.MasterPackageName = @MasterPackageName
        AND PD.ContainerName = @ContainerName 
        AND PC.PackageName IS NULL
    ORDER BY 
        PD.ExecutionOrderSequence ASC;
END
GO

现在呢?举个简单的例子

Now what? Let's gin up a simple example

这是主包的样子.请原谅它上面没有注释,我没有安装 Snagit,而且我对 mspaint 很着迷

Here's what the master package looks like. Forgive the lack of annotations on it, I don't have Snagit installed and I am le suck at mspaint

这是对 PackageHistory 表的插入.我全面指定了系统变量,没有为 ParentPackageID 或 StopTime 指定值.ExecutionID 将是来自 System::ServerExecutionID 的值,我认为其余的都是不言而喻的.

This is an insert into PackageHistory table. I specify system variables across the board, with no value specified for ParentPackageID or for StopTime. ExecutionID is going to be the value coming from System::ServerExecutionID I think the rest are self evident.

这是一个序列容器.根据您希望并行运行的情况制作尽可能多的这些.但是,只有在完成第一个之后才能复制和粘贴,以使您的生活更轻松.

This is a sequence container. Make as many of these as you want to have things running in parallel. But, only copy and paste after you have the first one complete to make your life easier.

注意我定义了 3 个变量,它们的作用域是 Sequence Container.默认情况下,在 2012+ 中,变量是在包级别范围内创建的.在 2005/2008 年,在您拥有鼠标焦点的地方创建了变量.对于 2012+,正常创建变量,然后单击变量列表中的第二个图标,即移动变量".将这三个移动到序列容器.

Notice I have 3 variables defined and they are scoped to the Sequence Container. By default in 2012+, variables are created at the package level scope. In 2005/2008, variables were created where you had mouse focus. For 2012+, create variables as normal and then click the second icon in the Variable list which is "Move Variable". Move those three to the Sequence Container.

这将允许我们使用范围规则来保持变量对其他容器隐藏.懒惰 - 我是懒惰的粉丝

This is going to allow us to use scoping rules to keep variables hidden from other containers. And be lazy - I'm a fan of lazy

3 个变量.

  • ContainerName - 这是您将存储在 PackageDependency 表的 ContainerName 列中的值.
  • CurrentPackageName - 这是当前项目中有效 SSIS 包的名称.没关系,因为这会被覆盖,但确实有 .dtsx 扩展名
  • rsWorkList - 这是一个 Object 类型的变量.它将保存我们的存储过程调用的结果.

我们需要获取这个容器应该运行的所有包的列表.记住我们创建的存储过程,让我们使用它.SQL 语句是并且您需要指定执行 SQL 任务将返回一个完整的结果集.

We need to get a list of all the packages this container should run. Remember that stored procedure we created, let's use it. The SQL statement is and you need to specify the Execute SQL Task will return a Full Result set.

EXECUTE dbo.PackageProcesssingControlListGet ?, ?;

在结果集选项卡中,将名称 0 映射到 User::rwWorkList

In the Result Set tab, map the name of 0 to User::rwWorkList

这是标准的粉碎记录集.我们在上一步中填充了变量,因此让我们枚举结果.我们的结果集中将有一列,您将其映射到 User::CurrentPackageName

This is a standard shredding of a recordset. We got our variable populated in the previous step so let's enumerate through the results. There will be one column in our result set and you will map that to User::CurrentPackageName

序列中的最后一步是实际运行我们刚刚从工作列表中弹出的包,因此执行包任务具有基于变量@[User::CurrentPackageName] 的 PackageName 表达式.

The final step in the sequence is to actually run the package we just popped off the work list so an Execute Package Task that has an expression for PackageName based the variable @[User::CurrentPackageName].

此外,我将使用参数绑定选项卡 (2012+) 将当前 System::ExecutionID 作为参数 ParentExecutionID 传入子包.

Furthermore, I'm going to use the Parameter Bindings tab (2012+) to pass in the current System::ExecutionID to the child package as the parameter ParentExecutionID.

如果我们完成此任务,我们将更新 PackageHistory 行,其中 ExecutionID 和 PackageIDs 匹配且 StopTime 为 NULL.

If we reach this task, we update the row the PackageHistory where the ExecutionID and PackageIDs match and the StopTime IS NULL.

这实际上只是父包,除了中间没有 0 到 N 个序列容器,我们只是做子包所做的任何事情.我确实有一个包级别参数来接受父包 ID,但您不必这样做.

This is actually just the parent package except instead of having 0 to N sequence containers in the middle, we just do whatever the child package does. I do have a package level parameter to accept the parent package id but you don't have to do so.

看起来合理吗?如果是这样,那么您所要做的就是在 dbo.PackageDependency 表上做一些记账.

Seem reasonable? If so, then all you have is some bookkeeping to do on the dbo.PackageDependency table.

INSERT INTO
    dbo.PackageDependency
(   MasterPackageName
,   ContainerName
,   ChildPackageName
,   ExecutionOrderSequence
)
SELECT
*
FROM
(
    VALUES
    (
        'so_34866238.dtsx'
    ,   'List0'
    ,   'C1.dtsx'
    ,   10
    )
    ,
    (
        'so_34866238.dtsx'
    ,   'List0'
    ,   'C2.dtsx'
    ,   20
    )
    ,
    (
        'so_34866238.dtsx'
    ,   'List1'
    ,   'D1.dtsx'
    ,   10 
    )
) D (MasterPackageName, ContainerName, ChildPackageName, ExecutionOrderSequence);

GO
-- Test we get expected results
-- 2 rows
EXECUTE dbo.PackageProcesssingControlListGet 'so_34866238.dtsx', 'List0';
-- 1 row
EXECUTE dbo.PackageProcesssingControlListGet 'so_34866238.dtsx', 'List1';
-- NULL rows
EXECUTE dbo.PackageProcesssingControlListGet 'so_34866238.dtsx', 'List2';

最后,虽然这还不错,但有点乏味.如果我要这样做,我会用一些 Biml 自动消除日光.今晚我写得太晚了,但我会在我分心之前完成它.

Finally, while this isn't bad, it's somewhat tedious. If I were to do this, I'd automate the daylights out of it with some Biml. Too late tonight for me to write that up but I'll get it done before I'm too distracted.

这篇关于SSIS 互联网连接问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆