Importing Access ACCDB files into a SQL Server table through SSIS

  • 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.

  • 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.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

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

Viewing 3 posts - 1 through 2 (of 2 total)

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