Skip to content
+1-888-319-3663

COMMUNITY FORUM

SQL tasks

Daniel asked 5 years ago
I have 2 sql command tasks setup, one on record success and one on record failure.  Basically, I want to update my source data status to success or failure for each record processed.  After adding these tasks, the map runs much slower…  Is there a way at the end of the map, run one script and update all of the successes and I will leave the failure script at the document level?
Answers
Patrick Roth Staff answered 5 years ago
Daniel,
Yes, surely you could run whatever script you like at the desired time/location.
The biggest problem that I see with the “after map complete” idea is: How do you know which ones to update?
I mean you have the document number and so that you can easily use that at the document level to “fail” your documents.  But the issue that we see is how do you know the “good ones”?
The only thing I can maybe think of is that in the table Status = 0 means “not attempted”, 1 = failed, 2 = good
since your after document fail task runs, all the ones that have failed are set to 1 already.
so you could potentially just update the ones that are set to 0.  those must have been “good” since they were processed and didn’t fail (would be 1)
the other thing you could do (if we aren’t processing the entire original source file) is keep track of the documents as they are processed.
so in the ‘after document success’ script, keep track of the ones that were successful.
GBL_SUCCESSDOCS = GBL_SUCCESSDOCS + ‘_DOCUMENTNUMBER’ + “,”
so I the end, we should have a list like
‘00001’,’00003′,’00004′,’00006′,
Then in our task we now have a list of documents. you’ll maybe have to trim off the trailing single comma (or write your code better to not have one like my example isn’t doing)
then in your task, you’d just run
update Mytable set Status = 2 where DOCNUMBER in (GBL_SUCCESSDOCS)
you would need to adjust the where clause to fit your table of course but something like the above approach would work fine.
Daniel replied 5 years ago

is writing to a variable faster than executing a sql statement? since I’m putting a task on the document success why not just write the sql statement?


If you would like to submit an answer or comment, please sign in to the eOne portal.