Assignments

Assignment – 1 ETL with SSIS (Data Flow)

1. Create an SSIS package that merges 4 different sources into one destination table
2. Create an SSIS package that stores maximum & minimum marks from each subject of BCA semester 6 of a student list (Data Warehousing, Android, Python)
3. Create an SSIS package that creates two new columns as “Firstname” & “Lastname” from “Fullname” column of a student data & store all these columns in a destination table
4. Create an SSIS package that generate total of all three subjects of individual records (for each student) & percentage. Store these columns in a destination table (Student name, Sub1, Sub2, Sub3, Total, Percentage)
5. Create an SSIS package that generate a new column after giving 10% of discount on a given product price. Store them in a destination table (Product name, Product Price, Discounted Price). You have to use copy column transformation to create a copy of product price first & then replace it with discounted price
6. Create an SSIS package that splits students data into different tables (destination) according to their percentage. (Eg. per>65 and per<100 then they must be in a different table & so on)
7. Create an SSIS package that merges author_name from tbl_author to tbl_book & join them using author_id (Table 1 : book_id,book_name,price,author_id | Table 2 : author_id,author_name)
8. Create an SSIS package that pivots the following source table
9. Create an SSIS package that unpivot the previous resultant table into original table

Assignment – 2 ETL with SSIS (Control Flow)

1. Create an SSIS package that deletes all the records from SQL table tbl_emp using Execute SQL task & For Loop container. Table information : fields -> emp_id, emp_name, designation (Take at least 10 records). For loop must be executed dynamically.
2. Create an SSIS package that combines all .CSV files (emp_id,emp_name,basic_sal,HRA,DA,medical) into single SQL server table tbl_emp using foreach loop container. SQL Table information : emp_id,emp_name,basic_sal,HRA,DA,medical,gross_salary. Gross salary must be calculated when data is being loaded from source to destination table.

Note : Clear table before executing any package