Seven Stars

Don't truncate output DB table if input row count is zero

Hi guys,

 

I have simple job to load data as it is from .csv file to MS SQLServer table :

 

loadinvent.PNG

 

 

There is a chance that the tFileInputDelimited component would not get the file at specified path. So in this case, input row count becomes 0 and truncating output table is totally ineffiecient. 

 

Is there any way to define a check like if input row count > 0 then only contrl should passed on to output table.

 

Pseudo code of requirement :

 

if count(NorthInventory_Rows > 0) {
       truncate the table raw_inventory_north and load data
} else {
      don't truncate the table raw_inventory_north  
}

 

Thank you !

Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
Twelve Stars

Re: Don't truncate output DB table if input row count is zero

Use a tFileExist component to test for the existence of the file. Then use Run If triggers to choose your path based on that.

Rilhia Solutions
8 REPLIES
Twelve Stars

Re: Don't truncate output DB table if input row count is zero

Use a tFileExist component to test for the existence of the file. Then use Run If triggers to choose your path based on that.

Rilhia Solutions
Seven Stars

Re: Don't truncate output DB table if input row count is zero

@rhall_2_0, Thanks. this is what I was looking for. But its giving an error :

 

run-if2.PNG

 

Twelve Stars

Re: Don't truncate output DB table if input row count is zero

Can you show your RunIf config? It looks like you may have an issue there. Alternatively can you show us line 1408 of your code for this job? Just go to the code tab and you will see where the error is from a red line on the right hand side.

Rilhia Solutions
Seven Stars

Re: Don't truncate output DB table if input row count is zero

There is nothing as condition in if statement :

 

run-if-error.PNG

 

Nothing specified in condition as well :

condition.PNG

What do I need to specify as condtion ?

 

I tried Global Variable 'EXISTS' as mentioned here, but no luck.

 

Twelve Stars

Re: Don't truncate output DB table if input row count is zero

Ah, that will be why. You need a condition in here. Just use this....

 

((Boolean)globalMap.get("tFileExist_1_EXISTS"))

The above will give you a boolean representing whether the file exists or not

Rilhia Solutions
Seven Stars

Re: Don't truncate output DB table if input row count is zero


rhall_2_0 wrote:

Ah, that will be why. You need a condition in here. Just use this....

 

((Boolean)globalMap.get("tFileExist_1_EXISTS"))

The above will give you a boolean representing whether the file exists or not


This get it on to work. Many thanks for your time @rhall_2_0 Smiley Happy

Seven Stars

Re: Don't truncate output DB table if input row count is zero

Hi @rhall_2_0,

 

How can I check the same in below case :

 

anothercase.PNG

 

So, if the output of tFlowMeter = O rows, prevent truncating the output MS SQL table.

Twelve Stars

Re: Don't truncate output DB table if input row count is zero

You could forget the FlowMeter and simply load the data to a tHashOutput. Use the component statistics (look in the bottom left corner of the Studio, you can expand the component name and drag out statistic values from there) to decide whether more than 0 rows have been loaded to the tHashOutput. If that is the case, load the data from a corresponding tHashInput to your DB component.

 

Your code for checking whether more than 0 rows have been written to your tHashOutput will look something like this....

 

((Integer)globalMap.get("tHashOutput_1_NB_LINE"))>0
Rilhia Solutions