July 11, 2006 at 5:58 am
Hi,
I was wondering if anyone has run into this issue. I setup a series of DTS packages and created their scheduled jobs. These jobs were running without a hitch about a year ago. Now for the last 3-4 months some of the jobs appear like they are completing without a problem but when I view the tables, there is no data. I then run the DTS manually and everything is OK. I've setup a db user with the necessary privs. As I mentioned before everything was running fine. The only thing that has changed is that the network group now does a nightly backup on all servers from 9:00 PM to 9:00 AM every day.
Any thoughts would be most useful.
Thanks in advanced
July 11, 2006 at 6:40 am
Joe,
We are having the same type of problem. However it is sporadic. We have a weekly import job that moves a lot of data using multiple connection threads. Most of the time the packages move the data just fine. From time to time data is not copied...but no errors identified anywhere. I just started with this company, but they tell me this behavior started when they upgraded to SP4. This did not happen in SP3. Your post is the first I found with the similar issue.
I'm leaning towards a memory-leak, memory corruption, DMO issue, or a combination of all the above. They were rebooting the server on Fridays before the run and that seemed to have worked-around the issue.
Let me know if you have found anything? I'm still researching.
Thanks,
Alex
July 12, 2006 at 7:27 am
I've been here for the past two years and have used SP4 and this started when backups started running for 12 hours. I'm still not sure if this has anything to do with what's going on. I'm not sure what the backup procedure is but if there's a network drop that would explain why the data is not transfer into the destination tables.
Thanks,
July 12, 2006 at 12:45 pm
We have frequent network drops here. I've learned through experience that any remote file that I have to access as a source file in DTS, to always wrap the transform in a SQL transaction.
If you use an Execute SQL task before the tranform to delete or truncate the destination table, throw a BEGIN TRANSACTION before the deletion command, a ROLLBACK TRANSACTION on the failure workflow of the transform, and a COMMIT TRANSACTION on the success workflow.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply