Avatar of bfuchs
bfuchs
Flag for United States of America asked on

Error while importing Access table to SQL.

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.
Microsoft AccessMicrosoft SQL ServerSQL

Avatar of undefined
Last Comment
bfuchs

8/22/2022 - Mon
Raja Jegan R

>> 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 Tsioumpris

Take a look here for Ms Access to MSSQL datatypes mappings
bfuchs

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
John Tsioumpris

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 R

>> 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..
bfuchs

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
John Tsioumpris

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 R

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
Gustav Brock

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
bfuchs

ASKER
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 R

Datetime2 when saved as varchar(30) should work without any issues, may I know what is the error you have encountered..
bfuchs

ASKER
actually now I'm facing something new, cannot even perform what I did yesterday...
See attached.

Thanks,
'Ben
Untitled.png
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Raja Jegan R

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..
bfuchs

ASKER
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 R

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..
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
bfuchs

ASKER
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 R

>> 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..)
Gustav Brock

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bfuchs

ASKER
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
Gustav Brock

Try to sort on the DateTime2 field and look for extreme values.
Raja Jegan R

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..
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
bfuchs

ASKER
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 R

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..
bfuchs

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Raja Jegan R

Okay, if your data isn't sensitive, then pls share us to check once locally..
bfuchs

ASKER
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
bfuchs

ASKER
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Raja Jegan R

>> 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
bfuchs

ASKER
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 R

>> 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..
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bfuchs

ASKER
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 R

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..
bfuchs

ASKER
Also absolutely nothing, see attached.

Thanks,
Ben
Untitled.png
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Raja Jegan R

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..
bfuchs

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

Thanks,
Ben
Raja Jegan R

>> 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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bfuchs

ASKER
Hi,
I'm not in the office today, will try next week.
Have a nice weekend!
Thanks,
Ben
bfuchs

ASKER
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
bfuchs

ASKER
Hi,
Any hope for this...?
Thanks,
Ben
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
John Tsioumpris

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 R

>> 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..
bfuchs

ASKER
Hi,
So, I would recommend uninstalling both
Are you referring for this update only, or uninstall office completely?
Thanks,
Ben
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
bfuchs

ASKER
@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 R

>> 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..
bfuchs

ASKER
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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
John Tsioumpris

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...
bfuchs

ASKER
@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
bfuchs

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Raja Jegan R

>> 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..
bfuchs

ASKER
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 R

>> 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..
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
John Tsioumpris

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.
bfuchs

ASKER
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
ASKER CERTIFIED SOLUTION
Raja Jegan R

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
bfuchs

ASKER
Thanks to all participants!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.