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

New Contributor III

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 ?



Contributor III

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.

New Contributor III

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

- A property within these objects;

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

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


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.