troubleshooting Question

Using DTS to convert access database into Sql Server 2005 Express

Avatar of DrPcKen
DrPcKen asked on
Microsoft AccessMicrosoft SQL Server 2005
4 Comments1 Solution492 ViewsLast Modified:
I'm working on copying all my Access tables into a SQL Server 2005 Database I created.  All my tables seem to copy successfully with the exception of one.  The table name is Census.  In Access, it has a total count of 12,293 records in it.

I run DTS, set the datasource as my Access database, and my destination as my SQL database.  I select COPY DATA FROM ONE OR MORE VIEWS, then I select my census table in the source and leave everything else as defaulted.  It starts running the query then it errors out when it starts copying records.  The error report says this:

- Copying to [transfer].[dbo].[Census] (Error)
Messages
Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".
 (SQL Server Import and Export Wizard)
 
Error 0xc020901c: Data Flow Task: There was an error with input column "DOB" (111) on input "Destination Input" (83). The column status returned was: "Conversion failed because the data value overflowed the specified type.".
 (SQL Server Import and Export Wizard)
 
Error 0xc0209029: Data Flow Task: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "Destination Input" (83)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (83)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047022: Data Flow Task: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - Census" (70) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED.  Thread "WorkThread0" has exited with error code 0xC0209029.  There may be error messages posted before this with more information on why the thread has exited.
 (SQL Server Import and Export Wizard)
 
I have no idea where to start with all those errors.  After it did that I check my record count in the SQL database, and it managed to copy 2684 of the 12000+ records.

I can't figure out any reason why this would do this.  The only thing I notice is the census does NOT have a primary key field.  Would adding one help?

Please help!  Thank you!!!
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros