Posted: March 11th, 2023

Extract Transform Load (ETL) Process

Extract Transform Load (ETL) Process

Assessment Description

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

This assignment uses a rubric. Please review the rubric prior to beginning the assignment to become familiar with the expectations for successful completion.

Attachments

SYM-408-RS-ETLProcess-datafile-EmpHours (2).zip

Rubric Criteria

Expand All Rubric CriteriaExpand All

Query and Results Showing How Many Records Are in the Table

22.75 points

Query and Results Showing, in Updated Format, Who Worked Fewer Than 40 Hours in Week Four

9.75 points

Two Data Flow Components of SSIS

26 points

Solid Academic Writing and Documentation

SYM-408-RS-ETLProcess-datafile-EmpHours.csv

LastName
FirstName
JobTitle
week1
Week2
Week3
Week4
hireDate

Amen
Barbara
Accountant
40
40
40
40
10/5/2018

Seamans
Mike
Accountant
40
40
40
40
10/5/2018

Tomic
Dragan
Accounts Payable Specialist
40
40
40
40
10/5/2018

Sheperdigian
Janet
Accounts Payable Specialist
24
40
40
40
10/5/2018

Poe
Deborah
Accounts Receivable Specialist
40
40
40
40
10/5/2018

Spoon
Candy
Accounts Receivable Specialist
40
40
40
40
10/5/2018

Walton
Bryan
Accounts Receivable Specialist
40
40
40
40
10/5/2018

Berg
Karen
Application Specialist
40
40
40
40
10/5/2018

Meyyappan
Ramesh
Application Specialist
40
40
24
40
10/5/2018

Bacon
Dan
Application Specialist
40
40
40
40
10/5/2018

Bueno
Janaina
Application Specialist
40
32
40
40
10/5/2018

Barber
David
Assistant to the Chief Financial Officer
40
40
40
40
10/5/2018

Martin
Mindy
Benefits Specialist
40
40
40
40
10/5/2018

Kharatishvili
Tengiz
Control Specialist
40
40
40
40
10/5/2018

Norred
Chris
Control Specialist
40
40
40
40
10/5/2018

Ajenstat
François
Database Administrator
0
40
40
40
10/5/2018

Wilson
Dan
Database Administrator
40
40
40
40
10/5/2018

Chai
Sean
Document Control Assistant
40
40
40
40
10/5/2018

Berge
Karen
Document Control Assistant
40
40
40
40
10/5/2018

Arifin
Zainal
Document Control Manager
40
40
40
40
10/5/2018

Tamburello
Roberto
Engineering Manager
40
40
40
40
10/5/2018

Alberts
Amy
European Sales Manager
40
40
40
40
10/5/2018

Hedlund
Magnus
Facilities Administrative Assistant
40
40
40
24
10/5/2018

Culbertson
Grant
Human Resources Administrative Assistant
40
40
40
40
10/5/2018

Chen
Hao
Human Resources Administrative Assistant
40
40
40
32
10/5/2018

Penor
Lori
Janitor
40
40
40
40
10/5/2018

Macrae
Stuart
Janitor
40
40
40
40
10/5/2018

Berry
Jo
Janitor
40
0
40
40
10/5/2018

Coleman
Pat
Janitor
32
40
40
40
10/5/2018

Kleinerman
Christian
Maintenance Supervisor
40
40
40
40
10/5/2018

Brown
Kevin
Marketing Assistant
40
40
40
40
10/5/2018

Dempsey
Mary
Marketing Assistant
40
40
24
40
10/5/2018

Benshoof
Wanida
Marketing Assistant
40
40
40
40
10/5/2018

Wood
John
Marketing Specialist
40
40
40
32
10/5/2018

Eminhizer
Terry
Marketing Specialist
40
40
40
40
10/5/2018

Harnpadoungsataya
Sariya
Marketing Specialist
40
40
40
32
10/5/2018

Gibson
Mary
Marketing Specialist
40
40
40
40
10/5/2018

Williams
Jill
Marketing Specialist
40
40
40
40
10/5/2018

Sharma
Ashvini
Network Administrator
40
40
40
40
10/5/2018

Connelly
Peter
Network Administrator
40
40
40
40
10/5/2018

