Importing Access ACCDB files into a SQL Server table through SSIS

• pietlinden

  SSC Guru

  Points: 63584

  More actions

  February 24, 2020 at 2:21 am

  #3728043

  I'm really terrible at SSIS, so I figured I would try something simple.

  I imported data from a text file using the text connection manager, and it worked just fine. Same with importing from an MDB file - used Jet4.0 and it worked like a champ.  When I try to import from Access ACCDB files, it all goes to hell in a handbasket. It's odd, because now the text columns are of type NVARCHAR() in Access(?).  If I try to import into a regular VARCHAR(n) column, it fails and I get a conversion error. How do I convert from NVARCHAR to VARCHAR in SSIS? (Am I missing something obvious, or is this just a huge hassle?) What's super odd is that I can push the data from Access and it does the conversion under the covers apparently, because the inserts work. I could mimic SSIS by doing something like this:

  1. Create a "front end" database with a connection to the SQL Server tables I want to insert into, and linked tables to the Access tables I want to import.
  2.  use VBA to change the connection string of the connected tables.
  3.  run the append query
  4.  repeat steps 2-3 in a loop until all the files are processed.

  Has anyone here gotten SSIS to read from Access ACCDB tables? If so, could I have a hint? It's just baffling.

• Phil Parkin

  SSC Guru

  Points: 247159

  More actions

  February 24, 2020 at 3:51 pm

  #3728243

  I've not tried importing an accdb before, so can't help with specifics.

  But converting NVARCHAR to VARCHAR is straightforward enough – a Data Conversion component is what you need. This will allow you to take an NVARCHAR 'input column' and create a suitably converted 'output column', which can be mapped to your destination column in place of the NVARCHAR version.

  ---

• pietlinden

  SSC Guru

  Points: 63584

  More actions

  February 24, 2020 at 8:06 pm

  #3728384

  Oh, okay. I'll try it out. Thanks, Phil!