SSMS 5 - Automating the SQL script with Job

Now, this is something which we have been doing since the beginning of the project. All the SQL codes be it Insert statement, Select Statement, Views, Stored Procedures when combined and executed to perform a task is called a SQL script.

Not to confuse with Stored procedure. 

A Stored procedure is declaration and definition of a task, while script is the execution.

SQL script has a defined flow such as Extracting from a file/database, storing it into some tables, transform it and then load it. There are some other things as well which we have already covered.

If you refer the last piece of code which we had where we created stored procedure and remove the steps where we created the stored procedures and views. Then that would become a script. 

---------------------------------------------------------------------------------------------------------------------
Use DWAdventureWorksLT2012Lab01
Go

--********************************************************************--
-- ETL Pre-Load Tasks
--********************************************************************--
-- 1) Drop Foreign Keys
--********************************************************************--
-- Drop Foreign Key Constraints
--********************************************************************--

ALTER TABLE dbo.FactSales DROP CONSTRAINT
 fkFactSalesToDimProducts;

ALTER TABLE dbo.FactSales DROP CONSTRAINT 
 fkFactSalesToDimCustomers;

ALTER TABLE dbo.FactSales DROP CONSTRAINT
 fkFactSalesOrderDateToDimDates;

ALTER TABLE dbo.FactSales DROP CONSTRAINT
 fkFactSalesShipDateDimDates; 
-- 2) Clear Flush and Fill Tables
Truncate Table FactSales;
Truncate Table DimProducts;
Truncate Table DimCustomers;

--********************************************************************--
-- ETL Dimension Load Tasks
--********************************************************************--
-- 3) Load Flush and Fill Dimension Tables
Declare @ReturnCode int;
Execute @ReturnCode = pETLFillDimProducts;
Select [pETLDimProducts Status] = @ReturnCode;

Execute @ReturnCode = [pETLFillDimCustomers];
Select [pETLDimCustomers Status] =  @ReturnCode;
go

-- 4) Load Incremental Loading Dimension Tables -- NA

--********************************************************************--
-- ETL Fact Load Tasks
--********************************************************************--
-- 5) Load Flush and Fill Dimension Tables

Declare @ReturnCode int;
Execute @ReturnCode = [pETLFillFactSales];
Select  [pETLFactSales Status] =  @ReturnCode;
go

-- 6) Load Incremental Loading Dimension Tables -- NA

--********************************************************************--
-- ETL Post-Load Tasks
--********************************************************************--
-- 7) Replace Foreign Keys 
--********************************************************************--
-- Replace Foreign Key Constraints
--********************************************************************--
ALTER TABLE dbo.FactSales ADD CONSTRAINT
 fkFactSalesToDimProducts FOREIGN KEY (ProductKey) 
 REFERENCES dbo.DimProducts (ProductKey);

ALTER TABLE dbo.FactSales ADD CONSTRAINT 
 fkFactSalesToDimCustomers FOREIGN KEY (CustomerKey) 
 REFERENCES dbo.DimCustomers (CustomerKey);
 
ALTER TABLE dbo.FactSales ADD CONSTRAINT
 fkFactSalesOrderDateToDimDates FOREIGN KEY (OrderDateKey) 
 REFERENCES dbo.DimDates(CalendarDateKey);

ALTER TABLE dbo.FactSales ADD CONSTRAINT
 fkFactSalesShipDateDimDates FOREIGN KEY (ShipDateKey)
 REFERENCES dbo.DimDates (CalendarDateKey);
 

Select * from DimCustomers
Select * from DimProducts
Select * from DimDates
Select * from FactSales
go

Test this.
If it works fine, save it on your hard drive .

And now starts the fun part. You can automate this script.
Go to your SQL server agent and start it if this service is not already running

It would ask you to confirm(may have to do more than once)

Expand the agent and right click on the job to create a new job

Enter the Job name

Now define the step

Select Steps from Left and Click New

Here you can manually type or add the sql script saved on your local.

Click Open

Select the script you want to run. 

Note: this script does not have the definition of stored procedure and views because you do not want that to be create every time

Once done, close the wizard

Now time to run the Job.

Right click on the job and click on 'Start Job at Step'

and Voila!!! you have successfully automated your first ETL process.

Now just in case your ETL job failed. 

SSMS is very well designed. Follow the instruction provided in the error message. 

If it says check the log, right click on the job and click on 'View History'

and follow the screenshots