Link to home
Start Free TrialLog in
Avatar of DrPcKen
DrPcKen

asked on

Using DTS to convert access database into Sql Server 2005 Express

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!!!
ASKER CERTIFIED SOLUTION
Avatar of jmoss111
jmoss111
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DrPcKen
DrPcKen

ASKER

Would it be because some of the dates are blank in the access database?
Avatar of DrPcKen

ASKER

Ok well the nulls don't seem to be the problem because some of the records that transferred have nulls in them.  Is there a way to check the format of the date? Or find any invalid date fields?
Avatar of DrPcKen

ASKER

Brilliant!  I queried all records without any null dates, and went through and found a couple invalids (ie 5/5/199) and fixed them, then tried DTS again and it worked perfectly!!!

THANKS!