在表达式中设置映射变量，并在源过滤器中使用它 [英] Set Mapping variable in Expression and use it in Source Filter

问题描述

我在不同的数据库中有两个表.在表A中是数据，在另一个表B中是用于从第一个表增量加载数据的信息.我想从表B加载并将上一次成功从表A加载的日期存储在映射变量$$LOAD_DATE中.为此，我从表B中读取了一个日期，并在表达式中使用SETVARIABLE()函数来设置$$LOAD_DATE变量.我在其中执行此操作的端口被标记为输出，并写入到虚拟平面文件中.我只读过此资料的一排！

然后，我在表A的源限定符的源过滤器中使用此$$LOAD_DATE变量，以仅加载比$$LOAD_DATE变量中存储的日期年轻的新记录.

我的问题是我无法正确设置$$LOAD_DATE变量.始终为日期1753-1-1-00.00.00，这是类型为日期/时间的映射变量的默认值.

我该如何解决?如何将日期存储在该变量中，以后再在Source Qualifiers源过滤器中使用它?甚至有可能吗?

表A的记录太多，无法全部读取并稍后进行过滤.这太昂贵了，因此必须在源过滤器级别对其进行过滤.

解决方案

在同一次运行中无法设置映射变量并在其他地方使用它的值，因为该变量实际上是在会话完成时设置的. /p>

如果您真的想使用映射变量来实现它，则必须创建两个映射，一个用于设置映射变量，另一个用于实际的增量负载.您可以使用工作流变量将映射变量值从一个会话传递到工作流中的另一个会话. https://stackoverflow.com/a/26849639/2626813

其他解决方案可能是在B上使用查找，然后再使用过滤器.

在执行映射之前，您还可以编写一些脚本来查询表B并使用最新的$ LOAD_DATE值修改参数文件.

I have two tables in different databases. In a table A is the data, in the other table B are information for incremental load of the data from the first table. I want to load from table B and store the date of the last successful load from table A in a mapping variable $$LOAD_DATE. To achieve this, I read a date from table B and use the SETVARIABLE() function in a expression to set the $$LOAD_DATE variable. The port in which I do this is marked as output and writes into a dummy flat file. I only read on row of this source!

Then I use this $$LOAD_DATE variable in the Source Filter of the Source Qualifier of table A to only load new records which are younger than the date stored in the $$LOAD_DATE variable.

My problem is that I am not able to set the $$LOAD_DATE variable correctly. It is always the date 1753-1-1-00.00.00, which is the default value for mapping variables of the type date/time.

How do I solve this? How can I store a date in that variable and use it later in a Source Qualifiers source filter? Is it even possible?

EDIT: Table A has too much records to read them all and filter them later. This would be to expensive, so they have to be filtered at source filter level.

解决方案

It is not possible to set a mapping variable and use it's value somewhere else in the same run, because, the variable is actually set when the session completes.

If you really want to implement it using mapping variables you have to create two mappings, one for setting the mapping variable and another for actual incremental load. You can pass a mapping variable value from one session to another in a workflow using a workflow variable. https://stackoverflow.com/a/26849639/2626813

Other solutions could be to use a lookup on B and a filter after that.

You can also write some scripts to query table B and modify the parameter file with the latest $LOAD_DATE value prior to executing the mapping.

这篇关于在表达式中设置映射变量，并在源过滤器中使用它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！