Isn’t it a desirable request to run your Data flow Tasks into Transaction Mode that rolls back if something wrong happened in the middle. Will the functionality is there but you’ll need some configuration to bring it into life. Let’s have a look together.

First you want to change the TransactionMode in the DataFlowTask properties from supported to Required

 Now you need a log holder for holding the data until the Commit or rollback. The Distrebuted Transaction Coordinator "DTC" is your target we need to enable it as follows

1. Go to the component services in the administrative tools of Control Panel.

2. Expand component services > Computers > My Computer > Distrebuted Transaction Coordinator

3. Then right click go to properties security tab.

4. Check Network DTC Access

5. Check Allow Remote Clients

6. Check Allow Inbound

7. Check Allow Outbound

8. Choose No Authuntication required

9. Check Enable SNA LU 6.2 Transactions

10. Then confirm it’ll require the service restart.

After this you can try to run the Data flow in transaction mode. If you received “Bulk Insert with another outstanding result set should be run with XACT_ABORT on”

Switch off the Bulk Insert in the Data Destination component that’s in your Data Flow tasks. As it triggers an error regarding Deadlock checker of SQL Server 2008 r2 more info could be found here

http://support.microsoft.com/kb/2412203

 Hope this Helps !!! and say goodbye to debugging data what inserted and what not inserted after errors

SQL Server page is 8K so remember when you format your hard disk. Create it in a 64K clusters for the best performance. Let’s no more about the SQL Page

·         96 Bytes for the page Header

·         8096 for the page rows with a limits 8060 bytes Only for rows.

·         2 bytes in the end of the page determine the location of each Row inside the page

·         Rows aren’t sorted inside the page but the pointers for it sorted according to the index.

·         When SQL writes the rows if the remaining of the page won’t fit the whole row, SQL leaves this page and start a new page.  

I hope this may help you with your next SQL installation