Forum Discussion

Keyser_Soze's avatar
Keyser_Soze
Contributor
11 months ago

Import Step: Where is Data stored before being saved to 'SourceStageData' Table

I am using an Event Handler to manipulate loaded data before it gets saved to the staging area table. More specifically, I am duplicating some rows while adding new logic to the dimensions (I know it is not recommended but it is done in a controlled fashion). Derivative rules won't do the trick for me and they are very time consuming

After multiple tests, I found that data gets stored in 'SourceStageData' app table only after operation 'WriteTransformedData' or before 'SummarizeTransformedData'. Which is a bit too late for my case ...

 - My attempt: When I try to manipulate data stored in the 'SourceStageData' table, I can visualize the derivated rows in the 'Import' step Interface ! However, when I move the 'validate' step, they are not processed for transformation (even though all columns are valid, even 'Ri' column which is unique has a NewGuid inserted). This is what led me to the guess below

    - My guess would be: Loaded data rows are stored in memory and processed, then gets saved to the table 'SourceStageData' after operation 'WriteTransformedData' ? If so how can I access the loaded data before it gets processed please ?

  

Import
Workflow
  • MarcusH's avatar
    MarcusH
    Contributor III
    11 months ago

    I think it uses INSERT BULK direct from the source somehow so there's no intermediate table (at least that's what the profiler is saying). If you are using a text file as a source you could edit that file before it is imported. I have done it on the InitializeTransformer Before event.

    • Keyser_Soze's avatar
      Keyser_Soze
      Contributor
      11 months ago

      Thanks for replying,
      For AuditTrail purposes, I think it would be wise to keep the original file intact. Also, it won't work for every workflow Profile since the data sources aren't the same ... It would be hard parsing each file by dimension etc

      What method did you use to achieve this though ? 
      - Retrieving the .csv file from the harvest folder and modify it
       Or

      - A property within these objects;

            Dim objTransformer As Transformer = DirectCast(args.Inputs(0), Transformer)

            Dim objParser As Parser = DirectCast(args.Inputs(0), Parser)

       

      • MarcusH's avatar
        MarcusH
        Contributor III
        11 months ago

        It was much simpler than that. It was not a batch process so:

        - take a backup of the original file, read the file, make the changes and then save.