June 16, 2011 at 10:19 am
Here you go... please see attachements
Thank you for working on this
June 16, 2011 at 10:25 am
Why do you have the container and the task TransactionOption set to two different things?
Package Level should be Required, not Supported.
The other two can be (probably should be) Supported.
Currently, you have Package & Task set to Supported and the Container set to Required. This might be what's messing you up.
June 16, 2011 at 10:33 am
But to chage the packageScope TransactionOption to required, I need to disable the check point property SaveCheckpoints : False, other wise it doesn't allow me to change from supported to Required.
June 16, 2011 at 11:25 am
quillis131 (6/16/2011)
But to chage the packageScope TransactionOption to required, I need to disable the check point property SaveCheckpoints : False, other wise it doesn't allow me to change from supported to Required.
Are you saying the package won't let you change this stuff? Because I can change back and forth between the two on my packages just fine. EDIT: Without changing the SaveCheckpoints property..
EDIT: But if it does force you to set the Save to False, you should be able to re-enable it after switching the package property.
June 16, 2011 at 11:38 am
Yes package won't let me change the stuff... I am struck with this since 3 days ...When i want to change the Transactionoption on PAckagescope to Required from support, it gives this message:
"The current package settings are not supported. Please change the SaveCheckpoints property or the TransactionOption property."....I have no idea why its saying this way.
And coming to re-enable it doesn't allow the checkpoint property Savecheckpoint TRUE after Switching the package property. It gives message "The current package settings are not supported. Please change the SaveCheckpoints property or the TransactionOption property.
I couldn't figure it out what is going on .....
June 16, 2011 at 11:58 am
At this point, I would say the package is corrupt or something is wrong with your install.
Start over with a brand new package.
June 16, 2011 at 12:07 pm
Thank you ....But even i tried out with a new package ...it says the same thing.
One thing does this Isolation level needs to be changed from default...does this help me .... and you can change all this stuff ( transaction), which version are you using, mine 2008
June 16, 2011 at 12:14 pm
I cannot understand how you were able to set your PackageScope properties ...I checked, if it allows me set the property from supported to required, on another machine ...but answer is NO ...its the same issue...I don't understand how its working for you.
June 16, 2011 at 12:23 pm
quillis131 (6/16/2011)
which version are you using, mine 2008
Right there might be the source of our communication issue. I'm using 2005 and having no trouble at all. And I assumed you were using the same since you didn't specify your version at the beginning of the thread.
So, Visual Studio and SQL Server 2008? Which edition? And is it regular 2k8 or R2?
EDIT: Just an FYI, BOL for 2005 says TransactionOption should be Required, but I notice 2008 doesn't even mention that.
June 16, 2011 at 12:29 pm
Looking at 2008 BOL, there is a very important note about the checkpoint file:
Set the CheckpointUsage property to one of two values:
Select Always to always restart the package from the checkpoint.
Important:
An error occurs if the checkpoint file is not available.
Select IfExists to restart the package only if the checkpoint file is available.
I've bolded the relevant part. Try changing that and see what happens.
The checkpoint is the point of failure. You can't restart at the checkpoint, I believe (could be wrong) without the checkpoint file. Given that IfExists doesn't guarantee that the package restarts at the checkpoint, you need to change the CheckpointUsage property to Always.
June 16, 2011 at 12:41 pm
No luck....I tried to change that ...the same message. And I am not sure what my version is ..but can definetly say it is 2008(sql express).......This seams a bug
June 16, 2011 at 12:49 pm
quillis131 (6/16/2011)
No luck....I tried to change that ...the same message. And I am not sure what my version is ..but can definetly say it is 2008(sql express).......This seams a bug
Check the Microsoft Connect site to see if someone's already posted it. Also, google the exact error to see if anything besides this thread comes up.
June 16, 2011 at 12:49 pm
I'm getting a similar issue through my own testing.
Slap together a quickie Test DB somewhere and drop this table build into it:
CREATE TABLE CheckpointTest
(ctID INT IDENTITY( 1, 1) NOT NULL,
ctSomeData VARCHAR(50) NULL CONSTRAINT CT_CheckpointTest_ctSomeData CHECK (ctSomeData <> 'aaa')
)
GO
Generate your new connection to that DB. Setup Checkpoints on the Package Properties as usual (Filepath/IfExists/True). Drop yourself off a Sequence container, failPackageOnFailure True, TransactionOption Required.
Drop in a pair of ExecuteSQL commands into the container, one doing an Insert CheckpointTest (ctSomeData) VALUES ('ABC') and another doing Insert CheckpointTest (ctSomeData) VALUES ('aaa'). Link them in order ABC -> aaa. First will work, second will fail, Transaction operates correctly.
Restarts restarts the transaction, as expected.
Fix the second ExecuteSQL command. have it Insert 'aab' instead. Run and see success. Inspect table, find records are finally inserted.
After the sequence container, add a new ExecuteSQL command. FailPackageOnFailure True. Command: SELECT 1/0 on the connection.
Run process, fail on ExecuteSQLTask that occurs after the transacted SequenceContainer. Rerun: See entire package re-run, including initial container which already successfully completed. Prove this to self by reviewing table.
I'd heard rumors of transactions and checkpoints not behaving nicely in 2k5, I'm going to need to explore a few other packages I'd expected to behave themselves.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 16, 2011 at 12:53 pm
Craig Farrell (6/16/2011)
I'd heard rumors of transactions and checkpoints not behaving nicely in 2k5, I'm going to need to explore a few other packages I'd expected to behave themselves.
I've had problems in 2k5, on SP1, with the Checkpoint not picking up properly. It was a huge package, major production item, and it kept failing. So we added checkpoints to roll stuff back. DIdn't work. We ended up having to rebuild the whole thing from scratch, breaking up the package, etc. to get things to work correctly.
But the OP is having issues with SQL Express 2008.
June 16, 2011 at 1:04 pm
Any way ...Thank you all ...atleast now I feel good as I am not the problem ...it has to be something with the Version level ..... Now hitting on other side ...
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply