Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using Checkpoints in SSIS (Part 1) Expand / Collapse
Author
Message
Posted Tuesday, February 17, 2009 12:08 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 2:57 PM
Points: 25, Visits: 143
Comments posted to this topic are about the item Using Checkpoints in SSIS (Part 1)

Aaron Akin
http://aaronakinsql.wordpress.com || http://www.linkedin.com/in/aaronakin
Post #658238
Posted Tuesday, February 17, 2009 10:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 27, 2009 7:58 AM
Points: 7, Visits: 24
One question I had after reading this article was, "How does it work with For Loop containers?" Rather than post the question and wait for a response, I thought I'd check it out myself. :D

I created a simple package with three Script tasks, the second of which was embedded within a For Loop container. Within that second Script task, I put an InputBox that would allow me to cause the task to fail on any given iteration.

As long as you set up your For Loop properties correctly, using variables instead of absolute start points, the checkpoint process will save off your counters to the XML file and, when restarted, will pick up the For Loop container at the exact iteration that failed. Naturally, you can also set it up to restart from the first iteration or skip to the next...whatever you need.

The main point is that the checkpoint process will save off your variables from within a For Loop and allows you to reenter that For Loop at the precise point of failure.

k
Post #658655
Posted Tuesday, February 17, 2009 12:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 2:57 PM
Points: 25, Visits: 143
It's great that you got it to work properly. I'd be interested in the details. I tried every way possible (or so I thought) but never had any luck. I also researched the heck out of it but never found any proof of it actually working.

Who knows, maybe if I had just restarted VS it would have magically started working again...it's happened before. :)


Aaron Akin
http://aaronakinsql.wordpress.com || http://www.linkedin.com/in/aaronakin
Post #658757
Posted Tuesday, February 17, 2009 2:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 27, 2009 7:58 AM
Points: 7, Visits: 24
My first attempts didn't work at all. It just kept starting the loop over again at the first iteration—sometimes you might want that, but I wanted to specifically test a re-entry on the iteration that failed.

I realized that it wasn't working because my For Loop had an InitExpression that initialized the index variable to an absolute number (e.g., @LoopCounter = 1). This was overriding the value the Checkpoint process was saving in the XML file for @LoopCounter.

When I changed the InitExpression to a variable expression (e.g., @LoopCounter = @LoopCounter + 1), this maintained the stored value of the variable from the XML file.

Naturally, you'd have to play around with it to make it suit your needs.

k
Post #658897
Posted Wednesday, February 18, 2009 7:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 2:57 PM
Points: 25, Visits: 143
Excellent. Can't imagine how I missed that. Thanks!

Aaron Akin
http://aaronakinsql.wordpress.com || http://www.linkedin.com/in/aaronakin
Post #659331
Posted Thursday, February 19, 2009 6:55 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 07, 2011 6:35 AM
Points: 54, Visits: 96
Hi Aaron

After reading an earlier article regarding checkpoints we have chosen to try to implement them but we are having a stifling issue. We have designated a directory on the SQL Server to write the XML (.CHK) files. When we build the package and test it - the .CHK files are written to a location on the users PC. We are OK with that while developing and testing the process. When we try to change the location to the SQL server itself - it simply will not save the new path location that is on the server... have you ever experienced this, and what ideas might you have for us?

-Frustrated with Check Points
Post #660256
Posted Thursday, February 19, 2009 7:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 2:57 PM
Points: 25, Visits: 143
Hi Gary,

1. Is there an expression at the package level that is setting the value of the CheckpointFileName property. This would cause any changes to this property to be ignored when the package is executed.

2. What is the value of the CheckpointFileName property that you want in the package? Is it a local path (i.e. c:\checkpointfile.chk) or a network path (i.e. \\servername\share\checkpointfile.chk)?

3. When you execute the package in Visual Studios (BIDS), click on the Execution Results tab and look for the line that says, "Information: The package will be saving checkpoints to the file xxx during execution." Is this a local path or a network path?

See if any of this helps and let me know the results.


Aaron Akin
http://aaronakinsql.wordpress.com || http://www.linkedin.com/in/aaronakin
Post #660293
Posted Thursday, February 19, 2009 9:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 27, 2009 7:58 AM
Points: 7, Visits: 24
For this sort of thing, we've been using Package Configurations stored in an XML file. This allows us to change properties (like file paths) without rebuilding the package. But that's a completely different topic from Checkpoints...

However, one error I encountered along these lines was when I changed a property in the package (in BIDS), and saved it, but either (a) failed to build it, or (b) built it, not realizing that the build-path was different than expected.

This is a real basic error, true, but I've done it, so I mention it here. Just make sure that the changes you make in BIDS are built into the package (*.dtsx) you're actually executing. And (as Aaron mentioned) that the pathname is accurate--a network server pathname would be better than a locally-relevant pathname (i.e., \\Server01\SSIS\path vs. D:\SSIS\path).

k
Post #660482
Posted Thursday, February 19, 2009 9:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 2:57 PM
Points: 25, Visits: 143
I thought of this as well, but you cannot set the CheckpointFileName property with package configurations. You'd have to add a variable and create an expression that would set this property using the value of that variable. The variable could then be set using configurations.

Aaron Akin
http://aaronakinsql.wordpress.com || http://www.linkedin.com/in/aaronakin
Post #660541
Posted Thursday, February 19, 2009 10:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 27, 2009 7:58 AM
Points: 7, Visits: 24
Aaron Akin (2/19/2009)
I thought of this as well, but you cannot set the CheckpointFileName property with package configurations. You'd have to add a variable and create an expression that would set this property using the value of that variable. The variable could then be set using configurations.


Hmm...I have to disagree with you on this. Though your alternative (assign the CheckpointFileName to a variable and use a package config to change the variable) works just fine, I was able to create a package configuration file that included the CheckpointFileName property and changed it directly.

CheckpointFileName and CheckpointUsage were second and third on the list of Properties in the Package Configuration Wizard. I checked CheckpointFileName and saved the configuration to XML. Editing the config file, I found entries for:

Configuration
ConfiguredType="Property"
Path="\Package.Properties[CheckpointFileName]"
ValueType="String"
ConfiguredValue C:\Solutions\CheckpointTest\Logs\CheckpointFile.xml


I edited this, changing the value of CheckpointFile.xml to CheckpointFileTestMe.xml. Then I executed the package in a way that forced it to fail. This caused it to create a checkpoint file, and it used the CheckpointFileTestMe.xml name. I also tested it with a different pathname, and that worked as well.

k
Post #660573
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse