Under the "Transaction" section of the Option tab in the
workflow properties of a SQL task step, there is an option
to check "Commit transaction on successful completion of
this step".

Do I have to check this in order to have each SQL task step
(s) to commit when completed?...in this case, each SQL
task step of my DTS package is an insert statement into
the FACT and DIM tables. The SQL task steps currently do
NOT explicitly use the COMMIT statement within their SQL
statements.

What is actually happening if I don't have this check in
each of the SQL task steps within a package? (Note: Some
of these steps run in parallel). Does this mean
transactions are not committed until ALL steps are
successfully completed within the package? Does it impact
memory or temp space? And impact on transaction log?

Thanks in advance.

If you set thhis property to checked the the transaction will commit on this
step.

The way you probably want it to work is that the package commits the
transactions for you (Package Properties | Advanced)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"Steven Wong" <anonymous (AT) discussions (DOT) microsoft.com> wrote