Ellerbrock
Ruth
Production Technician – WC10
40
40
40
40
10/5/2018

Johnson
Barry
Production Technician – WC10
40
40
40
40
10/5/2018

Higa
Sidney
Production Technician – WC10
40
40
40
40
10/5/2018

Ford
Jeffrey
Production Technician – WC10
40
40
40
40
10/5/2018

Hartwig
Doris
Production Technician – WC10
40
40
40
0
10/5/2018

Glimp
Diane
Production Technician – WC10
40
40
40
32
10/5/2018

Kearney
Bonnie
Production Technician – WC10
40
40
40
40
10/5/2018

Stadick
Betsy
Production Technician – WC10
40
40
40
40
10/5/2018

Wedge
Patrick
Production Technician – WC10
40
40
40
40
10/5/2018

Tiedt
Danielle
Production Technician – WC10
40
40
32
40
10/5/2018

Zimmerman
Kimberly
Production Technician – WC10
24
40
40
40
10/5/2018

Vande Velde
Tom
Production Technician – WC10
40
40
40
40
10/5/2018

Ersan
Ebru
Production Technician – WC10
40
40
40
40
10/5/2018

Baker
Mary
Production Technician – WC10
40
32
40
40
10/5/2018

Homer
Kevin
Production Technician – WC10
40
40
40
40
10/5/2018

Kane
John
Production Technician – WC10
40
40
0
40
10/5/2018

Hill
Christopher
Production Technician – WC10
40
40
40
40
10/5/2018

Salmre
Ivo
Production Technician – WC20
40
40
40
40
10/5/2018

Valdez
Sylvester
Production Technician – WC20
32
40
40
40
10/5/2018

Sousa
Anibal
Production Technician – WC20
40
40
40
40
10/5/2018

Smith
Samantha
Production Technician – WC20
40
40
32
40
10/5/2018

Ting
Hung-Fu
Production Technician – WC20
40
40
40
40
10/5/2018

Samarawickrama
Prasanna
Production Technician – WC20
40
40
40
32
10/5/2018

Su
Min
Production Technician – WC20
40
40
40
40
10/5/2018

Turner
Olinda
Production Technician – WC20
40
40
40
40
10/5/2018

Sunkammurali
Krishna
Production Technician – WC20
40
40
40
40
10/5/2018

Singh
Paul
Production Technician – WC20
40
40
40
40
10/5/2018

Feng
Hanying
Production Technician – WC20
40
40
40
40
10/5/2018

Sam
Raymond
Production Technician – WC20
40
40
40
40
10/5/2018

Fakhouri
Fadi
Production Technician – WC20
40
40
40
40
10/5/2018

Sacksteder
Lane
Production Technician – WC20
40
40
40
40
10/5/2018

Randall
Linda
Production Technician – WC20
40
40
40
40
10/5/2018

Dyck
Shelley
Production Technician – WC20
40
40
40
40
10/5/2018

Earls
Terrence
Production Technician – WC20
24
40
40
40
10/5/2018

Hines
Michael
Production Technician – WC20
40
40
40
40
10/5/2018

Mirchandani
Nitin
Production Technician – WC20
40
40
40
40
10/5/2018

Decker
Barbara
Production Technician – WC20
40
40
40
40
10/5/2018

Chen
John
Production Technician – WC20
40
40
40
40
10/5/2018

Hesse
Stefen
Production Technician – WC20
40
40
40
40
10/5/2018

Selikoff
Steven
Production Technician – WC30
40
40
40
40
10/5/2018

Poland
Carole
Production Technician – WC30
40
40
40
40
10/5/2018

Rettig
Bjorn
Production Technician – WC30
40
40
40
40
10/5/2018

Osada
Michiko
Production Technician – WC30
40
40
40
40
10/5/2018

Philips
Carol
Production Technician – WC30
40
40
32
40
10/5/2018

Netz
Merav
Production Technician – WC30
32
24
40
0
10/5/2018

Wang
Jian Shuo
Production Technician – WC30
40
40
40
40
10/5/2018

Reátegui Alayo
Sandra
Production Technician – WC30
40
40
40
40
10/5/2018

Watters
Jason
Production Technician – WC30
40
40
40
24
10/5/2018

