This post is an example of SSIS Checkpoints implementation.
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!
Welcome to SinghVikash blog
Popular Posts
-
Mobile Payment is combination of technologies. These technologies include Mobile Wallet providers, Mobile Technology providers, Banking, So...
-
This article is next in series of our learning PEGA. In the last two articles we learn what is PEGA and what are Work Type and Work Items ...
-
MongoDB provide flexibility of dynamic fields (columns) for each of the document (row). Unlike in RDBMS or DBMS system where each row has f...
-
SSIS provides many interesting tasks and components in its kitty to work with. This post is inspired by one of the question asked in a popul...
-
What does comes to your mind when you have to search something on Internet – Google , Yahoo , Bing , AOL etc. The contributions of these se...
-
GMail Tips How to create an archive copy of all your email or selected Gmail labels? How to check your Gmail account was hack...
-
Learning is a never ending thing and the same apply to even basic software’s like Ms-Excel, Ms-Word etc. Recently I tried one of the amazing...
Blog Archive
-
▼
2011
(24)
-
▼
October
(8)
- SSIS: How to pull Stock Quotes from Google Finance
- How to Map your Google blog with your own Domain?
- SQL Server 2008: Encrypt/Decrypt data through SQL ...
- SSIS: Checkpoints implementation
- SSIS: What is CheckPoints?
- PM: How accurate is your Project Cost Estimates?
- SSIS: Event Handlers
- PM: Do you know your Project Cost?
-
▼
October
(8)