Help Loading data from Access DB File to SQL Server using SSIS

Question

Post reply

Help Loading data from Access DB File to SQL Server using SSIS

kashyap4007

SSC Eights!

Points: 859
More actions
November 28, 2018 at 8:05 am

#409343

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.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246978 More actions · Answer 1

Have you got the file open in Access when you try to read it from SSIS?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kashyap4007 SSC Eights! Points: 859 More actions · Answer 2

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)

Phil Parkin SSC Guru Points: 246978 More actions · Answer 3

kashyap4007 - Wednesday, November 28, 2018 8:29 AM
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)

Maybe someone else has the time, but I'm afraid that I do not.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

kashyap4007 SSC Eights! Points: 859 More actions · Answer 4

Sorry If I meant it in the wrong way.
Jut trying to understand If I am doing the right way or not.

pietlinden SSC Guru Points: 63506 More actions · Answer 5

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?

kashyap4007 SSC Eights! Points: 859 More actions · Answer 6

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.

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 7

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.

kashyap4007 SSC Eights! Points: 859 More actions · Answer 8

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.

Sreekanth B SSCertifiable Points: 6145 More actions · Answer 9

Sreekanth B

SSCertifiable

Points: 6145

November 28, 2018 at 10:40 am

#2014848

Glad I could help.