mike55 Posted July 15, 2008 Posted July 15, 2008 I have two database servers, one is SQL Server 2000, the second is a SQL Server 2005 (Standard Edition). In the 2000 server, I have a database from which I need to take data on a regular basis and copy it to a particular database in 2005. In the tables that I am copying from I have a column titled "ETLStatus" which can either be NULL or 'T' where 'T' indicates transfered data. If a row is inserted or updated, the "ETLStatus" is set to NULL. I then run a select statement where the "ETLStatus IS NULL" and proceed to perform a SSIS Lookup operation on the 2005 database. If I find data during the lookup, I then perform an update operation on the 2005 database. If I don't find data during the lookup, I perform an insert operation. I have all my select, update and insert statements in a Sequence Contrainer, thus allowing me to do a Begin and Commit Transaction. One of the tables that I am processing has 250,000 records (give or take). The problem that I am having is that I seem to lose my connection with either the source or destination as I am performing the operation. In other cases I get the following error: "An OLE DB error has occurred. Error code: 0x80004005.". Any suggestions on how I could get over these problem. One option that has been suggested to me is to do a loop of 500 records at a time; not sure how practical this is... Quote A Client refers to the person who incurs the development cost. A Customer refers to the person that pays to use the product. ------ My software never has bugs. It just develops random features. (Mosabama vbforums.com)
Administrators PlausiblyDamp Posted July 17, 2008 Administrators Posted July 17, 2008 Is anyhting being logged by sql in window's event logs? Quote Posting Guidelines FAQ Post Formatting Intellectuals solve problems; geniuses prevent them. -- Albert Einstein
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.