November 18, 2015 at 8:10 am
I am updating records in a table based on mutually exclusive criteria.
Data is being passed from Conditional Split to either insert or update as below
Conditional Split - CASE A - UPDATE TABLE A
Conditional Split - CASE B - INSERT INTO TABLE A
Conditional Split - CASE C - INSERT INTO TABLE B
Inserts never fail.
Update is sporadic.
Worst case : Update works 15% of time
Best case:Update works 80% of time
There is seemingly no pattern.
Kicker:
If I attach a DataViewer in 'Update Table A' step then update command works 100% of time.
Help !
November 18, 2015 at 8:30 am
What are you getting for errors on the OLEDB Command?
Can you post table definitions and the update statement?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 18, 2015 at 10:30 am
No errors whatsoever.
This is literally one of those 'sometimes it works, sometimes it does not'
I see the OLEDB getting one row as expected, firing just fine, but update does not happen.
I suspected some kind of data anomaly coming into OLEDB Command, but if it did, then the update statement would always misfire on same records.
Interestingly, adding DataViewer just after data source, results in UPDATE not updating any records.
It is almost as if OLEDB command is firing asynchronously :-/
update statement below
UPDATE LFN_IMPORT
SET TRAILER_COUNT=?
WHERE SOURCE_FILE=?
TRAILER_COUNT is int being fed by ScriptComponent whose coressponding data type is unsigned int
SOURCE_FILE is varchar(500) being fed by ScriptComponent whose coressponding data type is DT_STR(500)
November 18, 2015 at 12:27 pm
Have you tried running a trace or XEvents session (assuming the destination is SQL Server) to see if the update statement is being run and what is actually being executed on the server?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 18, 2015 at 2:08 pm
amer-887457 (11/18/2015)
No errors whatsoever.This is literally one of those 'sometimes it works, sometimes it does not'
I see the OLEDB getting one row as expected, firing just fine, but update does not happen.
I suspected some kind of data anomaly coming into OLEDB Command, but if it did, then the update statement would always misfire on same records.
Interestingly, adding DataViewer just after data source, results in UPDATE not updating any records.
It is almost as if OLEDB command is firing asynchronously :-/
update statement below
UPDATE LFN_IMPORT
SET TRAILER_COUNT=?
WHERE SOURCE_FILE=?
TRAILER_COUNT is int being fed by ScriptComponent whose coressponding data type is unsigned int
SOURCE_FILE is varchar(500) being fed by ScriptComponent whose coressponding data type is DT_STR(500)
If it looks like it's firing asynch try setting the EngineThread property on the Data Flow Task to 1.
Maybe dump it into a staging table and work from there.
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
November 19, 2015 at 8:01 am
Changing Engine Thread seems to help.
Out of 7 records to be updated,I consistently get 5 or 6 updated.
(Hell, twice I even got all of them updated - out of some 10 runs)
Unlike before where I could not predict if it will update 1 or 6 records.
Sadly, SSIS tells me I cannot have less than 2 Threads.
How in the world can this be?
November 19, 2015 at 11:57 am
Never crossed my mind to trace it as I could see records flowing into the OLDE DB Command when I attached DataViewer to it.
H o w e v e r ....
Even though data apparently flows into the component, server trace shows no execution of the UPDATE command.
Never seen me this before.
Very sad.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply