{"id":1753,"date":"2022-02-13T09:46:29","date_gmt":"2022-02-13T09:46:29","guid":{"rendered":"https:\/\/swatilathia.com\/?page_id=1753"},"modified":"2023-01-05T02:42:35","modified_gmt":"2023-01-05T02:42:35","slug":"assignment-data-warehousing-using-sql-server-2012","status":"publish","type":"page","link":"https:\/\/swatilathia.com\/?page_id=1753","title":{"rendered":"Assignments"},"content":{"rendered":"\n

\n

Table of Contents<\/p>\nToggle<\/span>

Assignment \u2013 1 ETL with SSIS (Data Flow)<\/a><\/li>

Assignment \u2013 2 ETL with SSIS (Control Flow)<\/a><\/li><\/ul><\/nav><\/div>\n

<\/span>Assignment \u2013 1 ETL with SSIS (Data Flow)<\/span><\/h4>\n\n\n\n
$\"What$ <\/figure>\n\n\n\n
\n
Create an SSIS package that merges 4 different sources into one destination table<\/li>\n\n\n\n
Create an SSIS package that stores maximum & minimum marks from each subject of BCA semester 6 of a student list (Data Warehousing, Android, Python) <\/li>\n\n\n\n
Create an SSIS package that creates two new columns as \u201cFirstname\u201d & \u201cLastname\u201d from \u201cFullname\u201d column of a student data & store all these columns in a destination table <\/li>\n\n\n\n
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)<\/li>\n\n\n\n
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<\/li>\n\n\n\n
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)<\/li>\n\n\n\n
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)<\/li>\n\n\n\n
Create an SSIS package that pivots the following source table<\/li>\n\n\n\n
Create an SSIS package that unpivot the previous resultant table into original table<\/li>\n<\/ol>\n\n\n\n
\n
\n
\n
\n
\n
rollno<\/td> stud_name<\/td> subject<\/td> marks<\/td><\/tr>
1<\/td> AAAA<\/td> J2EE<\/td> 80<\/td><\/tr>
1<\/td> AAAA<\/td> ASP.Net<\/td> 90<\/td><\/tr>
1<\/td> AAAA<\/td> SEO<\/td> 85<\/td><\/tr>
2<\/td> BBBB<\/td> J2EE<\/td> 70<\/td><\/tr>
2<\/td> BBBB<\/td> ASP.Net<\/td> 70<\/td><\/tr>
2<\/td> BBBB<\/td> SEO<\/td> 80<\/td><\/tr>
3<\/td> CCCC<\/td> J2EE<\/td> 50<\/td><\/tr>
3<\/td> CCCC<\/td> SEO<\/td> 60<\/td><\/tr>
4<\/td> DDDD<\/td> ASP.Net<\/td> 70<\/td><\/tr><\/tbody><\/table><\/figure>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n\n\n\n
<\/span>Assignment \u2013 2 ETL with SSIS (Control Flow)<\/span><\/h4>\n\n\n\n
\n
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.<\/li>\n\n\n\n
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.<\/li>\n<\/ol>\n\n\n\n
Note : Clear table before executing any package<\/p>\n<\/body>","protected":false},"excerpt":{"rendered":"
Assignment \u2013 1 ETL with SSIS (Data Flow) 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 \u2013 2 ETL with SSIS (Control […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"zakra_page_container_layout":"customizer","zakra_page_sidebar_layout":"customizer","zakra_remove_content_margin":false,"zakra_sidebar":"customizer","zakra_transparent_header":"customizer","zakra_logo":0,"zakra_main_header_style":"default","zakra_menu_item_color":"","zakra_menu_item_hover_color":"","zakra_menu_item_active_color":"","zakra_menu_active_style":"","zakra_page_header":true,"om_disable_all_campaigns":false,"footnotes":""},"class_list":["post-1753","page","type-page","status-publish","hentry"],"jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/swatilathia.com\/index.php?rest_route=\/wp\/v2\/pages\/1753","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swatilathia.com\/index.php?rest_route=\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/swatilathia.com\/index.php?rest_route=\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/swatilathia.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swatilathia.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1753"}],"version-history":[{"count":6,"href":"https:\/\/swatilathia.com\/index.php?rest_route=\/wp\/v2\/pages\/1753\/revisions"}],"predecessor-version":[{"id":2669,"href":"https:\/\/swatilathia.com\/index.php?rest_route=\/wp\/v2\/pages\/1753\/revisions\/2669"}],"wp:attachment":[{"href":"https:\/\/swatilathia.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1753"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}