Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Checkpoints in SSIS (Part 1)


Using Checkpoints in SSIS (Part 1)

Author
Message
Aaron Akin
Aaron Akin
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 150
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
kgiambas
kgiambas
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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. BigGrin

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
Aaron Akin
Aaron Akin
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 150
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. Smile

Aaron Akin
http://aaronakinsql.wordpress.com || http://www.linkedin.com/in/aaronakin
kgiambas
kgiambas
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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
Aaron Akin
Aaron Akin
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 150
Excellent. Can't imagine how I missed that. Thanks!

Aaron Akin
http://aaronakinsql.wordpress.com || http://www.linkedin.com/in/aaronakin
gary.proctor
gary.proctor
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 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
Aaron Akin
Aaron Akin
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 150
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
kgiambas
kgiambas
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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
Aaron Akin
Aaron Akin
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 150
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
kgiambas
kgiambas
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search