SQLTeam.com | Weblogs | Forums

Need to create a package for moving data from one SQL Server database to another

I am new to SSIS. I have a requirement to create a package for moving data from one SQL Server database to another.There are about 30 tables in both the DBs. Need to update if matches are found for record in the table & insert if no match is found. Kindly explain how this can be done in detail.

Is the second db production level or non production ?
How often should the synch happen?

Both the DBs are non production level. Both the DBs are part of the same DB server.

There is a EmployeeAllocationDB which has EmployeeMember table & EmployeeSector table. & other 30 tables.

EmployeeMember


MemberId - PK,

MemberCode,

Name,

SectorId - FK

EmployeeSector


SectorId - PK,

SectorCode,

Name

There are four sectors namely Technical, Non technical, Hardware, Software.

We need to move all the records of tables in EmployeeAllocationDB to EmployeeTech who are belonging to sectorcode = 1 (technical).

There are some other tables in EmployeeAllocationDB who are not having any FK relationship using MemberID or SectorID. For those tables all the records need to be moved to EmployeeTech.

This is the scenario.