SQL Server SSIS Transaction Handling

My organization is upgrading to SQL Server 2008 from 2000 (yay!) and I am unfamiliar with the inter workings of Integration Services.

I currently manage very large databases that store business process transactions that amount to between 500000 and 1000000 transactions daily. We've had very poor management of the databases in the past and they have thus grown to an unmaintainable size. I'm working to provide daily archival of the databases so that the working databases are more manageable. I wrote several stored procedures to do an archive and subsequently prune the working databases. However, in dabbling with Integration Services, I've found great built in functionality for the job that my SPs currently do.

What I've created are several SSIS packages that perform an export/import. Since I'm only interested in certain data, I use a custom query in the packages that is of the form:

DELETE TransactionTable
OUTPUT
DELETED.*
WHERE (EventTimestamp >= 
DATEADD(D, 0, DATEDIFF(D, 0, (SELECT MIN(EventTimestamp) FROM TransactionTable)))
)
AND (EventTimestamp < 
DATEADD(HH, 0, (DATEADD(YY, -1, DATEDIFF(D, 1, GETDATE()))))
);

This query grabs the data I'm interested in and deletes it from the working table. Using SSIS, this query produces the output that is placed into the archive table.

My question(s) are:

Since I want to import records into my archive and delete those records from the working database within the same SSIS package to ensure consistency, this query seems to be the way to do it. However, I'm concerned about the structure of the transaction. I'm deleting records from my working database as output to be inserted into m开发者_开发问答y archive database.

How does SQL Server handle errors in this case?

Is running this package safe?

What happens if the output generated by the statement is invalid and an error occurs?

Does the statement get rolled back?

Will the DELETE only be committed if all of the output was able to be transferred to the archive?

If not, how might I be able to achieve a fail-safe condition?

The good news is, you can set the SSIS package to rollback a set of tasks if any failure occurs.

There is a TransactionOption property that is available on pretty much every container/task, including the package itself. You can set it to Required, Supported, and NotSupported.

You can details on each option here: http://msdn.microsoft.com/en-us/library/ms137690.aspx

Obviously play around with this a bit, forcing errors on different steps to see what the result is.