Update control table upon succesful data load

Question

I have a batch macro, which is getting a number CSV files as input, and are outputting into a Azure SQL DataWarehouse table via the output tool.

After each file is succesfully loaded into the db, I want to update a control table saying that file XYZ was inserted, such that the same files are not loaded the next time. But I only want to do this update on succesful completion. I have tried a couple of things unsuccesfully:

1) Adding an UPDATE statement for the control table in the POST CREATE SQL STATEMENT

2) Adding a "Parallel Block until done" tool (CReW macro), updating the control table as output 2.

For both of these I have the issue, that they will execute the control table update regardless of whether the insert is actually succesful. If the connection to Azure times out for instance, then no records are loading into the database, but the control table is still updated as if the load was completed.

How would be the best way of conditionally updating a control table, depending on the succes of an output tool? Any advice would be greatly appreciated.

Regards

Casper

MarqueeCrew · Accepted Answer

@caz777

Sounds like you have an input where you know: Filename, Total_Count_of_Records_to_be_Update:

and after a load you want to:  Select Filename, Count(*) from tablename Group By Filename;

You want to know that the counts match.  If they do match, then you want to set the control file for Filename to reflect a successful load.

If this is what you're trying to achieve, then I woulduse the POST CREATE SQL STATEMENT to get the count of loaded records from the batch and compare that result with the expected result.  This answer would get loaded to the control table.

Is this answer helpful?

Thanks,

Mark

bsharbo · Accepted Answer

Hello Caz777.

In the past I have done this using the Detour tool. So within the batch macro, I would put ANOTHER batch macro, which had a detour in it.

In your example:

After I inserted into the database, I would run the count(*) to make sure the number of records you expected to loaded were in-deed loaded.  if they were I would then pass a value of "1" into the batch macro, and then have that value dynamically update the Detour of "left - right" on the detour tool.

Then on the "left" detour i would put the write to the database table, and on the "right" detour I would put whatever else you want to have happen (can be nothing).

Basically this is like a giant "if then" statement.  you load data, then check if your counts are correct. if your are ARE correct, then Write to your other table, else "Fail".

I can post an example of what I am talking about if you need, but is my description sounding similar to what you need?