Error while using SQL Server Export/Import Wizzard (1 Viewer)

Hi,
I'm getting an error while trying to import from an Access-table to a sql-table, but 2/3 of all records are imported correct, 1/3 is ommitted because of this error msg:

Messages
Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Invalid date format".
(SQL Server Import and Export Wizard)

Error 0xc020901c: Data Flow Task 1: There was an error with Destination - tblLeistungen.Inputs[Destination Input].Columns[Datum] on Destination - tblLeistungen.Inputs[Destination Input]. 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 1: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Destination - tblLeistungen.Inputs[Destination Input]" failed because error code 0xC020907A occurred, and the error row disposition on "Destination - tblLeistungen.Inputs[Destination Input]" 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)


If the data in the Access-table in field ([Datum], which is indead not a clever name for a field) would be NOT correct - by the way I cannot see any incorrect format, as this field is filled by VBA-code (Me.MyDatum=Date()) - WHY are about 2/3 imported without errors and 1/3 cannot be imported??

This does not seem logic and I have no idea how to import thousands of records....

Pleas help me with any idea!

Thanks

Michael

http://msdn.microsoft.com/en-us/library/hh302874(v=sql.105).aspx
I have a litte more informaton about it here:
http://www.access-programmers.co.uk/forums/showthread.php?t=223173&highlight=SSMA

Let me strongly suggest using the SQL Server Migration Assistant for Access (SSMA)
For me, it has made better conversions and provided better errors.
It is also something that is used in AZURE.
If it makes the conversion, that is great. If it gives specific reasons, that isn't a bad outcome either.
My preference is to rapid protype tables and data in Access and then move it to SQL Server with this tool.

Hi,

thanks your reply, I am using the SSMA normally, in that case, as I had just one table to Import I used the wizard, but I could not solve it anyhow.

By the way I found an error in all those thousands records which most probably leads to the "skipping" of about 1/3 of the records.

ONLY 4 records had a incorrect date, 1014-10-09 instead of 2014-10-09. The Access table accepted these Dates but no so the SQL Server.

After correcting the year I still could not Import all records. Then i linked the SQL-table to the Access database and inserted the Access records in the sql-table and it worked!
So the Jet engine did something, what the SQL Import wizzard could not do.

Now, to add new records in the SQL Server table works normal.

Michael

Sorry, I should have keyed on the date field. The various date formats and difference between beginning dates can be a migration challange.
Yes, the linked table does provide additional ODBC support for this effort. Glad you found the problem there.
Are you using the OS ODBC or using the SQL Server Native Client 11.0 ODBC driver?
For dates, I have the illusion that the Native Client made my migration effors easier. It is based on ODBC but is suppose to have considerable more features.
http://msdn.microsoft.com/en-us/sqlserver/aa937733.aspx
This isn't the lastest article, but it covers some of the reasons to use Native Client.
All of the demos for MS Access linked to AZURE use Native Client for example.

I used the SQL Server Native Client 11.0 ODBC Driver before.
Thanks for the links!