Error while importing Access table to SQL.

bfuchs
bfuchs used Ask the Experts™
on
Hi Experts,

I'm getting the following error when trying to import an Access table to SQL Server (2008).

- Copying to [dbo].[Skilled_Nursing_Visit_Note] (Error)
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 10.0"  Hresult: 0x80004005  Description: "Invalid date format".
 (SQL Server Import and Export Wizard)
 
Error 0xc020901c: Data Flow Task 1: There was an error with input column "Date_Of_Birth" (382) on input "Destination Input" (284). 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 "input "Destination Input" (284)" failed because error code 0xC020907A occurred, and the error row disposition on "input "Destination Input" (284)" 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 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Destination - Skilled_Nursing_Visit_Note" (271) failed with error code 0xC0209029 while processing input "Destination Input" (284). 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 0xc02020c4: Data Flow Task 1: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
 (SQL Server Import and Export Wizard)
 
Error 0xc0047038: Data Flow Task 1: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "Source - Skilled_Nursing_Visit_Note" (1) returned error code 0xC02020C4.  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.
 (SQL Server Import and Export Wizard)

Open in new window

Already tried selecting custom mappings and changing the datatype to varchar/text, but getting conversion error.

Also the import process stops in middle process after importing part of the records, and I have no way to find out which record/s are causing this problem.

Ran a query in Access to see which records are DOB not null and isdate(DOB) = 1, but nothing appeared.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> There was an error with input column "Date_Of_Birth" (382) on input "Destination Input" (284). The column status returned was: "Conversion failed because the data value overflowed the specified type

It says conversion failed which makes it clear that the datatypes in Access and SQL Server 2008 is not matching.
If you can share us some date values, we can guide you better..
Kindly use CONVERT functions to convert the ACCESS date values based upon the correct regional value settings..
https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017
John TsioumprisSoftware & Systems Engineer

Commented:
Take a look here for Ms Access to MSSQL datatypes mappings
If you can share us some date values, we can guide you better..
Its a date/time field in Access coverting to datetime in SQL.
Kindly use CONVERT functions
I'm using the wizard to import directly from a table, not sure where you want me to apply convert functions...

Take a look here for Ms Access to MSSQL datatypes mappings
There I saw mapping to datetime2, is that what I need to use?

Thanks,
Ben
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

John TsioumprisSoftware & Systems Engineer

Commented:
Just to be "safe" just try either try SSMA or Access 2 MSSQL to convert a table with similar data to see what it happens.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> I'm using the wizard to import directly from a table, not sure where you want me to apply convert functions...

If that is the case, then I would suggest to use SSMA as mentioned by John above as well so that it will do the necessary basic conversions..
then I would suggest to use SSMA as mentioned by John
Isn't this what I'm currently using?
See attached.

Thanks,
Ben
Untitled.png
John TsioumprisSoftware & Systems Engineer

Commented:
Probably you have another issue...Access is much more forgiving when it comes to dates...i can't remember what are the ranges but i had a case with a table which had dates entered by users....rarely the user would type an invalid date...Access was fine but SQL would "crash " without any real info....so check your table for "strange" dates...like too big or too small or a slash missing or double..
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Okay, if you want to check this faster, then try exporting the records from Access tables to a SQL Server table with Date_Of_Birth column alone as varchar(30). Later we can check whether the data is a proper datetime datatype or not and then fix it out..

If you are good in SSIS, then you can create a simple Derived column transformation and then convert the source Access Date_Of_Birth column to appropriate style or regional setting so that SQL Server can save in the table without any issues.
https://mindmajix.com/ssis/how-to-use-derived-column-transformation-in-ssis-example
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
There I saw mapping to datetime2, is that what I need to use?

It probably should be DateTime.

DateTime2 only works as expected with the never "native" ODBC drivers.
Hi Experts,
When I tried with datetime2 it worked.

so check your table for "strange" dates...like too big or too small or a slash missing or double..
Can you send me a query for it, as I already tried not IsDate() and nothing showed up.

then try exporting the records from Access tables to a SQL Server table with Date_Of_Birth column alone as varchar(30).
Tried that and gave me conversion error msg.

It probably should be DateTime.
Guess we need a way to find the violating data as mentioned...

Thanks,
Ben
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Datetime2 when saved as varchar(30) should work without any issues, may I know what is the error you have encountered..
actually now I'm facing something new, cannot even perform what I did yesterday...
See attached.

