ETL is a process used to move data from one system and data structure to another. One tool used for this process is called SQL Server Integration Services (SSIS).
For this assignment, refer to the “SYM-408 ELT Process Data Employee Hours” zipped file to complete the assignment.
Use SQL Server Data Tools (SSDT) within the virtual machine to build an SSIS package. For this activity, include the following:
First, extract the datafile provided by your instructor and use SSMS to map the datafile’s columns into a SQL table.
Then, in SSDT, Create Data Flow Task to perform the following:
1. Use the Other Sources section to import the datafile.
2. Use the Derived Column component to transform the datafile: concatenate first and last names into one column, “Full Name,” in your SQL table, and map the “Hire date” column as date in your SQL table with the following format mm-dd-yyyy.
3. Use the Other Destination section to load the datafile in your SQL table.
4. Verify the completion of the ETL process by retrieving the data from your SQL table as follows:
– Create and execute a query that shows how many records are in the table. Take a screenshot of the query and results.
– Create and execute a query that shows the Full Name, Hire date and Week 4 hours for everyone who worked fewer than 40 hours in week 4. Take a screenshot of the query and results.
Finally, in a succinct paragraph, explain two data flow components of SSIS, data location differences with relational and multidimensional models, data transformation tools, BI/OLAP tools within SSIS and SSIS optimization techniques. Include examples in your response.
Submit the following in a single document compatible with the digital classroom (e.g., Microsoft Word). Include a one-line description above each screenshot.
• Screenshot of query and results showing how many records are in the table
• Screenshot of query and results showing, in updated format, who worked fewer than 40 hours in week four
• Paragraph of explanations
