Set Mapping variable in Expression and use it in Source Filter

Question

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.

Answer 1

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.

Answer 2

Yes, it's possible.
In the first map you have to initialize the variable, like this:

In first session configuration you have to define the Post-session on success variable assignment:



The second map (with your table A) will get the variable after this configuration of the session in Pre-session variable assignment:

It will work.

Answer 3

Since we're having two different DBs, use two sessions. Get values in the first one and pass the parameters to the second one.