Ruth
Andy
Production Technician – WC30
40
32
40
40
10/5/2018

Vanderhyde
Michael
Production Technician – WC30
40
40
40
40
10/5/2018

Shabalin
Rostislav
Production Technician – WC30
40
40
40
40
10/5/2018

Johnson
David
Production Technician – WC30
40
40
40
40
10/5/2018

Young
Garrett
Production Technician – WC30
40
40
40
40
10/5/2018

Metters
Susan
Production Technician – WC30
40
0
40
40
10/5/2018

Li
George
Production Technician – WC30
40
40
40
40
10/5/2018

Yalovsky
David
Production Technician – WC30
40
40
40
40
10/5/2018

Ingle
Marc
Production Technician – WC30
40
40
40
40
10/5/2018

Zabokritski
Eugene
Production Technician – WC30
40
40
32
40
10/5/2018

Martin
Benjamin
Production Technician – WC30
40
32
40
40
10/5/2018

Koch
Reed
Production Technician – WC30
40
40
40
40
10/5/2018

Lawrence
David
Production Technician – WC30
40
40
40
40
10/5/2018

King
Russell
Production Technician – WC30
40
40
40
40
10/5/2018

Frum
John
Production Technician – WC30
40
40
40
40
10/5/2018

Miksovsky
Jan
Production Technician – WC30
40
40
40
40
10/5/2018

Brown
Eric
Production Technician – WC40
40
40
40
40
10/5/2018

Kaliyath
Sandeep
Production Technician – WC40
40
40
40
40
10/5/2018

Frintu
Mihail
Production Technician – WC40
40
40
40
40
10/5/2018

Creasey
Jack
Production Technician – WC40
0
40
40
40
10/5/2018

Cook
Patrick
Production Technician – WC40
40
0
40
40
10/5/2018

Martinez
Frank
Production Technician – WC40
40
40
40
40
10/5/2018

Goldstein
Brian
Production Technician – WC40
40
40
40
40
10/5/2018

Cornelsen
Ryan
Production Technician – WC40
40
40
40
40
10/5/2018

Komosinski
Paul
Production Technician – WC40
40
24
40
40
10/5/2018

Yukish
Gary
Production Technician – WC40
40
40
40
40
10/5/2018

Caron
Rob
Production Technician – WC40
24
40
40
40
10/5/2018

Cetinok
Baris
Production Technician – WC40
40
40
40
40
10/5/2018

Holliday
Nicole
Production Technician – WC40
40
40
40
40
10/5/2018

Rothkugel
Michael
Production Technician – WC40
40
40
40
40
10/5/2018

McGuel
Alejandro
Production Technician – WC40
0
0
0
0
10/5/2018

Northup
Fred
Production Technician – WC40
40
40
32
40
10/5/2018

Liu
Kevin
Production Technician – WC40
40
40
40
40
10/5/2018

Mohamed
Shammi
Production Technician – WC40
40
40
40
32
10/5/2018

Patel
Rajesh
Production Technician – WC40
40
40
40
40
10/5/2018

Nay
Lorraine
Production Technician – WC40
40
40
40
40
10/5/2018

Nartker
Paula
Production Technician – WC40
40
40
40
40
10/5/2018

Lee
Frank
Production Technician – WC40
40
40
40
40
10/5/2018

Lloyd
Brian
Production Technician – WC40
40
32
40
40
10/5/2018

Nusbaum
Tawana
Production Technician – WC40
40
40
40
40
10/5/2018

Myer
Ken
Production Technician – WC40
32
40
40
40
10/5/2018

Mares
Gabe
Production Technician – WC40
40
40
24
40
10/5/2018

Koenigsbauer
Kirk
Production Technician – WC45
32
32
32
32
10/5/2018

Steele
Laura
Production Technician – WC45
40
32
40
40
10/5/2018

Nayberg
Alex
Production Technician – WC45
40
40
40
40
10/5/2018

Cencini
Andrew
Production Technician – WC45
40
40
40
40
10/5/2018

Preston
Chris
Production Technician – WC45
40
40
40
40
10/5/2018

McKay
Yvonne
Production Technician – WC45
40
40
40
40
10/5/2018

