September 16, 2009 at 2:09 pm
Hi,
We have this SSIS in production working wonderfully for a while and now we have this issue all of sudden.
The SSIS package is merging two datasets and then calls a stored prodecure on third database for insert or update of the merged records. After firing the SSIS job, profiler trace shows that each stored procedure is getting called for each merged records. But for some strange reasons the records are not updated or inserted in the table.
What could be it?? we are not getting any errors on Job. Job executes and finishes with status as 'SUCCESS'.
Will Appreciate some expert insights on how to troubleshoot it further.
thanks,
September 17, 2009 at 11:45 am
Are you sure the data is sorted properly? If you are using an OLE DB source and are sorting the data when it is coming in then set the IsSorted on the output and the SortKeyPosition on the column that is being ordered by.
You might want to add some data viewers in your data flow task so that you can monitor the rows throughout the process to further troubleshoot your issue.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
September 22, 2009 at 6:37 pm
Could there be a transaction that is getting rolled back?
CEWII
September 23, 2009 at 11:42 am
I've experienced this before. In my situation I took the calls being shown in the Profiler trace and ran them manually. What I found was that there was an error coming back however the stored procedure did not use the 'RaiseError' event, rather just returned a text message stating 'Error ...'.
To my SSIS package it looked like the proc was called and a result of success was sent back so all is good however, this was obviously not the case.
In the end I had the DB dev team modify the proc to actually return an error and all worked as expected at that point...
Hope this helps!
Ben Sullins
bensullins.com
Beer is my primary key...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply