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...
-
United State Bulls Island | South Carolina Falls Park on the Reedy | Greenville, South Carolina BMW Zentrum | Spartanbu...
-
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...
-
Welcome to the Risk Management Questions for PMP examination. These questions here have been collected from various sources. Please check t...
-
MongoDB provide flexibility of dynamic fields (columns) for each of the document (row). Unlike in RDBMS or DBMS system where each row has f...
-
Time To Live or TTL indexes are special kind of indexes which remove the expired documents (rows) from collection (tables) after a fix time...
-
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)