Jump to content

How do I assign the same datetime value to all records uploaded


Tim York

Recommended Posts

I'm using Scribe Online and have built a map to insert records from a CSV file into a SQL Server database.

My database table has a field named ImportDatetime which stores the date the record was inserted. If I am inserting 500 records from the CSV file, I would like each record to have the exact same ImportDatetime value in the SQL table. I've tried 4 approaches and found one that works but it is the least optimal of the 3. I'm hoping to find a better solution.

 

In the Create block, assign the formula GETDATETIME() to the ImportDatetime field.

 

This approach results in a slightly different value for each record since they are sequentially processed.

 

 

Use a variable to store the datetime at the beginning of the map execution.

 

This approach would be the right answer in every other ETL tool but doesn't work in Scribe (as far as I can tell).

Scribe does not allow any command to be executed between the Query and For..Each blocks. This means any command to set the variable will be run for each record. Running the command for each record means a different datetime value will be assigned to each record.

 

 

Use an If..Else block after the For..Each block to assign a value to the variable only on the first record and leave it unchanged.

 

This approach seems to be the next best solution but still doesn't work.

In the If..Else block, I set RECNUM() = 1 as the condition. If the condition is met (the first record is being processed), the variable is initialized. If not, nothing is done.

The problem is that only the first record uploaded gets assigned a value. Apparently, the variable is cleared with each subsequent record and, since the condition set in the If..Else block is not met, the resulting ImportDatetime value is null.

 

 

Create a separate map in the same solution that executes before the upload map. This map inserts a record in another SQL table with the current datetime for this solution execution. In the upload map, I added a Lookup block to query the datetime value from the table populated in the previous using the Solution ID as the lookup value.

 

This is a very poor design since I have to make a SQL call for every record to beinserted. However, it's the only solution I've found that works so far so it's what I'm going with.

 

 

 

This requirement seems so fundamental to any ETL system that I just can't believe I'm not missing something. I'm hoping someone out there can reply with a "You idiot! Here's all you need to do!" I would be ecstatic to find out I overlooked something obvious.

Thanks in advance for any help you can provide.

Tim

Link to comment
Share on other sites

Hi Tim,

I think option 4 will come closest to what you would like.

However you can optimze things a little.

 

Instead of using a SQL DB, which you would have to query. I would suggest using the Lookup Tables.

Enable the PlatformAPI connector. Create a connection to it.

 

Modify your 'add date to sql map' to write the current date time to the lookup table.

In the seqential map you would not 'query' the SQL table, but simply use a Lookuptable formula to retrieve the date time you have entered the previous map.

The lookup table values are stored locally onto your agent. So that means no external connection needed for every record.

 

I hope that makes sense.

Nick

Link to comment
Share on other sites

Hi,

I would suggest adding a slightimprovement to your approach number 3(almost correct as your described it)to get the same ETL style experience, i.e. get the datetime within the samemap(at start of the map, preprocessing) and reuse for all records;

(a variant of this is to write the datetime in a separate database table and look it up the PK idand link it to your dataset as FK id (ETL - DWH style datamodel, also possible using same variable connector with IF BLOCK (recnum=1) ).

Here is the reference documentation on usingthe Scribe labs variables connector.

https://support.tibco.com/s/article/Using-The-TIBCO-Scribe-Labs-Variables-Connector

 

You basically setup the variable (preprocessing using IF block (recnum=1), do all your preprocessing logic within this IF recnum=1 clause of the block and save your variable for reuse during your processing of records.

1)IF Block: get the current timestamp only once and store it in variable named: "var1", 9use the get datetime formula or lookitup from your datetime SQL table, depending on your target system approach.

- Choose the datatype you need for your variable:String or Datetime for example,

or if you are linking to a primarykey of the table where you store your datetime field, you can also make it an INTEGER.(typically done in ETL's loading into DWH/datamartdatamodels, soyou only write the datetime once in your datamodel then link your dataset to it.)

2) Then (very important!), outside your IF - Else Block, you add a scribe variable lookup block(this needs to be called by each loop thru the source records to bring the variable in memory scope;

- lookup criteria name="var1", select from the fields tab the Value field to get your datetime into memory.

This will make sure you have the variable ready for each record in your source and map it to your target system entity.

 

I hope this helps, if not please let us know, this is a common pattern used in scribe ETL designs.

Link to comment
Share on other sites

  • 2 weeks later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...