Programmabilities Blog

An SSIS to copy tables from a Source to a Destination. Directions:

1. From Start, select SQL Server Business Intelligence Development Studio
2. File
   1. New Project…
      1. Project types: Business Intelligence Projects
      2. Integration Services Project…
      3. Click “OK”
3. Toolbox
   1. Drag "Transfer SQL Server Objects Task" to "Control Flow" tab's pane.
      1. Right-click choose Edit…
         1. Objects
            1. SourceConnection ‹New connection…› = NWDSQL
            2. SourceDatabase = NIS_empl_wage
            3. DestinationConnection ‹New connection…› = TESTSQL
            4. DestinationDatabase = NIS_empl_wage
            5. CopyData = True
            6. ExistingData = Replace (so not append (dup errors))
            7. ObjectsToCopy (expand)
               1. CopyAllTables = True (so not sprocs)
            8. Note: Leave all other options False. Ex., Table Options—Can leave all these False because it will just move data so Destination’s Primary Keys will remain.
      2. Click “OK”
4. Save and Run

Note 1: Your Properties for the Transfer SQL Server Objects Task will now look like this:

1. CopyAllTables = True (so not sprocs)
2. CopyData = True
3. DestinationConnection = TESTSQL
4. DestinationDatabase = NIS_empl_wage
5. ExistingData = Replace (so not append (dup errors))
6. SourceConnection = NWDSQL
7. SourceDatabase = NIS_empl_wage

Note 2: A DB "restore" by the DBA would nix Destination tables that do not exist in Source tables; but not this technique.

Labels: sql

- posted by programmabilities @ 12:28