SSIS 脚本转换入门

引言

本文旨在演示如何使用脚本转换来转换儒略历日期并创建额外的列以构建日期维度。它将展示连接其中一个脚本组件的基础知识，以及如何使用它为每个输入/处理过的行创建一个输出行。第一次尝试使用这些 SSIS 组件时，我很难找到有用的示例，我写这篇文章是为了让其他人也能从我学到的知识中受益。  我还想在此发出警告。我认为此代码对于 2000 年之前的日期存在局限性。我在 vb6 中有一个旧版本，它通过检查长度是否小于 6 来查看日期是否早于 2000 年。它也可能需要修改以处理闰年。

背景 

我编写这个脚本转换是作为迁移 JD Edwards 交易数据项目的一部分，该数据源自一个后端为 DB2 的 OLTP 系统。公司已决定关闭几个业务部门，因此不再需要使用 JD Edwards 系统进行新交易。展望未来，他们决定不续订 JD Edwards 的许可证，因此将无法访问该系统。还决定出售运行该系统的 IBM iSeries 硬件。

我的任务是在 SQL Server 数据库上创建数据的副本，并创建报表，以便他们将来可以随时查看数据。选择 SQL Server 是因为剩余的业务部门将继续在其服务器上维护数据库以支持持续运营。因此，我有一个新的数据仓库项目需要开发。

公司使用 JD Edwards 进行订单交易，因此我需要构建的第一部分之一是订单明细事实表和一些维度，如产品、客户和日期。  到目前为止，这看起来是一个相当典型的数据仓库项目。在订单明细表中，有三个重要日期。第一个是交易日期，第二个是发货日期，从
会计角度来看，最重要的发票日期是确认收入的日期。

我遇到的问题是日期格式为儒略历。JD Edwards 系统在用户界面和报表中很好地显示了常规日期，但系统中的底层日期是儒略历格式。例如，1/1/2012 表示为 112001，2/1/2012 表示为 112032。前三位数字表示年份。2011 年将是 111。最后三位是当年中的天数。

我决定，由于公司只需要过去四到五年的历史记录，日期维度不会很大，每年大约只有 365 行，减去周末、节假日以及任何没有活动的日期。我将使用儒略历日期作为日期维度的键，并为其分配整数数据类型以提高效率。

以下是 ETL 过程中将儒略历日期转换为常规日期以填充日期维度（包括所有现有日期和在软件不再使用之前输入的日期）的部分。

构建 ETL

因此，我用于基本转换的 ETL 过程如下所示：

我有一个 OLE DB 数据源，它查看 SQL Server 上的订单明细表，并查询日期的不重复列表。

为了简单起见，上面显示的是从 OrderDetail 表检索的单个列。

下一步是将脚本组件添加到数据流中。执行此操作时，您会看到选择将脚本组件用于源、目标或转换的选项。在这种情况下，选择的是转换。

现在，您的数据流中有一个脚本组件，您首先要做的就是将其连接到 OLE DB 源，以便它具有输入。如果跳过此步骤，您将无法完全配置该组件，并且如果尝试这样做，您将收到以下警告：

现在右键单击脚本组件并选择“编辑”。左侧窗格包含要配置的项目列表，主窗格是您进行更改和设置的地方。因此，对于第一个项目“脚本”，我们可以设置属性。以下属性以粗体显示，您可以更改它们。您有名称、描述和脚本语言。我大部分项目都使用 C#，因此下面的代码示例将是 C#。

现在我们可以查看下一项，即输入列。下面您将看到在 select 语句中返回的单个列。编写显式的 SQL 语句，准确告诉 SSIS OLE DB 源您想要返回的内容，这始终是最好的做法。这可以通过仅检索您需要的数据来保持效率，并防止源表修改后可能出现的未来问题。注意输入列的默认名称（input 0）。输入和输出的名称很重要，因为它们与您单击上面图像中的“编辑脚本”按钮时看到的脚本代码中的方法签名匹配。

