Yes, but where in the control flow will depend on how large you want to scope the transaction. You wouldn't just put it on the Truncate task. You...

OK, then the transaction that you are creating (by setting the Transaction Option to Required) needs to be scoped out to include the Truncate. Instead of setting the Transaction...

I did not know you had a step in the package to truncate the table. Where is that happening? Inside the data flow or in the control flow?

So you've got 2 issues here.

1. E-mail notification on error - you said that it did not send an email when your flat file destination encountered an error....

I would cluster the date column only and create a non-clustered index on your key columns linked to dimensional tables.

When you query the data, filter the query based...

roundel1900 (8/11/2009)

---

Maybe I am not explaining this correctly. Yes it appends the rows but without regards to the keys. So as it stands now I can continually append the exact...

Thanks for the info Greg. I've got a busy morning ahead of me, but I'll get something back to you today sometime. I'll share with you how I...

Pretty simple then, since you'll be returning the results from the SP via varchar(max) data type, add the XML header info to the top of the varchar string and away...

So you're using an IDENTITY column as a surrogate key for your parent rows. Is there a business key available that you could use to find the parent row...

Jack Corbett (8/10/2009)

---

Duh, why didn't I think...

So just to be clear, your application is tracking user events in the form of XML messages in a table. You want to move those messages into other tables...

You may be better off adding some logic to your data flow to ensure that there is only 1 instnace of any given row id in the pipeline. What...

Jack's got some good questions here. You have not told us much about what you are doing.

In general, you'll need 2 data flows. One to load the parent...

Yes. If you put a Send Mail task in the OnError event, you'll get an e-mail when the task raises an error.

You should be able to accomplish this by seting the Maximum Insert Commit Size setting on your OLE DB Destination Fast-Load component.