June 3, 2010 at 6:29 pm
Hi ,
I have a SSIS package which has the below design:
1) A forloop container
2) A SQL Task which has the has a begin transaction
BEGIN TRANSACTION TRANS_SRC_PRJ_BLB_SLICES
3) A DFT to load the data into the table with Fast Load method with Fastloadmaxcommitsize of 10000
4) SQL task to commit transaction
COMMIT TRANSACTION TRANS_SRC_PRJ_BLB_SLICES
Sometimes when the load happens, partial data is getting loaded in the target table and no error messages are coming. Again if we rerun the process partial load may happen or entire data gets loaded.
Some specification of database:
The data file of the database has unrestricted growth and the logfile has growth limit of 2GB.The auto shrink
The problematic DFT is loading around 11 miilion records.
June 3, 2010 at 11:55 pm
I do not think that this is the correct way to set up a transaction for what you are trying to do.
Try removing the BEGIN and COMMIT SQL tasks and instead set the 'TransactionOption' property of your DF component to 'Required'.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 4, 2010 at 2:46 am
If I use Execute SQL Tasks to force a transaction, I use the following commands:
BEGIN TRAN (at the beginning)
and
COMMIT (at the end).
Make sure the connection manager to your database has the property RetainSameConnection set to True.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 4, 2010 at 12:20 pm
I think you want to look at TransactionOption property..
From BOL:
NotSupported:
Specifies that no transaction will be started for this container, and consequently, the outcome of the current transaction, if one has been started by a parent container, will not affect the data that may be altered during execution of this container. This means that changes will not roll back, even if the parent container started a transaction.
Required:
Specifies that this container will cause a new transaction to be started unless the parent container already has a transaction, in which case, the parent’s transaction will be joined.
Supported:
Specifies that this container will not start a transaction. However, it will participate in a
transaction if the parent container started one. This is the default.
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply