Source to Destination Transfer

Question

Source to Destination Transfer

durga palepu

Say Hey Kid

Points: 701
More actions
August 22, 2011 at 4:54 am

#243550

A SQL Server table has columns source, destination tables and Primary Key where source and destination tables have same structure. How to transfer data from source to destination using SSIS?
The table structure is as listed below
SourceTable DestinationTablePrimaryKey
tabSouceA tabDestA pktabA
tabSouceB tabDestB pktabB
tabSouceC tabDestC pktabC
tabSouceD tabDestD pktabD
Any suggestions are helpful to me
Thanks in advance,
Durga

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

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

rgtft Hall of Fame Points: 3518 More actions · Answer 1

durga palepu (8/22/2011)
A SQL Server table has columns source, destination tables and Primary Key where source and destination tables have same structure. How to transfer data from source to destination using SSIS?
The table structure is as listed below
SourceTable DestinationTablePrimaryKey
tabSouceA tabDestA pktabA
tabSouceB tabDestB pktabB
tabSouceC tabDestC pktabC
tabSouceD tabDestD pktabD
Any suggestions are helpful to me
Thanks in advance,
Durga

Is the table you describe a type of audit table that just lists the records that were copied?

Or are you trying to automate a copy of data where you define the source and destination and PK in a separate table?

durga palepu Say Hey Kid Points: 701 More actions · Answer 2

Hi rgtft,

Its a master data mart table which contains source, destination tables and respective primary key columns.

The requirement is to transfer data from source table to destination table using SSIS.

Thanks,

Durga

rgtft Hall of Fame Points: 3518 More actions · Answer 3

durga palepu (8/22/2011)
Hi rgtft,
Its a master data mart table which contains source, destination tables and respective primary key columns.
The requirement is to transfer data from source table to destination table using SSIS.

I guess I'm still not totally understanding, but if the source and destination tables are always the same, just use a Data Flow taks that contains an OLE DB Source transformation and an OLE DB Destination task to transfer the data. You could do a multi-cast to write out the PKs to your master data table.

So you are copying your data as well as adding the records to your master data table. Look at the attached .jpg showing an example SSIS package.

HTH,

Rob

durga palepu Say Hey Kid Points: 701 More actions · Answer 4

Dear Rob,

I think I got the solution.

The approach we have to follow is to use Foreach loop container with Foreach ADO Enumerator and a Data Flow Task inside the Foreach loop. The primary key is to identify the new rows and existing rows between source and destination table i.e. to do the incremental loading.

Anyway you have certainly helped me to think the solution and what you have suggested will help in add or update the source, destination tables information to the master table.

Thanks a lot,

Durga.