none
Export data using SSIS to create another database

    Question

  • Hi,

     

    I need create one database (Access, for instance) from SQL Database, using SSIS. I don't want to use the tool Import/Export Wizard, because I will need to have the same structure between the two databases. For example, I desire to create a table in a MS Access Database from one another table already exists in sql server database.

     

    It's possible?

     

    thanks

    Monday, August 25, 2008 8:02 PM

Answers

  • Yes, it's possible. Most things are, given enough effort.

     

    However, how much effort it will take could vary wildly, depending on your requirements. If this is a one-time process, simply create a data flow, add a OLE DB Source that points to your Access table, then add a OLE DB Destination that points to the SQL Server. Click the Create Table button, and you should see a script that will create a table that matches the original Access table.

     

    If you want to make this dynamic (that is, it will work with any table), you will have to write a program that creates SSIS packages programmatically, or use a different tool. To see a sample of a program to create packages, see the PkgGen sample here: http://www.codeplex.com/SQLSrvIntegrationSrv

     

    Tuesday, August 26, 2008 12:31 AM