SQL Server Import and Export Error

Question

I am trying to import a 82k + row table from db access to SQL Server 2008.

Using the SQL Server Import and Export Wizard, I get an error message about 78 thousand records.

Here's the error:

Error 0xc0208265: Data Flow Task 1: Failed to retrieve long data for column "members_notes". Error 0xc020901c: Data Flow Task 1: There was an error with output column "members_notes" (41) on output "OLE DB Source Output" (11). The column status returned was: "DBSTATUS_UNAVAILABLE". Error 0xc0209029: Data Flow Task 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "members_notes" (41)" failed because error code 0xC0209071 occurred, and the error row disposition on "output column "members_notes" (41)" specifies failure on error. Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Query" (1) returned error code 0xC0209029. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. 

The members_notes column in the access database is a memo field. In the SQL Server table to which this data is migrated, I defined this field as nvarchar (MAX).

I looked for this error message and found articles suggesting to compress my SQL-db and clean up temporary files on the computer running Export / Import, none of which worked.

Anyone else run into this problem?

Answer 1

I started searching in the memo field in the Access database. The last line contains "#Error" in the memo field. When I clicked in this field, I received the following message:

"The Microsoft Jet database engine stopped the process because you and another user are trying to modify the same data at the same time."

I ran Compact and Repair in the Access database. This put a bunch of # characters in the memo field for this particular entry.

Then I tried the SQL Server Import and Export Wizard again. It worked great during this time!

Answer 2

I don't have enough reputation to answer your dpii answer, but it definitely worked for me. This problem made my head, and a simple compact and repair made her leave. Also beware of other usesr when using a file that you are loading at the same time (or open the file in Access), or you may encounter this error

Answer 3

This seems to be a bug in SQL Server 2005 that needs to be fixed, see here

Edit: to your comment below, it is fixed in 2008 SP1 CU5 .