如何使用 SSIS 将包含递增变量值的额外列添加到目标表中? [英] How can I use SSIS to add an extra column containing the value of an incremented variable into my destination table?

问题描述

我定义了这个 SSIS 工作流.在我解释了我在做什么之后，我的方法可能是错误的，因为这是我的第一个真正的"SSIS 包.

I have this SSIS workflow defined. After I explain what I'm doing, it might be possible my approach is wrong, as this is my first "real" SSIS package.

为了增加计数器，我这样做:

To increment the counter I do this:

public void Main()
{
    int i = Convert.ToInt32(Dts.Variables["User::DayCounter"].Value.ToString());
    i++;
    Dts.Variables["User::DayCounter"].Value = i;

    Dts.TaskResult = (int)ScriptResults.Success;
}

我的目标表目前如下所示:

My destination table currently looks like this:

PID Campaign Name   Company Impr    Clicks  Clickthru   Leads   View-Through Leads  # Sales # View-Through Sales    Sales   View-Through Sales  Sub Sales   View-Through Sub Sales  We Get  They Get    Revenue EPC S U R T Active  SourceFile
1734    US - Lending Tree Refinance Mortgage - DA 3 Page SSN    Tree.com    58  2,053   3,539.66%   201 0   0   0    0.00    0.00    0.00    0.00    8044.02     5846.00     2198.02     2.85   10% Yes Active  \\ad1\Marketing\Aaron\DirectTrackAutomatedReports\admin_program_stats_20120229.csv

我的目标只是在报告中添加一个名为 Day 的列，其中包含计数器变量的值.

My goal is simply to add a column called Day to the report, containing the value of the counter variable.

推荐答案

您应该在平面文件源和 OLE DB 目标组件之间的数据流任务中添加派生列转换.

You should add a Derived Column transformation in your Data Flow task between the Flat File Source and OLE DB Destination components.

派生列转换将根据您的变量添加一个名为 Day 的新列.

The Derived Column transformation will add a new column called Day based on your variable.

之后，数据流中将显示一个新的 Day 列.只需将此列映射到相应的数据库字段即可.(假设您的报告表中有一个 Day 列.)

After that a new Day column will be presented in the data flow. Just map this column to your corresponding database field. (Assume there is a Day column in your report table.)

这篇关于如何使用 SSIS 将包含递增变量值的额外列添加到目标表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！