Unable to update datetime to sink via data flow

Question

Hi,

I want to pass in a parameter to the data flow.

This variable is current UTC value.

@formatDateTime(utcNow(), 'yyyy-MM-dd HH:mm:ss.fff')

In my data flow, I have a derived column. My sink column is timestamp data type.

fromUTC(toTimestamp($p_sysupdate_dt, 'yyyy-MM-dd HH:mm:ss.SSS'), 'Asia/Singapore')

All my loading are ok but I added the derived column "sys_update_dt", I keep getting this error below. The error is misleading too.

Error: Job failed due to reason: com.microsoft.dataflow.Issues: DF-ARG-007 - store is not defined - [564 737],EXE-0001,Dataflow cannot be analyzed as a graph, source( ) ~> MainSource DF-ARG-007 - store is not defined - EXE-0001,Dataflow cannot be analyzed as a graph,[564 737], source( ) ~> ChangedQuotes DF-EXPR-009 - Column quote_item_id not found. The stream is either not connected or column is unavailable - [315 62 463 564 737],EXE-0001,Dataflow cannot be analyzed as a graph, MainSource, ChangedQuotes join( ) ~> JoinSourceAndChgQuotes,[463 564 737] DF-EXPR-010 - Column 'region' used in expression is unavailable or invalid. - EXE-0001,[315 221 320 463 564 737],Dataflow cannot be analyzed as a graph, JoinSourceAndChgQuotes select( ) ~> SelectSourceFields DF-EXPR-010 - Column 'form_number' used in expression is unavailable or invalid. - EXE-0001,Dataflow cannot be analyzed as a graph,[315 227 320 463 564 737], JoinSourceAndChgQuotes select( ) ~> SelectSourceFields DF-EXPR-010 - Column 'quote_number' used in expressio

Answer 1

Hi ,

Thanks for reaching out to Microsoft Q&A.

Check Parameter Type in Data Flow

• You’re passing @formatDateTime(utcNow(), 'yyyy-MM-dd HH:mm:ss.fff') to the data flow.
• Ensure the parameter (p_sysupdate_dt) in Data Flow Parameters is a string type, as formatDateTime returns a string.

Ensure Proper Timestamp Conversion

• Your Derived Column Expression

  fromUTC(toTimestamp($p_sysupdate_dt, 'yyyy-MM-dd HH:mm:ss.SSS'), 'Asia/Singapore')

  Potential Issue: If $p_sysupdate_dt is not being interpreted correctly as a timestamp, try:

  fromUTC(toTimestamp(toString($p_sysupdate_dt), 'yyyy-MM-dd HH:mm:ss.SSS'), 'Asia/Singapore')

• This ensures that the value is explicitly treated as a string before conversion.

Verify Sink Column Data Type

• The sink column must be of type timestamp or datetime.
• If the sink expects yyyy-MM-dd HH:mm:ss, try

  format(toTimestamp($p_sysupdate_dt, 'yyyy-MM-dd HH:mm:ss.SSS'), 'yyyy-MM-dd HH:mm:ss')

• If using Synapse/SQL, ensure the column is DATETIME2(3) for millisecond precision.

Fix Column Availability Issues in Error Log

• The error DF-EXPR-009 indicates missing columns (quote_item_id, region, form_number, quote_number).
• This typically happens if:
  • Joins or transformations dropped the columns before being referenced.
    • The column names were changed in an earlier transformation.
      • The source is not connected properly.

Fix to Try:

• Check whether the missing columns exist before being referenced.
• Add a Select transformation before the Derived Column and ensure those columns are available. Verify that the schema in the source and sink match.

5. Test by Hardcoding a Value

• If issues persist, test by setting a hardcoded timestamp value. If this works, the issue is likely in how p_sysupdate_dt is being passed.

  fromUTC(toTimestamp('2024-03-05 12:00:00.000', 'yyyy-MM-dd HH:mm:ss.SSS'), 'Asia/Singapore')

1. Confirm that p_sysupdate_dt is a string.
2. Ensure the timestamp format matches what toTimestamp() expects.
3. Check transformations (especially joins) to ensure required columns exist.
4. Test with a hardcoded value to isolate the issue.

Please feel free to click the 'Upvote' (Thumbs-up) button and 'Accept as Answer'. This helps the community by allowing others with similar queries to easily find the solution.

Answer 2

Hi @Lotus88

Thank you for posting your query!

The error message you are encountering indicates that there are issues with the data flow graph analysis, specifically related to column availability and definitions. Here are some steps you can follow to troubleshoot potential issues and understand what the error messages might indicate:

DF-ARG-007 - store is not defined

This suggests that there might be a missing or misconfigured data source or sink in your data flow. Ensure that all your data sources and sinks are correctly defined and linked in the data flow. Double-check the configurations to make sure that the connections and datasets are properly set up.

DF-EXPR-009 - Column not found

This error indicates that the data flow is trying to reference a column (quote_item_id, in this case) that does not exist or is not available in the specified dataset. Make sure that the column names used in your data flow expressions match exactly with those in your source datasets, including any case sensitivity.

DF-EXPR-010 - Column used in expression is unavailable or invalid

Similar to the previous error, this indicates that columns like region, form_number, and quote_number used in your expressions are not available. Ensure that these columns are present in the datasets you are working with at that stage of the data flow. Also, verify that any transformations or joins preceding this step do not inadvertently exclude these columns.

Verify Column Names - Ensure all referenced columns (quote_item_id, region, form_number, quote_number) exist in your source dataset. If any column has been removed or renamed, update your transformations accordingly.

Check Data Types - Confirm that the data types of your columns match the expected formats, especially for sys_update_dt. The sink expects a timestamp, so ensure proper type conversion.

Review Derived Column Logic - You're passing sys_start_run_dt from the pipeline as a UTC timestamp using:

@formatDateTime(utcNow(), 'yyyy-MM-dd HH:mm:ss.fff')

Then, in Data Flow, you're using:

fromUTC(toTimestamp($p_sysupdate_dt, 'yyyy-MM-dd HH:mm:ss.SSS'), 'Asia/Singapore')

Potential Fix - Ensure that the format in toTimestamp() exactly matches the input format. Since you're using .fff in the pipeline, update your Data Flow expression to:

fromUTC(toTimestamp($p_sysupdate_dt, 'yyyy-MM-dd HH:mm:ss.fff'), 'Asia/Singapore')

Alternatively, try explicitly casting it before conversion:

fromUTC(toTimestamp(cast($p_sysupdate_dt as string), 'yyyy-MM-dd HH:mm:ss.fff'), 'Asia/Singapore')

Also, ensure that the sink column data type is timestamp and not string.

Check Data Flow Connections - The error "Dataflow cannot be analyzed as a graph" indicates a potential issue with disconnected or incorrectly configured components. Ensure all transformations and sources are properly linked.

Test Expressions Independently - Try testing the derived column expression separately in a simpler pipeline to confirm it works as expected before integrating it into your full data flow.

I hope this information helps. Please do let us know if you have any further queries.

---

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.