Import Data error

[AccessHunter]

Hi,

I am trying to import data from an Access 2003 database table 'Cases' to the table 9dbo.Cases) in SQL Sever database and its giving me the following error. Could someone please explain what could be the issues here.

Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERRO R. 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)


Thanks,

[ck9663]

Hi,

I am trying to import data from an Access 2003 database table 'Cases' to the table 9dbo.Cases) in SQL Sever database and its giving me the following error. Could someone please explain what could be the issues here.

Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERRO R. 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)


Thanks,

check the structure of your source table and your target table... you might also want to import your access table into a non-existing table...once uploaded, analyze the table do an insert to your target

-- CK

[AccessHunter]

Thanks CK for the reply. Could you please explain the same little more,
You said "import my access table to a non-existing table", could you please elaborate on that.


Thanks.

[ck9663]

delete the table on your sql server to make sure you're uploading it to a non-existing table. it might be just inserting into an existing one...if you can not delete it, during import, give a tablename that you are sure a non-existing tablename. we're just trying to isolate your problem first.

-- CK

[Jim Doherty]

Hi,

I am trying to import data from an Access 2003 database table 'Cases' to the table 9dbo.Cases) in SQL Sever database and its giving me the following error. Could someone please explain what could be the issues here.

Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERRO R. 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)


Thanks,

One simple way of doing this on the client side that you might not know about is to simply create yourself an Access project ADP file in Access and then using the conventional access menubar method of File..... Get External data locate the mdb file containing the data and import into the ADP file the source table(s) from the mdb file.

You're not actually importing the table into the ADP file at all! you're merely using the ADP file as a vehicle to get your tables to the server.

Because ADP files communicate directly with SQL server the net effect is the ADP sets the tables up for you (datatypes, fieldsizes, and so on from the Access side) in much the same way as you might use the upsizing wizard. You can select multiple tables too using this method.

None of this is relevant of course if your requirement is to do this exclusively from the server side (you wouldnt have access installed on the server of course) , but I thought I'd throw it in.... if all you are wishing to do is get tables to the server.

Jim :)

[AccessHunter]

delete the table on your sql server to make sure you're uploading it to a non-existing table. it might be just inserting into an existing one...if you can not delete it, during import, give a tablename that you are sure a non-existing tablename. we're just trying to isolate your problem first.

-- CK

I tried uploading to a non-existing table, it gae me the following error,

Copying to [TESTAccess2007A dopTerm2000SQL].[dbo].[Cases] (Error)
Messages
Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERRO R. 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 "DateClosed " (292) on input "Destinatio n Input" (215). 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_INDUCEDTR ANSFORMFAILUREO NERROR. The "input "Destinatio n Input" (215)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destinatio n Input" (215)" 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)

[ck9663]

check the data type of that field. it might be inserting/converting it to a datetime datatype...your input might contain values that can not be converted int a date datatype

-- CK