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 dont 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...
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...