Hite
Douglas
Production Technician – WC45
40
40
40
40
10/5/2018

Esteves
Janeth
Production Technician – WC45
40
40
40
40
10/5/2018

Rounthwaite
Robert
Production Technician – WC45
40
40
40
40
10/5/2018

Penuchot
Lionel
Production Technician – WC45
40
40
40
24
10/5/2018

Munson
Stuart
Production Technician – WC45
40
40
40
40
10/5/2018

Alderson
Greg
Production Technician – WC45
40
40
40
40
10/5/2018

Gode
Scott
Production Technician – WC45
40
40
40
40
10/5/2018

Flood
Kathie
Production Technician – WC45
40
40
40
40
10/5/2018

Newman
Belinda
Production Technician – WC45
40
40
40
40
10/5/2018

Smith
Denise
Production Technician – WC50
40
40
40
40
10/5/2018

Miller
Frank
Production Technician – WC50
40
40
40
40
10/5/2018

Keil
Kendall
Production Technician – WC50
40
40
40
40
10/5/2018

Hohman
Bob
Production Technician – WC50
40
40
40
40
10/5/2018

Male
Pete
Production Technician – WC50
0
0
0
0
10/5/2018

Tibbott
Diane
Production Technician – WC50
40
40
40
24
10/5/2018

Hunter
Russell
Production Technician – WC50
24
40
40
40
10/5/2018

Scardelis
Jim
Production Technician – WC50
40
40
40
40
10/5/2018

Keyser
Elizabeth
Production Technician – WC50
40
40
40
32
10/5/2018

Samant
Mandar
Production Technician – WC50
40
40
40
40
10/5/2018

Tejani
Sameer
Production Technician – WC50
40
40
40
40
10/5/2018

Yu
Nuan
Production Technician – WC50
40
40
40
40
10/5/2018

Song
Lolan
Production Technician – WC50
40
40
40
40
10/5/2018

Pournasseh
Houman
Production Technician – WC50
40
40
40
40
10/5/2018

Ciccu
Alice
Production Technician – WC50
40
40
40
40
10/5/2018

Cao
Jun
Production Technician – WC50
40
40
40
40
10/5/2018

Fatima
Suroor
Production Technician – WC50
40
40
40
40
10/5/2018

Evans
John
Production Technician – WC50
40
40
40
40
10/5/2018

Moschell
Linda
Production Technician – WC50
40
40
32
40
10/5/2018

Krapauskas
Mindaugas
Production Technician – WC50
40
40
40
40
10/5/2018

Barbariol
Angela
Production Technician – WC50
40
40
40
40
10/5/2018

Patten
Michael
Production Technician – WC50
40
40
40
40
10/5/2018

Niswonger
Chad
Production Technician – WC50
40
40
40
40
10/5/2018

Hall
Don
Production Technician – WC50
40
40
40
40
10/5/2018

Entin
Michael
Production Technician – WC50
40
40
24
40
10/5/2018

Lertpiriyasuwat
Kitti
Production Technician – WC50
40
40
40
40
10/5/2018

Gilbert
Guy
Production Technician – WC60
40
32
40
40
10/5/2018

McArthur
Mark
Production Technician – WC60
40
24
40
40
10/5/2018

Simon
Britta
Production Technician – WC60
32
40
40
40
10/5/2018

Shoop
Margie
Production Technician – WC60
32
40
40
40
10/5/2018

Laszlo
Rebecca
Production Technician – WC60
40
40
40
40
10/5/2018

Stahl
Annik
Production Technician – WC60
40
40
40
40
10/5/2018

Mohan
Suchitra
Production Technician – WC60
40
40
40
40
10/5/2018

Heidepriem
Brandon
Production Technician – WC60
40
40
40
40
10/5/2018

Lugo
Jose
Production Technician – WC60
40
40
40
40
10/5/2018

Okelberry
Chris
Production Technician – WC60
40
40
40
40
10/5/2018

Abercrombie
Kim
Production Technician – WC60
40
40
40
40
10/5/2018

Dudenhoefer
Ed
Production Technician – WC60
40
40
40
40
10/5/2018

Baker
Bryan
Production Technician – WC60
40
40
40
40
10/5/2018

