X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Connecting Ssis To Access 2016 (.accdb Format), Access 2016    
post Feb 24 2019, 02:20 PM

Posts: 3,567
Joined: 27-February 09

Does anybody know which provider type to use to create a functional Source for an SSIS data flow? The Jet4.0 one worked fine, but what's the ACCDB version of that? I was watching videos on Pluralsight on SSIS, but they're from maybe 2011. (Time for an update, maybe?)

I downloaded & installed the X64 Database Engine stuff, but still no joy.

(Interesting that even MSFT treats Access like some unwanted stepchild.)
Go to the top of the page
post Apr 3 2019, 08:57 PM

Posts: 33
Joined: 3-April 19

Did you install the correct database engine for your version of office (32 or 64 bit).

You can use an existing odbc connection or also use the OLE Provider MS Office (XX.X) Access Database Engine OLE Provider that is installed with the database engine.

I remember having to set the debugging - Run64BitRuntime to false to correct a debugging issue I was having, but this was a bug in an earlier version of SSIS.

Go to the top of the page
post Feb 25 2020, 06:25 PM

Posts: 3,567
Joined: 27-February 09

Got it kinda working. The snag was that Access stores text as NVARCHAR() and most text fields in SQL Server are VARCHAR(), so you have to convert the source before inserting it into a SQL table. Just another one of those things that Access hides. =(
Go to the top of the page
post Feb 25 2020, 09:10 PM

Posts: 799
Joined: 21-September 14
From: (MilitaryBrat) Tampa Bay, Florida, USA

I recall in a recent related SSIS thread that the provider for an accdb has never worked for anyone, but the Jet for mdb's does.


Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix, Oracle & PostgreSQL db's.
Go to the top of the page
post Feb 25 2020, 09:18 PM

Posts: 3,567
Joined: 27-February 09

LOL. That's the impression I got from posting on SQLServerCentral. I guess one option is using DAO and saving it as an MDB file? Just seems really odd, that's all.
According to Phil Parkin, the solution is to create the link to the ACCDB file, and then insert a conversion step in the SSIS package to cast the weird double-byte strings from Access to single-byte. (VARCHAR2 to VARCHAR). Then everything works fine. I guess I won't give up using Access as a temporary cleaner just yet... I can do horrible things like read the columns of the table and make decisions based on that... <g>
Go to the top of the page
post Mar 2 2020, 03:46 PM

Posts: 3,567
Joined: 27-February 09


Any clue how to cast an Access text data type to VARCHAR and not VARCHAR2? I've used a bunch of the C functions like CBool(), CInt(), etc... is there one for a VARCHAR type?

I think I just answered my own question...

This post has been edited by MadPiet: Mar 2 2020, 04:03 PM
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    3rd April 2020 - 02:15 AM