Thanks,
'Ben
Untitled.png
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Error indicates that the access file is opened up by some user exclusively and hence SQL Server can't import from that..
Kindly close the Access file and try again..
Hi,

Yes I also thought that, and therefore made sure nobody has it open, in addition I tried with a copy of the Access file, and still same issue, going off my mind...

Thanks,
Ben
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Any other Import/Export wizard windows opened up?
Try to close all once or restart your machine and try once.. Hope you are doing this from a client machine and not at Server..
Hi,

Any other Import/Export wizard windows opened up?
Dont know of any other wizard.
Try to close all once or restart your machine and try once..
Already tried re-creating the Access file from scratch and import all objects, nothing helped.
I'm concerned perhaps what I was trying to do yesterday  -those errors- may have mess up things...
Will have to wait till Thu for restarting the pc.
Will keep you posted.
Hope you are doing this from a client machine and not at Server..
Yes, dont worry-:)

Thanks,
Ben
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Dont know of any other wizard.

I meant any other instance of Import/Export wizard from other clients or servers(while trying to check this..)
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
First, DateTime2 will only work with Access if using the SQL Server Native ODBC client:

Microsoft® ODBC Driver 17 for SQL Server®

If not, using the default "SQL Server" ODBC client, DateTime2 will be read as text, not date.

Second, dates should always be handled and stored as Date, not text.
Hi Experts,

I restarted the pc and still having this issue, see attached.
This seems to be a major problem.
Any suggestions?

@Gustav,
I agree, however having problems converting, see 2nd attachment.
Can you suggest some query to identify the culprit?




Thanks,
Ben
Untitled.png
Untitled--Recovered-.bmp
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Try to sort on the DateTime2 field and look for extreme values.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
If possible, try to provide a sample of your data so that we can also try to see the data once to understand the problems you are facing..
Hi Raja,

The main problem remaining is that for some reason I cannot import anything (as posted in attachment above).
Do you think its data related?

Thanks,
Ben
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
As the error states, your *.mdf file is opened up by some application and hence can't be opened exclusively by SQL Server..
Kindly check whether anyone else have opened up that *.mdf file and it is not data related..
No, this is a copy of the original file, nobody has that open.
also as mentioned, I created a new file and imported all tables from the other file and still have this issue...

Thanks,
Ben
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Okay, if your data isn't sensitive, then pls share us to check once locally..
Data is, perhaps can send table structure.
Meanwhile I just tried with several other Access files, not even related to this import (for testing) and all having the same issue.
Is it possible the SSMS wizard got corrupted?

Thanks,
Ben
Hi Experts,

I will be on vacation for the next 10 days...
If you have any suggestion please post it and will test upon return.
Have a nice weekend!

Thanks,
Ben
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Is it possible the SSMS wizard got corrupted?

Haven't heard about any issues like this..
Anyhow, try using the latest version of SSMS once(link provided below) or try applying SP4 for SQL Server 2008(if not applied already)
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017
Hi Experts,

Just returned from vacation...

or try applying SP4 for SQL Server 2008
Did that and didn't help.
Also having the same issue with SSMS version 2014.

FYI- I have 2 Access versions installed on my pc, 2003 and 2016, maybe this is causing the problem...

but still wondering how this didn't happen before, at the time I posted the question?!

Thanks,
Ben
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> FYI- I have 2 Access versions installed on my pc, 2003 and 2016, maybe this is causing the problem

Shouldn't be but try removing both and install 2016 Access drivers once to see whether it helps out or not..

>> but still wondering how this didn't happen before, at the time I posted the question?!

Kindly check by any chance your system got any Windows updates with respect to both 2003 or 2016 drivers recently causing your program not to work..
Hi,
Shouldn't be but try removing both
Would leave that as a last resort as I'm not sure where the orig installation CD is located...
See attached my recent win updates, don't see anything regarding office.
Thanks,
Ben
Untitled.png
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
The snapshot above is from Windows Updates..
Kindly request you to check once under Control Panel -> Programs and Features -> View Installed updates which will list the detailed updates..
Also absolutely nothing, see attached.

Thanks,
Ben
Untitled.png
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Hope you would have already checked but to double confirm, have you scrolled down to see whether anything was there for MS Office 2016 below..
interesting but I dont see office 2016 category listed there at all, however i do have that installed.