Kramer
James
Production Technician – WC60
40
40
40
40
10/5/2018

Anderson
Nancy
Production Technician – WC60
40
40
40
40
10/5/2018

Rapier
Simon
Production Technician – WC60
40
40
40
40
10/5/2018

Michaels
Thomas
Production Technician – WC60
40
40
40
40
10/5/2018

Kogan
Eugene
Production Technician – WC60
40
40
40
40
10/5/2018

Dusza
Maciej
Production Technician – WC60
40
40
40
40
10/5/2018

Zwilling
Michael
Production Technician – WC60
40
40
40
40
10/5/2018

Reeves
Randy
Production Technician – WC60
40
40
40
40
10/5/2018

Khanna
Karan
Production Technician – WC60
40
40
40
40
10/5/2018

Adams
Jay
Production Technician – WC60
40
40
40
40
10/5/2018

Fitzgerald
Charles
Production Technician – WC60
0
0
0
0
10/5/2018

Masters
Steve
Production Technician – WC60
40
40
40
40
10/5/2018

Ortiz
David
Production Technician – WC60
40
40
40
40
10/5/2018

Hill
Annette
Purchasing Assistant
40
40
40
40
10/5/2018

Hillmann
Reinout
Purchasing Assistant
40
40
24
40
10/5/2018

Charncherngkha
Sootha
Quality Assurance Technician
40
40
40
40
10/5/2018

Berglund
Andreas
Quality Assurance Technician
40
40
40
40
10/5/2018

Harrington
Mark
Quality Assurance Technician
40
40
40
40
10/5/2018

Alexander
Sean
Quality Assurance Technician
40
40
40
40
10/5/2018

Luthra
Vidur
Recruiter
40
40
40
40
10/5/2018

Johnson
Willis
Recruiter
40
40
40
40
10/5/2018

Margheim
Diane
Research and Development Engineer
40
40
40
40
10/5/2018

Matthew
Gigi
Research and Development Engineer
40
40
40
40
10/5/2018

Blythe
Michael
Sales Representative
40
40
40
40
10/5/2018

Mitchell
Linda
Sales Representative
40
40
40
40
10/5/2018

Carson
Jillian
Sales Representative
40
24
40
40
10/5/2018

Vargas
Garrett
Sales Representative
40
40
40
40
10/5/2018

Reiter
Tsvi
Sales Representative
40
40
40
40
10/5/2018

Ansman-Wolfe
Pamela
Sales Representative
40
40
40
40
10/5/2018

Ito
Shu
Sales Representative
40
40
40
40
10/5/2018

Saraiva
José
Sales Representative
40
40
40
40
10/5/2018

Campbell
David
Sales Representative
40
40
40
40
10/5/2018

Mensa-Annan
Tete
Sales Representative
40
40
40
40
10/5/2018

Tsoflias
Lynn
Sales Representative
40
40
40
40
10/5/2018

Valdez
Rachel
Sales Representative
40
40
40
40
10/5/2018

Pak
Jae
Sales Representative
40
40
40
40
10/5/2018

Varkey Chudukatil
Ranjit
Sales Representative
40
40
40
40
10/5/2018

Uddin
Sairaj
Scheduling Assistant
40
40
40
40
10/5/2018

Vong
William
Scheduling Assistant
40
40
40
40
10/5/2018

Brewer
Alan
Scheduling Assistant
40
40
40
32
10/5/2018

LaMee
Brian
Scheduling Assistant
32
40
32
40
10/5/2018

Kuppa
Vamsi
Shipping and Receiving Clerk
40
40
40
40
10/5/2018

Berndt
Matthias
Shipping and Receiving Clerk
40
40
40
40
10/5/2018

Ackerman
Pilar
Shipping and Receiving Supervisor
0
0
0
0
10/5/2018

Eaton
Susan
Stocker
40
40
40
40
10/5/2018

Ralls
Kim
Stocker
40
40
40
40
10/5/2018

Bischoff
Jimmy
Stocker
40
40
40
32
10/5/2018

D’Hers
Thierry
Tool Designer
40
40
40
40
10/5/2018

Galvin
Janice
Tool Designer
32
40
32
40
10/5/2018