Assignment

Is this the process you used to do assignment.

STAGE TO 3NF

The following in class projects will prepare you to load your 3NF tables from your stage tables.

In class project part I - This example loads your stage STORE table and your 3NF REGION table. It uses a source qualifier to remove the duplicates.

1. Create the 3NF REGION table. Make sure this table has the correct PK.

2. Create the STAGE store table.

3. Create a SQL task in the control flow to truncate the 3NF REGION table. Make sure you give the SQL task a descriptive name.

4. Create a data flow task to load the STORE stage table from the STORE flat file. Make sure you use add a precedent constraint to ensure the SQL task completes before you execute this control flow. Also make sure you give this task a descriptive name.

5. Now execute this workflow. Make sure is executes correctly.

6. Now to SQL Server Management studio and duplicate all the rows in the store STAGE table. Do this by executing a SELECT INSERT statement.

7. Now modify your workflow by creating another data flow task. This data flow task will load your REGION 3NF table. For this data flow I want you to use a SQL SELECT statement with the keyword distinct in your OLE DB source to remove duplicates before you insert into the 3NF REGION table. Also make sure you have given this data flow task a descriptive name.

8. Execute the workflow.

9. Verify that your 3NF REGION table has all the correct rows.

In class project part II - This example uses a sort transformation instead of a source qualifier to remove the duplicates.

10. Modify the workflow you create in part one (above). Remove the distinct select query from your OLE DB Source.

11. Now to remove the duplicates I want you to add a sorter transformation and use the sorter transformation to remove the duplicates. Give the sorter transformation a descriptive name.

12. Execute the workflow.

13. Verify that your 3NF REGION table has all the correct rows.

In class project part III - Now we will learn about the multicast transformation. We will use the multicast transformation to duplicate the data path and load the STATE and REGION 3NF tables.

14. Modify the workflow you created in part two. Remove the sorter transformation.

15. Add another SQL task in the control flow to delete the rows from the NF3 STATE table.

16. Add a multicast transformation to the workflow where the sorter transformation was. Use the multicast transformation to split the data flow into two paths. One path will include the attributes for the REGION table and the other path will contain the attributes for the STATE table. Make sure you give a descriptive names to the multicast transformation.

17. Add two sorter transformations to remove duplicates for both data paths (STATE and REGION). Also make sure you give the sorter transformations descriptive names.

18. Now that you have removed the duplicates insert the rows into both the STATE and REGION 3NF tables.

19. Execute your workflow.

20. Verify the REGION and STATE tables are populated.

In class project part IIII - Now you will use a stored procedure instead of a multicast transformation to load your STATE and REGION 3NF tables.

21. Disable all the data flow and SQL tasks in your project.

22. Create a stored procedure that will delete the 3NF REGION and STATE tables.

23. Then insert the 3NF REGION and STATE tables.

24. Execute the stored procedure from a SQL Task in your control flow.

25. Execute your workflow.

26. Verify the 3NF state and region tables are populated.