如何通过映射参数与变量在Informatica中为Target表写入统一会话开始时间戳？

Alright, let's break down how to write the exact same session start timestamp to every record in your Target table using Informatica's mapping parameters or variables. The key here is ensuring we capture the timestamp once at session startup (not per record) so all rows get the identical value.

方法一：使用映射变量（Mapping Variable）存储固定会话时间 #

Mapping variables are perfect here because we can initialize them once at session start and keep that value consistent throughout the run.

• Step 1: Create the mapping variable
  Open your mapping in the Mapping Designer, right-click the Variables folder, and select Create. Choose Timestamp as the data type, name it something like $$SessionStartTS, and set an initial value (can be NULL—we'll overwrite it shortly).

• Step 2: Initialize the variable once at session start
  We'll use an Aggregator transformation to set the variable on the first record, then retain that value:

  1. Add an Aggregator to your mapping, connect your source to it.
  2. In the Aggregator, create an output port (e.g., o_SessionStartTS) with the expression:

     SETVARIABLE($$SessionStartTS, $PMSessionStartTime)
     

  3. Don't select any group by ports—this ensures the Aggregator only generates one row with the initialized variable value.

• Step 3: Join the timestamp to all source records
  To attach this fixed timestamp to every source row, use a Joiner transformation:

  1. Add an Expression transformation to your source pipeline, create a port join_key with a constant value (e.g., 1).
  2. Add another Expression transformation after the Aggregator, create a matching join_key port with value 1.
  3. Connect both Expression transformations to a Joiner, joining on join_key. This will do a Cartesian join, attaching the fixed timestamp to every source record.

• Step 4: Map to Target
  Connect the timestamp port from the Joiner to your Target table's TIMESTAMP field.

方法二：使用映射参数（Mapping Parameter）传递会话时间 #

This is a simpler approach if you don't want to mess with Aggregators and Joiners. We'll use a mapping parameter that pulls its value from Informatica's built-in session start variable.

• Step 1: Create the mapping parameter
  In the Mapping Designer, right-click the Parameters folder, select Create. Choose Timestamp as the data type, name it $$SessionStartParam.

• Step 2: Assign the session start value in the Workflow Manager
  Open your session in the Workflow Manager, go to the Mapping tab. Find your $$SessionStartParam and set its value to the system variable:

  $PMSessionStartTime
  

  This tells Informatica to populate the parameter with the exact time the session started, and this value stays fixed for the entire run.

• Step 3: Use the parameter in your mapping
  Add an Expression transformation, create a port o_SessionStart with the expression just $$SessionStartParam. Connect this port directly to your Target's TIMESTAMP field.

Important Notes #

• Data Type Matching: Make sure your mapping parameter/variable's data type matches exactly with your Target table's TIMESTAMP column to avoid conversion errors.
• Timestamp Format: If you need a specific format, use the TO_TIMESTAMP function to adjust. For example:

  TO_TIMESTAMP($PMSessionStartTime, 'YYYY-MM-DD HH24:MI:SS.FF')
  

• Aggregator Caveat: When using the Aggregator method, ensure your Joiner is set to Normal Join so no source records are dropped.

内容的提问来源于stack exchange，提问作者Natesh Vivek