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).
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.
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)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.
Take a look here for Ms Access to MSSQL datatypes mappings
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 functionsI'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 mappingsThere I saw mapping to datetime2, is that what I need to use?
Thanks,
Ben
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.
>> 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..
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..
ASKER
then I would suggest to use SSMA as mentioned by JohnIsn't this what I'm currently using?
See attached.
Thanks,
Ben
Untitled.png
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..
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
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
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.
ASKER
Hi Experts,
When I tried with datetime2 it worked.
Thanks,
Ben
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
Datetime2 when saved as varchar(30) should work without any issues, may I know what is the error you have encountered..
ASKER
actually now I'm facing something new, cannot even perform what I did yesterday...
See attached.
Thanks,
'Ben
Untitled.png
See attached.
Thanks,
'Ben
Untitled.png
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..
Kindly close the Access file and try again..
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
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
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..
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..
ASKER
Hi,
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.
Thanks,
Ben
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
>> 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..)
I meant any other instance of Import/Export wizard from other clients or servers(while trying to check this..)
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.
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.
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
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
Try to sort on the DateTime2 field and look for extreme values.
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..
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
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
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..
Kindly check whether anyone else have opened up that *.mdf file and it is not data related..
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
also as mentioned, I created a new file and imported all tables from the other file and still have this issue...
Thanks,
Ben
Okay, if your data isn't sensitive, then pls share us to check once locally..
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
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
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
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
>> 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
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
ASKER
Hi Experts,
Just returned from vacation...
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
Just returned from vacation...
or try applying SP4 for SQL Server 2008Did 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
>> 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..
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..
ASKER
Hi,
See attached my recent win updates, don't see anything regarding office.
Thanks,
Ben
Untitled.png
Shouldn't be but try removing bothWould 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
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..
Kindly request you to check once under Control Panel -> Programs and Features -> View Installed updates which will list the detailed updates..
ASKER
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..
ASKER
interesting but I dont see office 2016 category listed there at all, however i do have that installed.
Thanks,
Ben
Thanks,
Ben
>> 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
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
ASKER
Hi,
I'm not in the office today, will try next week.
Have a nice weekend!
Thanks,
Ben
I'm not in the office today, will try next week.
Have a nice weekend!
Thanks,
Ben
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
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
ASKER
Hi,
Any hope for this...?
Thanks,
Ben
Any hope for this...?
Thanks,
Ben
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.
>> 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..
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..
ASKER
Hi,
Thanks,
Ben
So, I would recommend uninstalling bothAre you referring for this update only, or uninstall office completely?
Thanks,
Ben
ASKER
@john,
Thanks,
Ben
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 minimumThis is something above my skill level, hope to find an easier solution.
Thanks,
Ben
>> 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..
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..
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
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
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...
But given the fact that you have extra IT support maybe he can help with the VM creation...
ASKER
@John,
Thanks,
Ben
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
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
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
>> 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..
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..
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
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
>> 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..
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..
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.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to all participants!
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