好的，由于上面的图像中勾选了 InvoiceDate，我们完成了这一项。如果我们引入多个列或整个表，我们可以使用复选框选择列。现在我们继续配置下一项也是最后一项，即“输入和输出”。在下面的图像中，我展开了输入和输出。输入正如您所期望的那样，即 InvoiceDate。请注意，我们无法设置数据类型，因为组件知道该列的底层数据类型。在 JD Edwards 源数据库中，儒略历日期字段可能是一个字符串，但我在项目中使用它作为整数，因为它将是日期维度的键，并且它将是一个索引，整数在索引中更有效。而且这很有意义，因为将字段作为整数递增正好符合您对日期值的预期，即它在 0-365 的域内添加了一天。

同样请注意，有一个输出（Output 0），但没有列。您还应该知道，您可以重命名输入和输出。如果您这样做，它们将在脚本中更改。关键是，如果您要更改它们，最好在编写脚本部分的代码之前完成。现在，在这个例子中，我想在输出中添加两列，我通过使用“添加列”按钮来做到这一点。在单击并突出显示“输出列”文件夹后，此按钮将启用。

在下面的图像中，您可以看到我添加了两列：JulianDate 和 RegularDate。我希望 JulianDate 列包含源表中出现的原始儒略历日期，RegularDate 将是常规格式的日期（或者至少是可以通过常用内置函数修改的格式）。

好的，现在我们可以通过选择左侧的第一个项目“脚本”来编写一些代码。这样做时，您将再次能够访问上面图像之一中出现的“编辑脚本”按钮。下面是您在此示例中将看到的内容，该示例使用默认的输入和输出名称。

我知道细节有点难以看清，但在您的机器上会清晰可见。添加代码后，我的 Input0_ProcessInputRow 如下所示：

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
  String julianDate = Convert.ToString(Row.InvoiceDate);
    DateTime dt_date;
    if (julianDate.Length == 6)
    {
        try
        {
            string sDay = julianDate.Substring(3);
            string sYear = "20" + julianDate.Substring(1, 2);
            dt_date = DateTime.Now;
            int iCurrentDay = Convert.ToInt16(sDay) - dt_date.DayOfYear;

            dt_date = dt_date.AddDays(iCurrentDay);
            dt_date = dt_date.AddYears(Convert.ToInt16(sYear) - dt_date.Year);
            julianDate = Convert.ToString(dt_date.ToShortDateString());
            Output0Buffer.AddRow();
            Output0Buffer.RegularDate = Convert.ToDateTime(julianDate);
            Output0Buffer.JulianDate = Row.InvoiceDate;
        }
        catch
        {
        }
    }
}

代码的第一部分进行儒略历日期转换，这相当直接。try 块中的最后三行逐行创建输出，无需循环。请注意在 Output0Buffer 语句中使用 Output0 名称。如果您更改输入名称，方法名称也会更改。如果您更改输出名称，您也需要适应这些语句，所以先进行任何重命名。因此，这些语句中的第一个为每个输入行添加一个输出行。因为我添加了两个输出列，我现在可以在第二个语句中输出常规日期，在第三个语句中输出原始儒略历日期。我将添加其他列，如 dayOfWeek、Year 等。

现在，当我在脚本组件的输入上放置一个数据查看器，在输出上再放置一个，然后将它们发送到 Union All 来保存数据并允许我运行它时，您可以看到一行儒略历值进入，两列（一列儒略历，一列常规）出来。如果您想观看一些演示 SSIS 任务的网络研讨会，请尝试 Pragmatic Works，并在培训菜单中查找。您可以选择注册未来的网络研讨会，也可以观看过去的网络研讨会。

结论  

希望您能看到此组件的强大功能。最佳实践建议您不要使用脚本组件来完成现有组件可以为您完成的任务，因为它们通常效率更高，而且为什么浪费时间（除非您像我一样喜欢它  " src="https://codeproject.org.cn/script/Forums/Images/smiley_smile.gif" />）。我也希望这个例子能帮助一些人入门。第一次使用这个组件时，我没有得到任何指导，也不理解添加列、输出行和输入处理的细微差别。一旦我弄清楚了所有这些工作方式，我就开始运行了，希望您也能如此！