February 17, 2009 at 12:08 am
Comments posted to this topic are about the item Using Checkpoints in SSIS (Part 1)
February 17, 2009 at 10:19 am
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. đŸ˜€
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
February 17, 2009 at 12:44 pm
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. đŸ™‚
February 17, 2009 at 2:32 pm
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
February 18, 2009 at 7:00 am
Excellent. Can't imagine how I missed that. Thanks!
February 19, 2009 at 6:55 am
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
February 19, 2009 at 7:28 am
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.
February 19, 2009 at 9:12 am
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
February 19, 2009 at 9:52 am
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.
February 19, 2009 at 10:26 am
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
February 19, 2009 at 12:01 pm
Hi Aaron
Yeah, I feel kinda stupid now. The object did have an expression set on that property. It was changing the value as soon as the expression was evaluated. I deleted the expression and it appears to have solved the problem. I'm running the package now and its performing exactly as it should. Thank you so much for pointing out the obvious. Sometimes it is the last place we look.
-Gary
February 19, 2009 at 12:01 pm
kgiambas (2/19/2009)
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.
Yep, you're right. Not sure what I was thinking...possibly going a bit crazy. :w00t:
February 19, 2009 at 12:02 pm
gary.proctor (2/19/2009)
Hi AaronYeah, I feel kinda stupid now. The object did have an expression set on that property. It was changing the value as soon as the expression was evaluated. I deleted the expression and it appears to have solved the problem. I'm running the package now and its performing exactly as it should. Thank you so much for pointing out the obvious. Sometimes it is the last place we look.
-Gary
No problem. Glad you got it working.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply