How does DTS manage large transactions with Oracle? I do understand the isolation levels provided by DTS. But if you are doing a data transformation for a large database, you might end up filling the log space. On way to get around this is to do partail commits. But I was not able to come up with a easy solution for this. Any information to this effect will be appreciated
Thansk in Advance

Either everything succeeds or nothing succeeds (2PC). Doing partial commits
and by this I presume you mean batched inserts, with smaller than the full
rowset, commit sizes will surely only leave you with a partial load if
something goes wrong.

The data regardless of whether the load is done using DTS or not will have
to be stored prior to insert.

One way you may want to do this is to load in batches but make sure you can
identify those batches in the destination. This way you will have a master
table detailing batches and you simply have a routine to reverse out that
data.

If you have done UPDATES in the middle then you will have a difficulty
rolling it back.

I would stick with my transaction handling in DTS so long as the Oracle
driver supports it.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Break It" <anonymous (AT) discussions (DOT) microsoft.com> wrote

for a large database, you might end up filling the log space. On way to get
around this is to do partail commits. But I was not able to come up with a
easy solution for this. Any information to this effect will be appreciated

I do agree with your argument that, I should allow DTS to handle transactions. Having said that, large tables would fill up your transaction logs. I would have ideally liked DTS to have some customization where, I can set the numbers of rows to be commited in a batch, and a final status when the whole transformation is done. This would be a very important feature when using DTS for data transformations for your data warehouse.
Are you aware if there are any such improvements in YUKON
There are atleast couple other ETL tools who provide this kind of feature.

So i can test you would like

1. A datapump say that exports n rows to another location.
2. To stop the log getting large you would like to partially commit those
rows.(every 100 rows say)
3. We move along and the datapump was task #2 and we are now at task #4 and
this fails.
4. We now want to rollback everything including the datapump rows.

If yes then SQL Server keeps a track of all rows inserted in the log so
should things crash it can roll forward or back on system restart. Without
this capability then SQL Server would become inconsistent. To keep the log
size low a transaction must be complete and have been flushed. The log
cannot be flushed of open transactions. What are the other ETL tools you
mention? Are they maintaining state somewhere else. Interesting !

A quick test was pump a number of rows to another table using a datapump
with a FETCH and INSERT batch size of 10
The table size was 300K

Join the transaction of the package

I then added an ExecuteSQL task that had some awful syntax within so would
fail.

Execute the package

The log on the destination grows and grows despite being in SIMPLE recovery
mode.

The ExecuteSQL tasks runs/fails and SQL Server issues a ROLLBACK.

SQL Server uses the log to maintain state and consistency so I am not sure
how these other tools can keep the log low without maintaining state
elsewhere.




--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Break it" <anonymous (AT) discussions (DOT) microsoft.com> wrote

logs. I would have ideally liked DTS to have some customization where, I can
set the numbers of rows to be commited in a batch, and a final status when
the whole transformation is done. This would be a very important feature
when using DTS for data transformations for your data warehouse.

Allan,

I have modified your steps as follows:

1. A datapump say that exports n rows to another location.
2. To stop the log getting large you would like to partially commit those
rows.(every 100 rows say)
3. We move along and the datapump was task #2 and we are now at task #4 and
this fails.
4. We do not want to rollback every thing if task #4 fails. In fact I am not even
interested in rollbacks, as long as I know that the ETL has failed. So this load is not useful
for creating cubes (i.e. If you use cubes as you final deliverable)
5. The ETL admin is then notified of the failure and takes further action.

The above idea can be matured, in that if a certain thershold is reached,
then you call the ETL successful. For example 90%.

Cognos does this by allowing the users to costomize the number of rows to
commit.

OK

Notification of failure should be fine.

90% of what? The package or a task? Interesting variation that no DTS does
not have as far as I can tell from what you want.

In DTS you can allow certain thresholds for errors i.e. I am happy that 1000
rows can fail and we will still march on.

In Yukon you will do it dlightly differently.

You will say

OK so this row is a bad row - handle it. I want you to log the row to
another place and when we get through with all this package mail the admin a
list of things that went badly.

There will be occasions where we can handle those errors more gracefully.
If you have a bad row you can then check why it is bad against a criteria
you set (char val in a supposed integer field). You can then manipulate the
data say put in a default value and then merge it back into your stream.

In your situation though we could still do some of this in that so long as
we can identify the batch rows we can then compare aginst these when we have
sorted out the resons for the failure with the source.


BTW. This isn't really a transaction is it in the sense that it either goes
in or it does not? You are more interested in "If the row fails to go in
then let the admin know or we fail n times or <90% of the package completes
without error.




--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Breat it" <anonymous (AT) discussions (DOT) microsoft.com> wrote

So i think you would need to build your own monitoring into the process.

Be able to identify rows that are to be loaded
To keep the log down utilise FETCH and COMMIT sizes
Minimise indices on the destination
SIMPLE recovery model on destination
Keep a record of failures and good rows so a comparison can be done at the
end of the package
Notify the admin of success or failure. Of course here could be success but
failed to load all rows from source.



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Breat it" <anonymous (AT) discussions (DOT) microsoft.com> wrote