Connection to Access in SSIS

Question

Connection to Access in SSIS

Gaurav Khanna-460823

SSC-Addicted

Points: 476
More actions
July 7, 2007 at 3:54 am

#117250

I want to create a package programatically in VB.NET which should have Access as Destination and SQL Server as Source.
But I am getting error in AcquireConnection() method for Access Connection Manager. I am using OLEDB as connection Type and my connection string is "Data Source=C:\ACCESSDB.mdb;OLE DB SERVICES=0;Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;"
What am I doing wrong in Connection for Access?

Viewing 7 posts - 1 through 7 (of 7 total)

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

WILLIAM MITCHELL SSChampion Points: 13766 More actions · Answer 1

I would suggest creating a Microsoft Data Link file (UDL) to build & test your connection string. Take a look at this blog post, maybe it will help:

http://wvmitchell.blogspot.com/2007/02/connection-strings.html

Once you have that working, you can copy & paste the string out of the UDL into your application.

EDIT: I haven't worked with vb.net but in C#, if you have a backslash in the connection string you have to either use the @ prefix, or double the backslash like

@"C:\connection_string_etc")

or

"C:\\connection_string_etc"

Something else to try.

Gaurav Khanna-460823 SSC-Addicted Points: 476 More actions · Answer 2

This is also giving the same error. Error on AcquireConnection.

Gaurav Khanna-460823 SSC-Addicted Points: 476 More actions · Answer 3

It worked now.

I was using 64 bit computer and 64 bit computer doesn't register Jet Provider 4.0. So need to run in x86 mode.

test-383770 SSC Rookie Points: 25 More actions · Answer 4

Could you please specify how you run in x86 mode on 64 bit computer for Jet Provider 4.0?

cvapl2008 Valued Member Points: 65 More actions · Answer 5

cvapl2008

Valued Member

Points: 65

March 24, 2009 at 1:58 pm

#965576

Could you let us know how you run x86

COZYROC One Orange Chip Points: 28499 More actions · Answer 6

cvapl2008 (3/24/2009)
Could you let us know how you run x86

If you use SQL 2005, in your SQL Job Step you have to select CmdExec execution step, instead of using the SQL Server Integration Services job step. Then go to the command line parameter and select the DTEXEC application located under:

\\\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn

This is the 32bit SSIS package executable you have to use with older providers like the Jet (Access) provider.

In SQL 2008 is much simplified. You have an option in the job step dialog where you can select to use the 32bit executable.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/