SSIS - Data Migration And Filtering (2)

Scenario:
OK.. My last scenario, I forget to tell you that we have Users table which connected to UserTypes. Which means every user has a specific user type.

Could you please re-migrate my data depends on those requirement?


Answer:
Yes of course.
By this way, we going to migrate a relational data base.

Just setting up a plan to now what we should do.
1. We have lookup table called UserTypes should migrate his data first to my new table EmployeeTypes.
2. Then, do migration for the second table Users to the new table Employees.

Here we go.

Those are tables should migrate:

Note the new column added (UserTypeID).

We create new table to migrate UserTypes called EmployeeTypes and edit our old table Employees and add a new column called EmployeeTypeID:

New, we open the same integration project we have created in  SSIS - Data Migration And Filtering (1)

and do the following steps:


STEP -1:
1. Double click 'Package.dtsx'.
2. In 'Control Flow' area, go to toolbox and drag 'Data Flow Task', you can find it under 'Control Flow Items', the drop it to 'Control Flow' area.
3. Rename 'Data Flow Task' to 'UserTypes_EmployeeTypes'
4. Connect with green line 'UserTypes_EmployeeTypes' with 'Integration'.

STEP -2:
1. Double click to 'UserTypes_EmployeeTypes' to move to its data flow area.
2.from toolbox, drag 'OLE DB Source' and drop it to 'Data Flow' area.
3. Rename 'OLE DB Source' to 'UserTypes'
4. Double click to 'UserTypes' for configuration.
5. Select the connection created to the old database as a value for 'OLE DB connection manager', then select 'Table or View' from drop down list of 'Data access mode' and select table '[dbo].[UserTypes]' from drop down list of 'Name of the table or the view'.

6. Press OK button.




STEP -3:
1.from toolbox, drag 'OLE DB Source' and drop it to 'Data Flow' area.
2. Rename 'OLE DB Source' to 'EmployeeTypes'
3. Connect with green line 'UserTypes' with 'EmployeeTypes'.

4. Double click to 'EmployeeTypes' for configuration.
5. Select the connection created to the new database as a value for 'OLE DB connection manager', then select 'Table or View - fast load' from drop down list of 'Data access mode' and select table '[dbo].[EmployeeTypes]' from drop down list of 'Name of the table or the view'.

6. From the left menu, select 'Mapping' and draw relation between the following:
a. UserID - EmployeeID
b. TypeName - EmployeeTypeName

7. Press OK  button.



By the above steps, we created migration for 'UserTypes' table.
The next step just back to 'Control Flow' area and assign 'UserTypeID' with 'EmployeeTypeID' in 'Integration'.

The final result will be like the following: