(SQL – Level 3 – Non-relational SQL Server Features and Tools)
Assignment 05
Automation
Module Overview
In this module, you learn about automating tasks to run on a predefined schedule using SQL Server Agent and Windows
Scheduler.
Required Software
SQL Developer Edition (or equivalent)
Visual Studio 2019 Community edition with the SSIS Extension
Report Builder
Assignment Videos
Please watch the following assignment videos. You can watch them all at once, but I recommend watching a few,
changing to a different task, like reading, and then watching a few more.
https://youtube.com/playlist?list=PLfycUyp06LG8bKCdWgJEkP9xdlTlARvZ6
Assignment Articles
Please read the following documents:
https://www.sqlshack.com/introduction-to-the-sql-server-agent/
https://www.sqlshack.com/multiple-methods-for-scheduling-a-sql-server-backup-automatically/
https://www.sqlshack.com/automate-sql-database-backups-using-maintenance-plans/
https://www.mssqltips.com/sqlservertip/5019/sql-server-agent-job-schedule-reporting/
Notes:
You do not need to perform any of the demos are labs in these articles
Assignment Tasks
In this assignment's tasks, you will create two SSIS projects. The first project will simply execute a set of provided ETL
stored procedures using SSIS Execute SQL tasks. The second project will import file data into a SQL server database using
more advanced features of SSIS.
Step 1. Create a DW with ETL Objects
In this step, you review and execute my pre-made ETL code.
1. Open, review, and execute the script, 1_Assignment03SourceDatabase.sql to create the source database.
2. Review the metadata worksheet, 2_Assignment03MetadataWorksheet.xlsx.
3. Open, review, and execute the script 3_Assignment03DWDatabase.sql. to create the destination.
4. Open, review, and execute the script 4_Assignment03ETL.sql to create an ETL process.
1|Page
(SQL – Level 3 – Non-relational SQL Server Features and Tools)
Step 2. Review a Solution
In this step, you review and Visual Studio Solution. The solution is already created, but you must make sure that the
package can run on your machine. If not, verify that the databases and all the ETL objects have been created.
1. Open, review, and execute the SSIS package DWEmployeeProjectsETLProcess.
2. Change the name of the Solution to use your name.
Figure 1. Testing the SSIS package
Tips: Remember to check the SQL Server Connection if you are using a named instance
Step 3. Automate the SSIS Package using SQL Agent
You are now going to create an SQL Agent automation job. Then you will create a script so that your instructor can run
this script to verify that your job was configured correctly.
1. Copy the SIS SSIS package in two a folder called C:\_SQL330.
Figure 2. Copying the SSIS package
2. Create a SQL Server agent automation job to execute the package each day at 1:00 AM.
3. Create a SQL script so that the job can be created on another computer.
2|Page
(SQL – Level 3 – Non-relational SQL Server Features and Tools)
Figure 3. The Job as a script file.
4. Add the SQL Agent Job Script to your VS Solution and click Save All.
Step 4. Create an Automation Report
Use SSRS Report Builder to report on both the ETL log and the SQL Agent job history.
Figure 4. The Report Builder report
Tips: If elements overlap they will hide one of them.
Assignment Knowledge Document
After you have created your ETL Solution, write a paper as if describing to a coworker how your ELT Automation
process works. Included enough detail that they could troubleshoot the processes if you were on vacation!
Please use an MS Word compatible editor and save your files as either a doc or docx file. Then, place it in the Document
folder of your Visual Studio Solution (Make sure to Save All).
You only need about a few paragraphs, though you can do more if you want. Make sure there are an introduction, a
summary, and one or more topic paragraphs.
Note: Make sure you put it in the proper, professional-level formatting! It does not have to be perfect, but you won't get
credit for turning in a simple blob of text! Use this link to understand what I am looking for: Creating Professional
Documents (External Site)
Add your files to the Visual Studio Solution
Now that the SQL script and Word document are complete, add all files to the assignment's VS Solution.
Important: Make sure to click the Save-All button in Visual Studio when you are done!
Submit your work to the Canvas site
After you complete your work, compress the Solution folder into a single zip file, then submit the zip file on the Canvas
website, in the appropriate module Assignment.
Figure 5. The submit assignment button in Canvas.
You’re done!
3|Page
