Post reply

Exporting tables in an MS Access database to SQL SERVER using SSIS

  • May 31, 2009 at 9:58 pm

    #132709

    Hi All,

    I want to export all the tables in an MS Access database to SQL Server tables using SSIS. I've to create a single package for that.

    My questions are how can we loop through all the tables in an Access database.

    Can we achieve this by using Foreach loop container if so which property do I need to select.

    Or is there any other way of achieving this.

    Thanks for your help in advance.

  • June 1, 2009 at 12:40 am

    #1002573

    Does it have to be SSIS? Have you thought about using the Access upsizing wizard?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • June 1, 2009 at 5:52 am

    #1002667

    Hi Thanks for your reply. It has to be a SSIS package..We are taking each table and comparing with another table in the database. Since comparison cannot be done in Access wizard it has to be SSIS package.

    Thanks in advance

  • June 25, 2009 at 7:06 am

    #1015275

    I have the same question. Is my only option to first export the Access table to a .txt file then import the .txt file into my SQL table?

  • December 1, 2015 at 6:50 am

    #1843181

    Hello,

    Yes, you can achieve this easily using a Foreach Loop and an ADO.NET connection.

    1) Drag a Foreach Loop on your Control Flow.

    2) Click once on your Foreach Loop and create a new variable named TableName of the type String.

    3) Edit your Foreach Loop and go to the Collection section.

    4) Set the Enumerator to "Foreach ADO.NET Schema Rowset Enumerator".

    Under Enumerator Configuration set:

    5) the connection to the ADO.Net Connection which points to your .accdb-file. (or create a new one by choosing "New Connection" from the list)

    6) Select "Tables" as Schema

    Under Variable Mappings:

    7) Choose the variable "User::TableName" and assign index 2

    For each iteration through the loop, the variable TableName will contain the name of a table, which you can use within the scope of the Foreach Loop.

Viewing 5 posts - 1 through 4 (of 4 total)

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