Transactions DTS Package (EM); Rollback actual work, but commit diagnostic info

I have not.  I will check to see what that does and report back.
Thanks!

Logging, along with exception logging, and not using [x] 7.0 compatibility looks like it could provide enough information for us to figure out what went wrong when a step fails.  Our custom log table records the step name, time and @@ROWCOUNT.  I'm not sure if the built-in logger will match the @@ROWCOUNT information, which could come in handy finding the bad row.

The biggest snag, however, is that logging is only available on data transformation and data driven query tasks, and most of our tasks are execute sql tasks.  I think we'd have to redo all those to be data driven queries so that we'd have logging available, which is a ton of work.

Are you sure there isn't a way to isolate the "insert into the custom status table" tasks from the rest--trasactionally speaking?  They are separate tasks in the DTS package (on failure workflow).

?

>>Are you sure there isn't a way to isolate the "insert into the custom status table" tasks from the rest--trasactionally speaking?<<
I really could not say.  The way we do it is to always import into a staging table first and then validate, prior to importing.

This:
>> prior to importing.<<

Should have read:
 prior to importing into the final production tables.

But when you process the staging table, how do you know where it fails validation?  The DTS package with all the tasks and connections is a very cool way to bring outside data into SQL.  However, when something goes wrong, it's nice to have the "undo" provided by the transaction rollback.  Tell me this, if your DTS package writes to the System Application Log, a custom log for your app that is, do those log writes get rolled back with the writes to the SQL database?  If they persist after the rest is rolled back, I could use that, maybe.  Do you know?
Thanks!

>>But when you process the staging table, how do you know where it fails validation?<<
Once the data is imported into the staging table you can then implement all the appropriate validation.  For example, check to see if the dates are valid dates (assuming they were imported into a varchar column), as in:

If EXISTS (Select 1 From StagingTable Where ISDATE(YourDateColumn) = 0
   Begin
        -- Here you add all your Error Handling code for this event.
   End

>>Tell me this, if your DTS package writes to the System Application Log, a custom log for your app that is, do those log writes get rolled back with the writes to the SQL database?<<
Not sure what you are talking about here:
If it is the Log Package execution I mentioned previously, the answer is no.  It is not rolled back.
If it is the Error file in the same Logging tab, than you should know this is a text file, so there is no "rollback".