Transactions && Checkpoints; Bug Or Feature?
Aug 23, 2007
Hi there,
I am trying here to get a situation going which includes both transactions and checkpoints to make sure that when something goes wrong I don't get a) data corruption (hence the transactions and b) I don't have to completely restart my 2hr run (hence the checkpoints). However I ran into something of which i cannot see whether it is intended behaviour or simply a bug.
Here's the deal:
I have a SSIS-package in which I enable checkpoints (CheckpointUsage: IfExists and SaveCheckpoints: True).
I have 2 Dataflows which follow eachother (the first dataflow prepares data for the second dataflow to edit).
Because I want to make sure that my data is secure I put a separate transaction on both the dataflows.
And here my problem arises. If I run my package now and the second dataflow breaks then my checkpoint sends me back to the first dataflow and my initial insert is executed again, which isn't meant to happen (I enabled checkpoints to prevent rerunning items). Somehow my checkpoint does not register the fact that the first dataflow has already been executed and it will execute that one again upon rerun.
However: if I put a random task between the 2 transacted dataflows (for example an empty script-task) it will work as intended. Just as long as this inserted item doesnt have a transaction; because if it does then the problem comes back
Now if I execute the package then my checkpoint shows that the first dataflow has already been executed and thus it will not execute this one again and it starts at the second dataflow upon re-execution.
I can work around it (with the empty script-task) but still I am wondering as to why this is happening. I am very interested to hear whether this is really a bug or if it is intended behaviour (and if it is then why is it intended?)
View 1 Replies
ADVERTISEMENT
May 26, 2007
Hello everyone, I had been studying the relationship between SSIS Checkpoints and SSIS Transactions.
What I want to do is to create a package with different task, where each one task creates a new transaction, and the same time each task be a checkpoint, it€™s in order to restarts the package from the failure task not from the beginning.
The Transaction-Checkpoint solution contains two packages*:
CkeckpointsAndTransactions1.dtsx and CkeckpointsAndTransactions2.dtsx
Package CkeckpointsAndTransactions1 contains four tasks, task three always fail. The package is configured to use checkpoints and each individual task creates a checkpoint. Additionally, each task creates a new transaction. The package has the TransactionOption setting to NoSupported.
In the CkeckpointsAndTransactions1 package there is something wrong, when the third task fails and I restart the package, the package starts from the beginning, this is wrong!!, the package should restart from the failure task.
In order to the package works like is expected it€™s necessary to add a new task between second and third task. It is also necessary that this new task hasn€™t transaction support. This is shown in the CkeckpointsAndTransactions2 package, in this package after package failure, I restart the package and the package restarts from the failure task, like is expected, but the additional task should not be necessary!!
Does anyone what is wrong in my packages?? How can I to create a package with different task, where each task creates a new transaction, and the same time each task be a checkpoint?
*Please download the BIDS solution from hernan93.files-upload.com (Transaction-Checkpoint.zip file)
View 1 Replies
View Related
May 2, 2007
I have a package that has a container containing multiple DF Tasks.
The container is set to be Transacted, such that should any of the DF tasks fail the data inserted in any of the previous tasks rolls back.
This works as expected.
However, this container is part of a larger package and so I wanted to have a checkpoint on it, so that should any of the tasks within it fail, the package could be restarted from this container.
However, I would expect the functionality to be that on failure, the checkpoint would cause the whole container to be started again (because the container is transacted all DF task info would be rolled back) so we would expect it to start at task 1 again.
This is not the functionality I see. The package restarts from the failed task within the container every time.
According to the book Prof SSIS, it should start again from the first task and as explained this makes sense on a Transacted container as you would want this to happen.
A previous forum message encountered the same issue it appears:
See SSIS Checkpoints 04 Dec 2006.
This is an extract from it:
"I only experimented a little but my experience was that when I have a transacted container with multiple tasks that are checkpointed, SSIS would try to restart from the task that failed rather than from the first task in the container. The transaction was being rolled back correctly though.
In short, I felt that check points were not aware of transactions.
So, I ended up with this setting and it works for me:
Container is checkpointed and trasacted.
Tasks within the container are not checkpointed.
'FailParentOnFailure' property set to True on the tasks.
That way, if a task failed, it would fail the container and a checkpoint would be created at that level. Transaction would be rolled back as usual."
While this makes sense to me it is not the same properties that the SSIS book has that work.
Additionally, this didn't work for me either !!
I have tried every combination of FailPackageOnProperty and FailParentOnProperty that makes sense but every time the package restarts from the failed container within the task.
The transaction is rolled back correctly every time, but it seems the checkpoint that is created is not used correctly when dealing with transactions within containers.
View 1 Replies
View Related
Mar 3, 2008
I want to use a checkpoint in an SSIS package and would require some help.
I have a scenario like this
Task A ------ Task B-----------Task C
------- Task B1
Task A has a precedence constraint which determines if either Task B or Task B1 runs. Task B is run if the condition is met and Task B1 if the condition is not met.
I would like Task B1 to be a script task that is used to fail Task A so that when the package is restarted it will start from task A based on the checkpoint.
Is there any way to do this ?
View 7 Replies
View Related
Jun 14, 2007
I have a package that has 4 Script Tasks that are placed sequentially.
I have Task1--> Task2-->Task3-->Task4
The arrows between them are OnCompletion Arrows as opposed to the Standard OnSuccess arrows.Even if Task2 failed, it would still execute 3 and 4
the catch is that i want it such that when i run the first time and task 2 fails, then all the tasks except task2 should run which is fine, but when i rerun it. I want it such that it realises that task 2 had failed earlier, so it runs just task2.... if both 2 and 4 had failed then it should just run 2 and 4
i tired to implement it with check points, but the problemn is that if it fails at task2 it stops at task2 and does not continue to execute tasks 3 &4... when u rerun it starts at 2 but like i said i would like 3 & 4 to have completed the previous run...
Any suggestions would be helpful
Thanks for any help in advance..
smathew
View 6 Replies
View Related
Dec 4, 2006
I have a package that uses ssis checkpoints. It works well. However, when I try to setup transactions for some task, the chekpoints aren't used.
I read BOL and It states:
"If a package is configured to use checkpoints, Integration Services captures the restart point in the checkpoint file. The type of container that fails and the implementation of features such as transactions affect the restart point that is recorded in the checkpoint file."
But, how checkpoints are affected by transactions? what relation exists between this two components?
View 11 Replies
View Related
Mar 6, 2008
hi people, i have crashed on a this problem. I have a sequence container and on this container I have set "FailPackageOnFailure=true". Now in this container there are 2 tasks. The first one is preceeding the second one. Now both this task have set "FailParentOnFailure=true". Both task are the same and their purpose is to drop table A.
1) I run the package and it fails, because there is no table to drop.
2) I create the table manualy and run package again.
3) I see, that the first task is beeing just SIMPLE OMMITED and the second task runs
In general, everytime any task in a sequence container invokes failure, next time is beeing ommited regardelss of its status. How can this be fixed ? Thanks
View 4 Replies
View Related
Jan 17, 2006
I am building a set of packages to load different things, some of which have relationships with the others. Therefore I want them loaded in a certain order. I have built a main package that executes the set of packages to control the flow of the packages.
Now, I want to implement checkpoints. Ultimately, I only want to deal with the main package that controls everything. So I figure the main package needs checkpoints enabled. When packages are nested and checkpoints are on at the top level package, will the nested package(s) start at the control flow point of failure or will it run the entire nested package? Should checkpoints be implemented within the nested packages as well? Should checkpoints only be implemented within the nested packages? Again, remember that I only want to launch
estart the main package.
Thanks. Any insight would be appreciated.
View 2 Replies
View Related
Jul 16, 2007
Hi,
I am using check point in my packages , but i am not able to run my packages where it exactly got failed. The scenario is i am 100 rows at source system and i was loaded 95 records into target and due to the some data formatting issues i got failed at the 96th record. Later i am trying to re-execute the package, Surprisingly my package start run from the 1 st record(nothing but the start point of dataflow task).
How can i achive to run from where it excatly got failed(96th record) ?? is it possible using check points else is there any work-around approach ??please respond this post , it is very helpfull for me..
TQ
Sreenivas
View 1 Replies
View Related
Oct 14, 2006
Hi,
We are currently facing an issue in ensuring restartability of an SSIS package. The scenario is explained below.
Context:
The SSIS Package has two Data Flow tasks. The Data Flow task named DFT1 is the predecessor for DFT2 and chained with OnSuccess precedence constraint.
OnPreExecute and OnPostExecute event handlers have been implemented for DFT1. Each task in both event handlers as well as DFT1 and DFT2 have FailPackageOnFailure set to True.
Scenario1: Task in OnPreExecute of DFT1 fails.
DFT1 is attempted and succeeded.
OnPostExecute of DFT1 was not attempted.
DFT2 was not attempted.
Checkpoint file was created; however, no entries were made.
When restarted, execution started from first step in Control flow.
Scenario2: Task in OnPostExecute of DFT1 fails.
DFT1 and its OnPreExecute Event were executed.
DFT2 was not attempted.
Checkpoint file was created and entries were made. Entries had DTS:result as 0 for OnPreExecute and DFT1 tasks.
When restarted, DFT2 was executed. OnPostExecute event, which failed during previous execution, was not attempted.
Each task in the package, whether it is in Control flow or as part of an event handler is crucial for seamless execution. But apparently, as explained above, there is no reliability on the event handlers in case of failures. Has anyone encountered similar scenario? Is this behavior as per design of the runtime engine?
Thanks, in advance,
Regards,
Rajesh
View 2 Replies
View Related
Oct 24, 2006
Hi,
I have a master package with a sequence container with around 10 execute package tasks (for child packages), all in parallel. Checkpoints has been enabled in the master package. For the execute package tasks FailParentOnFailure is set to true and for the sequence container FailPackageOnFailure is set to true.
The problem i am facing is as follows. One of the parallel tasks fails and at the time of failure some of the parallel tasks (say set S1) are completed succesfully and few are still in execution (say set S2) which eventually complete successfully. The container fails after all the tasks complete execution and fails the package. When the package is restarted the task which failed is not executed, but the tasks in set S2 are executed.
If FailPackageOnFailure is set to true and whatever be the FailParentOnFailure value for the execute package task, in case of restart the failed package is executed but the tasks in set S2 are also executed.
Please let me know if there is any setting that only the failed task executes on restart.
Thanks in advance
View 1 Replies
View Related
Sep 21, 2015
I have a sequence container in my Package and this sequence has more than one control flow tasks.
Can I create the checkpoints such that only the failed component inside the sequence container runs again and not the other successful components/tasks in the sequence container?
View 3 Replies
View Related
Feb 13, 2007
Hi,
I have a FTP task in my control flow that download files from a FTP server. This ftp task is inside a foreach container that loops over a ADO recordset for the file name. The files that the ftp task pulls are huge. If the FTP task fails then I want the FTP task to restart and only download those files that have not been downloaded. Is this possible?
What possible configurations do I have to make to the foreach container and the filetask?
Thanks a lot in advance for your help and time.
Regards,
$wapnil
View 2 Replies
View Related
May 22, 2005
Hi there,
I have decided to move all my transaction handling from asp.net to stored procedures in a SQL Server 2000 database. I know the database is capable of rolling back the transactions just like myTransaction.Rollback() in asp.net. But what about exceptions? In asp.net, I am used to doing the following:
<code>Try 'execute commands myTransaction.Commit()Catch ex As Exception Response.Write(ex.Message) myTransaction.Rollback()End Try</code>Will the database inform me of any exceptions (and their messages)? Do I need to put anything explicit in my stored procedure other than rollback transaction?
Any help is greatly appreciated
View 3 Replies
View Related
Jul 12, 2006
Service Broker will let you create two services with the same name (one with contract and another without)
CREATE SERVICE [Order Msg Recieve] AUTHORIZATION [dbo] ON QUEUE [dbo].[Order Return Msg Queue]
CREATE SERVICE [Order Msg Receive] AUTHORIZATION [dbo] ON QUEUE [ODS].[Order Return Msg Queue] ([OrderSubmission])
When you delete the service....
drop service [Order Msg Recieve]
It will only drop the first one. In the BOL there is no syntax for telling it to delete the second one, however you can drop it from SQL Management Studio.
I stumbled over this by accident. Just FYI
Gary
View 1 Replies
View Related
Jul 12, 2007
Hi. I apologize if this isn't the correct forum for this question: Where can we ask for a feature in the next version of SQL Server? Thank you.
View 1 Replies
View Related
Jan 4, 2006
When upgrading a database from MSDE to Visual Studio 2005 Express, I've noticed that the system tables in the MSDB database don't get upgraded. For example, the SYSSCHEDULES table doesn't get added. Is this by design or a bug?
-Doug
View 6 Replies
View Related
Aug 8, 2007
Try this in sql server 2005:
select COALESCE(a1, char(254)) as c1 from
(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1
group by a1
with rollup order by c1
select COALESCE(a1, char(255)) as c1 from
(select 'Z' as a1 union select 'Ya' as a1 union select 'Y' as a1 union select 'W' as a1) as b1
group by a1
with rollup order by c1
The only difference is that the first one uses 254 and the second one uses 255. The first sorts like this:
WYYaZþ
The second one sorts like this:
WYÿYaZ
Is this expected behavior?
View 3 Replies
View Related
Jul 16, 1999
Has anyone noticed that if you created DTS package and try to
change connection properties (i.e. change DSN or redirect
DTS to different server or Database), as soon you click OK
it does not save new password and hence does not work
anymore. In my case to move Database from Development to
Production server I would have to recreate all DTS packages.
Is there any way around it?
Any ideas greatly appreciated.
View 1 Replies
View Related
Jan 21, 2004
Dig this....
USE Northwind
GO
CREATE TABLE ItemInformation([Description] varchar(80))
GO
INSERT INTO ItemInformation([Description])
SELECT 'CHOCOLATE CHIP‚' UNION ALL
SELECT '‚COOKIES‚' UNION ALL
SELECT '‚CROISSANTS *PLAIN*‚' UNION ALL
SELECT '‚DONUTS‚' UNION ALL
SELECT '‚DONUTS *DOZEN*‚' UNION ALL
SELECT '‚MUFFINS‚' UNION ALL
SELECT '‚BAGELS‚' UNION ALL
SELECT '‚ROLLS‚' UNION ALL
SELECT '‚CUPCAKES‚' UNION ALL
SELECT '‚CRISPIES‚' UNION ALL
SELECT '‚DANISH/SWEET ROLLS‚' UNION ALL
SELECT '‚FUDGE BROWNIES‚' UNION ALL
SELECT '‚PUFF PASTRIES/ECCLES‚' UNION ALL
SELECT '‚STICKY BUNS‚' UNION ALL
SELECT '‚TURNOVERS‚' UNION ALL
SELECT '‚BLACK & WHITE COOKIES‚' UNION ALL
SELECT '‚LINZER TARTS‚' UNION ALL
SELECT '‚SCONES/BISCUITS‚' UNION ALL
SELECT '‚SCUFFINS‚' UNION ALL
SELECT '‚SINFULL BITS‚'
GO
SELECT * FROM ItemInformation
GO
UPDATE ItemInformation
SET [Description] = REPLACE([Description],',','')
GO
SELECT [Description], LEN([Description]) FROM ItemInformation
GO
SELECT REPLACE([Description],',','')
FROM ItemInformation
SELECT REPLACE([Description],'C','')
FROM ItemInformation
SELECT CHARINDEX(',',[Description])
FROM ItemInformation
GO
DECLARE @x varchar(80)
SELECT @x = '‚COOKIES‚'
SELECT @x
SELECT REPLACE(@x,',','')
GO
DELETE FROM ItemInformation
GO
INSERT INTO ItemInformation([Description])
SELECT 'CHOCOLATE, CHIP‚' UNION ALL
SELECT 'CHOCOLATE, CHIP‚' UNION ALL
SELECT ',CHOCOLATE, CHIP‚' UNION ALL
SELECT ',CHOCOLATE, CHIP‚ ' UNION ALL
SELECT ',CHOCOLATE, CHIP‚ A' UNION ALL
SELECT ',CHOCOLATE, CHIP‚ , '
GO
SELECT REPLACE([Description],',','')
FROM ItemInformation
GO
DROP TABLE ItemInformation
GO
BIZZARO WORLD
View 14 Replies
View Related
Jun 22, 2007
Currently - our Reporting Services site is setup so that all Domain Users can access it. We are starting to use Report Builder now. I have enabled the My Reports folder feature that that creates a user folder for anyone that logs onto the site. Is there a way to just create the user folders for certain people?
View 1 Replies
View Related
Aug 23, 2007
I just installed SQLExpress, with reporting services on my own laptop
However, when I go to http://localhost/Reports/, I can see my reports uploaded, but the subscription links are not available. In Properties, I cannot see "Execution" either.
Can someone help me to get the Subscriptions feature enable again?
Thanks,
John W
View 1 Replies
View Related
Aug 31, 2006
We desperately require the RetainSameConnection to be set to True on our ADO.NET - ODBC connection manager. Unfortunately RetainSameConnection always defaults back to False when you open the package.
* Is RetainSameConnection supposed to work for the ADO.NET - ODBC combination?
* Is it a bug that it defaults back to false for the ADO.NET - ODBC combination?
Quick responses much appreciated!
Kristian
Details:
SSIS with SP1
ConnectionManagerType:
ADO.NET:System.Data.Odbc.OdbcConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Provider:
.NET ProvidersODBC Data Provider
Connection String:
Driver={iSeries Access ODBC Driver};system=AS400;dbq=GAPFILES;dftpkglib=QGPL;languageid=ENU;pkg=QGPL/DEFAULT(IBM),2,0,1,0,512;
View 2 Replies
View Related
Apr 26, 2006
I posted this over on, I guess, the other SQL Server forum (msdn.microsoft.com/newsgroups). Sorry if it's a cross post or better answered there.
Does anyone know if the feature packs are included in SP1 or still need to be installed separately.
View 1 Replies
View Related
Nov 15, 2007
Hi!
I am having a terrible time with the report feature. First of all, my queries through it do not match my queries thru SQL server 2005 Express Management. When I enter my fields, through the auto query function, it moves them. When I try to put them where I think they belong (by editing the SQL code, my queries get all messed up).
Is there a way to import my SQL query and run it through the report feature?
I am using windows XP Pro
Microsoft Visual Studio 2005 version 8.0.50727.42
Microsoft SQL Server Reporting Services Designers version 9.00.2047.00
Thank You so much......
View 1 Replies
View Related
Aug 14, 2004
Theres a feature in oracle that allows you to modify tables, colums, values and the data from its enterprise console the same way that you can in sql server. In oracle however theres a button called 'show sql' that allows you to see and copy/paste the resulting sql for the changes made via the console.
I would imagine that sql server has a similar option. The reason i ask is that i would like to more fully learn how to do this through the query analyser and get more familiar with sql involved and I would be able to do this if I could see the resulting sql from enterprise manager.
Hope this makes sense.
I did find something in sql server called 'generate sql' but this doesnt update during changes you make automatically.
Thanks
View 3 Replies
View Related
Feb 21, 2006
Hello,
I installed full-text feature on a previously installed SQL Server. It
seemed it installed and it did not give me error but when I run “EXEC
sp_fulltext_database 'enable'� it gives me no full-text search featured
installed message. Any idea?
View 2 Replies
View Related
Jun 9, 2000
I need some help...
I'm trying to execute a stored procedure and I'm getting this message
Run-Time Error '-2147217887 (80040e21)':
[Microsoft][ODBC SQL Server Driver]Optional feature not implemented
Here is the code:
Public Function D2L(sconnect As Variant, dDate As Variant) As Variant
Dim rsDate As ADODB.Recordset
Dim cmdDate As ADODB.Command
Dim prmDate As ADODB.Parameter
Set cmdDate = New ADODB.Command
Set ADOConn = New ADODB.Connection
ADOConn.Open sconnect
Set cmdDate.ActiveConnection = ADOConn
cmdDate.CommandText = "dbo.UP_CVRT_DATE_TO_LONG"
cmdDate.CommandType = adCmdStoredProc
Set prmDate = New ADODB.Parameter
prmDate.Type = adDate
'prmDate.Size = 32
prmDate.Direction = adParamInput
prmDate.Value = dDate
cmdDate.Parameters.Append prmDate
Set rsDate = cmdDate.Execute()
Thanks in advance for any responses...
View 1 Replies
View Related
Jan 6, 2005
Hi,
While reading (I have come across what seems to be maybe a powerful and useful feature) available in MSAS and thus this related question:
Can someone briefly explain the use of this feature and how it can come in handy.
So far I have skimmed through BOL and it says that "Only parent-child dimensions support this dimension characteristic" i.e. Write-enabled feature.
Thanks.
View 2 Replies
View Related
Sep 18, 2007
Hello there
I installed sql server 2005 developer edition
and the only error message i got was.
- IIS Feature Requirement (Warning)
Messages
IIS Feature Requirement
Microsoft Internet Information Services (IIS) is either not installed or is disabled. IIS is required by some SQL Server features. Without IIS, some SQL Server features will not be available for installation. To install all SQL Server features, install IIS from Add or Remove Programs in Control Panel or enable the IIS service through the Control Panel if it is already installed, and then run SQL Server Setup again. For a list of features that depend on IIS, see Features Supported by Editions of SQL Server in Books Online.
how do i get hold of Internet Information Services
I installed it on vista premium.
Please help
Regards
ROB
View 3 Replies
View Related
May 20, 2008
Hi All,
I know that reporting services is a reporting suite, but is there a way in which records/data/information can be submitted/updated from RS?
thanx
View 1 Replies
View Related
Jul 27, 2006
Has anyone seen this strange behaviour?
I have a package which loads the fact data from Stage into Warehouse database. This packages normally handles early arriving facts. In that package I use lookup to check the dims which exists, and where they don't I populate the dimension and use the surrogate key to load the facts. This works fine.
I had a request to load 7 years worth of historical data. Instead of re-writing the package I took the package which handles early arriving facts and deleted the section which handles early arriving facts. I knew all the dimensions already exists and I don€™t want to hinder the performance when I load millions of rows. During testing I found something very interesting.
If you have configured error path in the lookup component and removed the error path later, the package will NOT fail (won't produce error) even if the lookup can't find matching values.
Correct Behaviour Example 1:
[1] Stage fact table has 2 records, with product code 1 and 2.
[2] Warehouse Product table has only product code 1.
[3] Source - Lookup - Destination in the data flow task. Error port on lookup is not configured.
[4] From source we read 2 records, and the package will fail at lookup as it can't find Product Code 2.
Correct Behaviour Example 2:
[1] Stage fact table has 2 records, with product code 1 and 2.
[2] Warehouse Product table has only product code 1.
[3] Source - Lookup - Destination in the data flow task. Error port on lookup is configured to go to RowCount.
[4] From source we read 2 records, and the package will run successfully. It will put one record into warehouse table and send the invalid record into RowCount.
Incorrect Behaviour Example 3:
[1] Stage fact table has 2 records, with product code 1 and 2.
[2] Warehouse Product table has only product code 1.
[3] Source - Lookup - Destination in the data flow task. Delete the configured error port from lookup.
[4] From source we read 2 records, and the package will run successfully. It will put one record into warehouse table and discard the other.
My understanding if the error port is NOT configured as shown in example 2, it should fail as shown in example 1.
Am I missing a point or is this suppose to be a correct behaviour or is it a bug?
Thanks
Sutha
View 1 Replies
View Related
Mar 20, 2008
I tried SQL mirroring in beta 1 , then it was gone, until SP1.
Now I can not setup mirror, it is fine if it is just hard to setup, but it seems it is full of bug! The mirroring has to be stable, since I am trying to mirror product db, what a diaster if something goes wrong.
I am trying two servers, both has 9.0.3042. First I tried to setup on my home machine, I VPN to my network, After I config security, I see two connection strings:
TCP://sql8.mydomain.netsql2005:5022
TCP://sql5.mydomain.netsql2005:5022
and I click start mirroring, then I got error - Alter failed due to invalid connection string. Hard to figure it out, right?
Then I tried the same thing on a server in the network, this time I get
TCP://sql8.mydomain.net:5022
TCP://sql5.mydomain.net:5022
and it doesn't show that error anymore, I am not sure why the connection should be like the latter format, but in anyway, how come SMO can not make it right?
then I get another error, SQL server doesn't exist or can not access, I search on the Internet, it seems that error could mean anything, include that the mirror db is not in restore mode.
But I did set the mirror db in restore mode and both sql5 and sql8 are under same domain, pysically close.
So what else I can check?
Any suggestion?
thanks
View 5 Replies
View Related
