Table of Contents
Assignment – 1 ETL with SSIS (Data Flow)
- Create an SSIS package that merges 4 different sources into one destination table
- Create an SSIS package that stores maximum & minimum marks from each subject of BCA semester 6 of a student list (Data Warehousing, Android, Python)
- 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
- 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)
- 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
- 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)
- 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)
- Create an SSIS package that pivots the following source table
- Create an SSIS package that unpivot the previous resultant table into original table
rollno | stud_name | subject | marks |
1 | AAAA | J2EE | 80 |
1 | AAAA | ASP.Net | 90 |
1 | AAAA | SEO | 85 |
2 | BBBB | J2EE | 70 |
2 | BBBB | ASP.Net | 70 |
2 | BBBB | SEO | 80 |
3 | CCCC | J2EE | 50 |
3 | CCCC | SEO | 60 |
4 | DDDD | ASP.Net | 70 |
Assignment – 2 ETL with SSIS (Control Flow)
- 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.
- 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