To see the Checkpoints mechanism in action I created a very basic package.
The package has two tasks.
Task A: This task is an Execute SQL Task and reads the current Date time of SQL Server and assigns it to a local variable (User::varDate). The SQL Statement is very basic
SELECT getDate() as 'TodayDate'
Task B: This task is an Execute SQL Task and calls a stored procedure. The stored procedure inserts the records in a table called [Emp1]. Stored procedure code logic is pretty basic.
CREATE PROCEDURE [dbo].[vksAddEmp](
@EmpName varchar(50),
@Age int,
@DOJ Datetime) AS
BEGIN
INSERT INTO HumanResource.dbo.Emp1 values(@EmpName,@Age,@DOJ)
END
To implement the Checkpoints at package level; the #1 thing I did was to ENABLE THE CHECKPOINT AT PACKAGE LEVEL Checkpoints feature at package level by setting up the following properties:
#2 I have CONFIGURE TASK(s) FOR CHECKPOINT AT CONTROL FLOW TAB by setting following properties for Task A and Task B.
After setting up the #1 and #2 pre-requisite, I run the package which got executed successfully. Since package was executed successfully no checkpoint files were created.
Now to generate an error at Task B, I removed the stored procedure (vksAddEmp) which the Task B is calling. This is done intentionally to generate errors. After that I executed the package and got the error in Task B. The package fails.
Since the package has failed it generated a Checkpoint file named myCheckpoint. I checked the file location and it exist.
When I open the checkpoint the content of the Checkpoint were visible as well.
The Checkpoints mechanism has generated the Checkpoints file.
Now I re-created the stored procedure which has been used in Task B and re-run the package. The Package runs and started with Task B which was the point of failure.
So in the above example we have seen the Checkpoints mechanism in action. It was a very basic example but the example shows that if applied correctly; Checkpoints are useful tool.
Thanks for reading the post!
