Post reply

MS Access db to SS05 using SSIS

  • May 1, 2007 at 9:22 am

    #119848

    I can load table by table from MS Access to SS05.

    Is there a way to load the whole MS Access DB into SS05 in one shot?  I have a MS Access 2000 db with over 200 tables.  I don't want to have to define each table.  I want the tables in SS05 to be created each time or tables truncated then dropped.

    Any one know the object to use?

     

    Thanks,

    Joseph

  • May 1, 2007 at 10:03 am

    #703586

    use BIDS to create an SSIS package.You can specify from Access to SS05.you may need to scirpt the truncate. good luck.

  • May 1, 2007 at 10:05 am

    #703588

    How about using Upsize wizard from Access itself? The advantage is the wizard takes care of integrity constraints.

    hth

    Thanks,

    JP

  • May 1, 2007 at 10:31 am

    #703602

    What is BIDS?

    I will look at the upsize wizard.  Did not know about that.

     

    Joseph

  • May 1, 2007 at 10:35 am

    #703606

    I am using SSIS.  When I look at the objects for control flow and data flow, I don't see a way to get the whole access database.  The Database Task is for SQL Server database. 

    I have created my connections in connection manager.

    What role does the Data Sources play?  I have done a couple quick tables from flat and mdb to ss05, and not used the data sources or data views in the Solution Explorer.

  • May 3, 2007 at 7:24 am

    #704056

    FYI:  By right clicking on the SSIS package, there is a wizard (nice).  You can select the whole mdb database then select the tables you want to import.  It has worked nicely.

    I have not found a way to bulk add the tables through the wizard or other methods to an existing project.  I have 20 tables in 1 project and want to add 30 more out of the 100 tables in the mdb database.  Any one have a hint on this?

     

    Joseph

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

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