Help Loading data from Access DB File to SQL Server using SSIS
-
Hi,
I am trying to load data from this state website URL.
http://cogcc.state.co.us/data2.html#/downloads
This website has Production data zipped on yearly basis.
I want to load the data for the present year every day to SQL Server( Will load new data and update any changes accordingly)
I am trying to load this using SSIS.
When I manually download the zip file and unzip it, I see the data is stored in Access DB File.When I try to load this using SSIS I am getting the below error.
Can someone help/guide me on this, please
FYI... I tried using below two providers
FYI.. When I used the Jet 4.0, I am getting the below error.
-
Have you got the file open in Access when you try to read it from SSIS?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
No, the file is not open.
Can you try to download once and try if you have a chance please( it is a public website and anyone can download the files) - kashyap4007 - Wednesday, November 28, 2018 8:29 AM
Maybe someone else has the time, but I'm afraid that I do not.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Sorry If I meant it in the wrong way.
Jut trying to understand If I am doing the right way or not. - kashyap4007 - Wednesday, November 28, 2018 8:05 AM
Hi,
I am trying to load data from this state website URL.
http://cogcc.state.co.us/data2.html#/downloads
This website has Production data zipped on yearly basis.
I want to load the data for the present year every day to SQL Server( Will load new data and update any changes accordingly)
I am trying to load this using SSIS.
When I manually download the zip file and unzip it, I see the data is stored in Access DB File.When I try to load this using SSIS I am getting the below error.
Can someone help/guide me on this, please
FYI... I tried using below two providers
FYI.. When I used the Jet 4.0, I am getting the below error.
Did you unzip the file in SSIS before trying to read it?
-
I did unzip it manually to test it first.
If the load process works then I know how to download and unzip it using SSIS.
Please let me know If I did not answer your question properly.Thank you.
-
What happens if you try using simple Import data wizard(Right click on your DB->Tasks->Import data) from your SSMS and use "Microsoft Access(Microsoft Access database engine)" as your source type? (That would use "Source Provider : Microsoft.ACE.OLEDB.12.0" if you running 2014.) On Ad-hoc basis, That's the quick and dirty way I import data from an MDB file into SQL Server and I never ran into issues IIRC.
-
Thank you Sreekanth.
I did the way you suggested and saved the SSIS package and it is working now.
Not sure what went wrong when I created the Connection manually.
Really Appreciate your help.FYI... I see the connection manager and It is the same as now and before but it did not work when I manually created it.
-
Glad I could help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply