community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More

Writing records with primary key to DB after Alteryx Schedule fails

Alteryx Partner

I have a scheduled flow that runs in the Alteryx Gallery that failed for ~6 hours and then continued running (data missing from 3pm-10pm CST). This means that I now have a gap in the data and I need to write the data between this time window to my database. The caveat is that the table has a primary key, RecordID, that is generated with each run so each new record has a unique identifier. This means that while the timestamp on the records skips 6 hours, the recordID continued to generate as expected. I've been able to pull the records I need, but I'm having trouble finding a solution to write them to the DB with the correct recordID. 

 

The goal here is to do the following:

 

1. Write the missing records to the DB with the correct RecordID (see image below)

2. Update the RecordID of the newer records that were added once the schedule ran successfully again

 

Can you all help me out here?ERCOT RT LMP Error.png

Moderator
Moderator

Hi @dallen, thank you for the post here! 

 

Looking at your screenshot, it appears that you are missing 1266 rows (10452886 - 10454163) of records during the ~6 hours of gallery failure. If you managed to pull in these missing records into Alteryx Designer, you can fill the void in the database by first manually assign a RecordID to these missing records in the Designer using the Record ID tool. You want to start with RecordID '10454164' since the last ID that got inserted before the gallery failure is 10454163. After you have the data and RecordID sorted out in the Designer, you can write the data to your database using the Output tool with output option "Update; Insert if new". Before you run the workflow to write data to your database, make sure the Field Map is correct, especially the RecordID field. You want to map the RecordID field in the Designer to the RecordID field (Primary Key) in your database.

 

Capture.PNG

 

Attached please find sample workflow. Hope this helps! 

Eddie Wong
Customer Support Engineer
Alteryx
Labels