Export data using SSIS to create another database


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



    Monday, August 25, 2008 8:02 PM


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


    Tuesday, August 26, 2008 12:31 AM