SSIS-填写OLE DB目标表中未映射的列 [英] SSIS - fill unmapped columns in table in OLE DB Destination

问题描述

如下图所示，我在SQL Server中有一个表，该表是通过平面文件源填充的.我要根据以下列出的逻辑来更新目标表中的两列:

As you can see in the image below, I have a table in SQL Server that I am filling via a flat file source. There are two columns in the destination table that I want to update based on the logic listed below:

• SessionID-第一次CSV导入的所有行的值均为1；第二个导入的值将为2，依此类推.
• TimeCreated-CSV导入发生的日期时间值.

我不需要有关如何编写TSQL代码来完成此操作的帮助.相反，我希望有人建议一种在SSIS中将其实现为数据流任务的方法.

I don't need help with how to write the TSQL code to get this done. Instead, I would like someone to suggest a method to implement this as a Data Flow task within SSIS.

预先感谢您的想法.

编辑2012年11月29日

由于到目前为止，所有答案都建议在SQL Server方面解决此问题，因此我想向您展示我最初尝试做的事情(请参见下图)，但是没有用. SSIS将数据插入目标表后，触发器未在SQL Server中触发.

Since all answers so far suggested taking care of this on the SQL Server side, I wanted to show you what I had initially tried doing (see image below), but it did not work. The trigger did not fire in SQL Server after SSIS inserted the data into the destination table.

如果任何人都可以解释为什么触发器不触发，那就太好了.

If any of you can explain why the trigger did not fire, that would be great.

推荐答案

如果能够修改目标表，则可以使SessionID和TimeCreated的默认值为您完成所有工作. SessionID将是一个自动递增的整数，而TimeCreated的默认值将是getdate()或gettime()，具体取决于数据类型.

If you are able to modify the destination table, you could make the default values for SessionID and TimeCreated do all the work for you. SessionID would be an auto-incremental integer while the default value for TimeCreated would be getdate() or gettime() depending on the data type.

现在，如果您确实需要在工作流中创建值，则可以为每个值使用变量.

Now, if you truly need it the values to be created as part of your workflow, you can use variables for each.

SessionID是一个由Execute SQL Task设置的程序包变量.只需在结果集中引用变量，然后让SQL确定要使用的下一个数字即可.但是，这存在潜在的并发问题.

SessionID would be a package variable which is set by an Execute SQL Task. Just reference the variable in your result set and have your SQL determine the next number to use. There are potential concurrency issues with this, though.

通过基于系统变量StartTime在数据流中创建派生列，可以轻松完成TimeCreated.

TimeCreated is easily done by creating a Derived Column in your data flow based on the system variable StartTime.

这篇关于SSIS-填写OLE DB目标表中未映射的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！