Hi there! I have a package that runs and loads data into the database using a 'data flow task' in the control flow view. When I double click on it It shows me the data flow view where I can see an OLE DB Destination that writes to the database. When I ran it the package failed due to badly formed data in the input file. Can anyone tell me when SSIS fails does it actually write to the database up until the point of failure or does it rollback the changes? Cheers, Zoe

No rollback... You need to implement that your self: http://microsoft-ssis.blogspot.com/2011/01/ssis-transactions.html or with TSQL http://microsoft-ssis.blogspot.com/2011/09/ssis-transactions-with-tsql.html Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

For failure-prone data sources you can also load data to additional side table. After successful load move the data to destination table, use metadata only operations like ALTER TABLE SWITCH. That way you are not locking destination table for long time and you can switch multiple tables within one, short transaction.