Thanks,
Ben
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> interesting but I dont see office 2016 category listed there at all, however i do have that installed.

Okay, if that is the case, then try reinstalling the drivers for Access 2016 from the below link(32/64 bit depending upon your environment)
https://www.microsoft.com/en-us/download/details.aspx?id=54920
Hi,
I'm not in the office today, will try next week.
Have a nice weekend!
Thanks,
Ben
Hi,
Are you referring to the below?
Microsoft Access Database Engine 2016 Redistributable
I tried both versions 32 bit and 64, none of them worked, see attached.
Thanks,
Ben
Untitled.png
Untitled1.png
Hi,
Any hope for this...?
Thanks,
Ben
John TsioumprisSoftware & Systems Engineer

Commented:
Probably there is a bit of mess up...my suggestion would to create a Virtual Machine on your desktop (Virtual Box,Vmware Player,HyperV choose your pick) ...install the OS and the bare minimum ...SSMA/Access Engine and try again.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> I tried both versions 32 bit and 64, none of them worked, see attached.

Nope, when you tried installing 32 bit it was saying 64 bit version is already installed and vice versa for 64 bit..
So, I would recommend uninstalling both and first try installing 32 bit version alone and see whether it helps or not.
If not, then uninstall 32 bit and then install 64 bit and try again..
Hi,
So, I would recommend uninstalling both
Are you referring for this update only, or uninstall office completely?
Thanks,
Ben
@john,
Probably there is a bit of mess up...
This is what I'm trying to figure out, what exactly got messed up and how?
my suggestion would to create a Virtual Machine on your desktop (Virtual Box,Vmware Player,HyperV choose your pick) ...install the OS and the bare minimum
This is something above my skill level, hope to find an easier solution.
Thanks,
Ben
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Are you referring for this update only, or uninstall office completely?

If there are no other dependencies, then uninstall office completely and test it or else just the Access drivers alone.
Since you mentioned that nothing is working properly in this machine, we can go with testing from the scratch itself..
Hi Raja,
I'm having our IT guy work on it, as if things needs to get uninstalled/installed back he is in charge.
I showed him what you have suggested.
Will keep you posted.
Thanks,
Ben
John TsioumprisSoftware & Systems Engineer

Commented:
I guess given the circumstances it would be better to completely uninstall everything from Office and start again...MS has a nice tool here  (option 2)...
But given the fact that you have extra IT support maybe he can help with the VM creation...
@John,
But given the fact that you have extra IT support maybe he can help with the VM creation...
Actually I showed him what you suggested as well, will see what he comes up with...
Thanks,
Ben
Hi Experts,
Just updating on this...
I had my IT guy trying to uninstall/reinstall both versions of office, and nothing seemed to help.
Then we realized the problem is actually happening at the server, so everything we did here locally was useless...
FYI- I showed your suggestion about VM creation and somehow he weren't so excited to go with that...
Any other suggestions?
Thanks,
Ben
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> I had my IT guy trying to uninstall/reinstall both versions of office

just to double confirm, you have tried with 32 bit version first and then did a clean uninstall and then installed 64 bit version..
If so, then try this one once..
1. Do a complete uninstall of everything(32/64 bit installations)
2. Install 32 bit version of Office
3. Install 64 bit drives alone on top of it..
Hi,
Okay will forward these to my IT guy.
Just keep in mind, all that has to be done on the server, while we only tried on my local pc...
Thanks,
Ben
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Just keep in mind, all that has to be done on the server, while we only tried on my local pc...

Sorry, did you meant that you have tried this on your local server only and not the Server??
If so, kindly let us know whether you have access to the server and can be tested or not..
John TsioumprisSoftware & Systems Engineer

Commented:
I hate to repeat but unless you test it on a clean machine (VM ?) you won't have a clear idea of what is causing the issues.....do remember that a single "bad" registry key can cause havoc while everything seems great.
Hi Experts,
looks like our IT finally figured it out...
I was entering my SQL username and PWD
however he suggested to use admin with blank pwd (see attached)
and only enter my SQL credentials and the second prompt...
I just listen and its seems to work...
No idea how it first worked...
what do you say for that?
Thanks,
Ben
Untitled.png
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
Yes, default password for Access mdb file is blank if not password protected..
Thanks to all participants!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial