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:

SSIS - Data Migration And Filtering (1)

Scenario:
We have DB holds users information.The Users table includes a lot of dirty data like null or empty values. We need to extract dirty value to fix it and save the correct data to new database and migrate users data with employee data in new table. Could you do it?

Answer:
YES.. we can do it by multiple ways like writing script and execute it. But we will try another way to do that which is by using SSIS(SQL Server Integration Services).

Here we go.

First of all, we look at our main table:

and this is data of the table:

We create our new DB, let us call it  which going to hold the correct data with the following information:
- ID(int) Auto Increment.
- Name(nvarchar(50)) NOT NULL.
- Email(nvarchar(50)) NOT NULL.

Now, we create new SSIS project call it 'MyIntegration':

After project created, from solution explorer, under SSIS packages folder, double click to Package.dtsx

from tools window, drag 'Data Flow Task', you can find it under 'Controls Flow Items', and drop it to 'Control Flow' area. Then write click to our 'Data Flow Task' and select 'Rename' and change name to 'Integration'

Now, double click to 'Integration'. You will see that you navigate to 'Data Flow' area.

In this area do the following:

STEP - 1:


1. From 'Data Flow Sources' in toolbox, drag 'OLE DB Source' and drop it to 'Data Flow' area.
2. Rename 'OLE DB Source' to 'Source'.
3. Double click to 'Source'then make a new connection to the 'OldDb'. Select 'Data Access Mode' from drop down list 'Table Or View' and select '[dbo].[Users]' from 'Name of the table or view' drop down list:

STEP -2:
1. From 'Data Flow Transformations' in toolbox, drag 'Data Conversion' and drop it to 'Data Flow' area.
2. Rename 'Data Conversion' to 'Converter'.
3. Connect with green line 'Source' with 'Converter'.
4. Double click to 'Converter'and change the data type of the column to 'Unicode string[DT_WSTR]':

STEP -3:
1. from 'Data Flow Transformations' in toolbox, drag 'Lookup' and drop it to 'Data Flow' area.
2. Rename 'Lookup' to 'Switcher'.
3. Connect with green line the 'Converter' with 'Switcher'.
4. Double click to 'Switcher' and select 'Redirect rows to no match output' in 'Specify how to handle rows with no matching entries' drop down list:

5. In the section 'Connection' select connection you made in the first step to 'OldDB'. Then, select 'Use result of an SQL query' and write the following query:

SELECT ID, Name, Email
FROM dbo.Users
WHERE (Name <> '') 
AND   (Email <> '')

6. In the section 'Columns' drow relation between:
  a. Name - Converter.Name
b. Email - Converter.Email
then press OK button:

STEP -4:
1. from 'Data Flow Destinations' in toolbox, drag 'OLE DB Destination' and drop it to 'Data Flow' area.
2. Rename 'OLE DB Destination' to 'Destination'
3. Make a green connection between 'Switcher' and 'Destination'. Select 'Lookup Match Output', which means we going to hold correct data to this DB.
4. Double click to 'Destination' then create new connection to the new database 'NewDB'. Then select 'Table or view - fast load' from 'Data accress mode' drop down list and select '[dbo].[Employee] ' from 'Name of the table or view' drop down list:

5. From 'Mappings' section, make a relation between:
a. EmployeeName - Converter.Name
b. EmployeeEmail - Converter.Email

then press OK button:

STEP -5:
1. from 'Data Flow Destinations' in toolbox, drag 'Flat File Destination' and drop it to 'Data Flow' area.
2. Rename 'Flat File Destination' to 'Dirty Data'.
3. Make OTHER green connection between 'Switcher' and 'Dirty Data'
4. Double click to 'Dirty Data' and create new 'Flat File Connection Manager'. After clicking to button 'New...' select 'Delimited'. Then set 'Connection Manager Name' value to 'InvalidData'. After that click on 'Browse...' then select where you would like to put text file and name it as 'InvalidData', then press OK:

5. From 'Mappings' section, make relation between the following:
a. ID - Source.ID
b. Name - Source.Name
c. Email - Source.Email

then press OK button:

The last design going to be like the following:

Now, click of 'Start Debugging' from 'Debug'.
Then, you can see the value inserted in our 'NewDB':

and the dirty data in the 'InvalidData.txt' file: