DBM 298 Week 4 Individual Assignment Automate Extract, Transform and Load (ETL) Process
This week, you will automate the load process you began in previous weeks.
Create a stored procedure called "sp_trunc_tables" that truncates all six of the tables you created for the assignments in previous weeks.
Create an operator in your database that has an email address.
Create a job named "LoadMart" that takes the following steps:
- Executes sp_trunc_tables to clear out all of the tables
- Executes the SQL Server Integration Services (SSIS) packages you created in Week Two to load the "source" (src_) tables. Each package can be executed as a separate step. Use the "File System" package source to execute your DTSX files from Week Two.
- Execute the stored procedures you created in Week Three to load data from the source tables into the fact and dimension tables.
Schedule your job to run once an hour throughout the week.
Configure your job to send notices to the operator you created when it completes successfully. The operator will not get all of the emails because we have not set up Database Mail yet.
Write a 700-word (minimum) paper that describes three stored procedures and explain why you think they would be essential for your flight data mart.
Format your paper consistent with APA guidelines.
Submit the following to the Assignment Files tab above:
- The paper
- The code for sp_trunc_tables
- Screenshots of the following pages from your "LoadMart" job, after the associated window has been made full screen:
Questions & Answers
Have a Question?
Be the first to ask a question about this.