July 4, 2012 at 12:22 am
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
July 4, 2012 at 12:32 am
Checkpoints work at the task level, not at the row level in the dataflow.
This means that the entire dataflow will be restarted.
Which makes sense of course. Data flows through the pipeline of the dataflow. By the time the package fails, a certain part is already written to the destination (may or may not be committed yet), some part is in memory and another part needs to be read yet. If you restart the package, there's no data in memory, so it has to start over. Furthermore, look at the dataflow as one transaction. It reads data and puts it into a destination. If a checkpoint would be able to restart a dataflow at the row level, it would mean it can restart a transaction in the middle, which violates ACID properties.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 4, 2012 at 10:22 pm
Thanks for u r reply.
Is any other solution is there for achieving that one?
July 5, 2012 at 12:26 am
sudhakar.siram (7/4/2012)
Thanks for u r reply.Is any other solution is there for achieving that one?
Hi Sudhakar,
As Koen mentioned that Checkpoints can be configured for Control Flow tasks only not for the data components individually.
FYI,according to MS, "SQL Server, perform operations in atomic units. This means that a single statement or series of statements is either successful and affects data or is not successful and the system returns the data to the state it was in before the attempted statement execution".
So I wonder if there is any solution at row level for you.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
July 6, 2012 at 12:02 am
If your source system is a database, you could add WHERE NOT EXISTS to your SQL SELECT statement, so that you only read rows that don't exist in the destination.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply