August 9, 2006 at 10:44 am
I am trying to get data through DTS and after transfering around 651K it gives me Exception Access Violation and it fails to transfer data there is more than millions records in the table!!
what is the solution for this please!!!!
August 11, 2006 at 2:26 pm
It sounds like you may have a problem with some OLEDB libraries on the machine where you are executing the DTS package itself.
Here's a few reccomendations. (Brian Knight, chime in if I'm off-base here)
1. Save the DTS as a structured storage file and copy it to another machine with the SQL client tools intstalled. Execute the DTS package using the DTSRun tool.
2. Make sure you have the "fast load" option set for the data pump task. Also try increasing the buffer size for the number of rows to insert.
3. Try reloading MDAC on the machine with the problem. You can find a copy of MDAC out at http://www.microsoft.com/data
4. If all else fails, you can run DTS by using the object model. If all you want to do is load a straight set of records from table A to table B, check out my perl script http://www.sqlservercentral.com/columnists/jbrown/flexibledtspackageswithperl.asp. You can download a copy of this script from the link at the bottom of the article. You'll have to install ActiveSate Perl from http://www.activestate.com. It uses the DTS object library to create a DTS package in RAM and execute it using the FASTLOAD option, but its a whole lot cleaner than designing and executing the package in the DTS designer.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply