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
-
Recently I completed my PMP Certification with PMI. Preparation for PMP exam is itself a project. Practicing PMP questions is a part of ever...
-
You might have seen this topic headline (Excel import issue) discussed in many forums, blogs etc. This issue puts a serious limitation to ...
-
SSIS (SQL Server Integration Services) is well known for extracting data from a variety of sources. It can extract data from online sources...
-
SQL Server Integration Services has provided many useful functions to the developers. FINDSTRING is one of the valuable string functions tha...
-
Reading and writing data with Ms-Excel is a very common in SSIS. A lot of individuals, organization, offices store their data in Ms-exc...
-
YouTube the popular video sharing site from Google has recently launched the trial of running videos using HTML5. HTML5 is the latest marku...
-
The use of flat file as a source or destination is very common in SSIS. MSBI has provided Flat File Source and Flat File destination compone...
-
This article is next in series of our learning PEGA. In the first article we learnt what is PEGA and the subsequent articles talked about ...
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)