First of all, i'm very new to SSIS, so i apologize if my question is stupid!
Also, i apologize for my english, it's not my first language.
So, I would like to know what is the best way to achieve my problem.
We have some flat CSV files we would like to store in tables in our SQL Server 2012 database. Each file correspond to a table in this DB. We need to load theses files in a specific order, because of the foreign keys.
What we want to achieve, is that if one file can't be loaded in the database, none file are loaded. So it's a simple transaction.
I know there are several possibilities:
** Load all datas in a temporary database and then load this database to the other. We prefer not to use this one.
** Use the transact SQL statement to begin transaction and rollback if it failed. I haven't succed to achieve that. I'm not sure we can use this in my particular case.
Thanks in advance for your advices.
For SSIS transactions, you will need to drag and drop a "Sequence Container" on to the control flow, and change "TransactionOption" to required. Then move your data flow items to be inside of the sequence container. Then if one data flow fails, all of the ones within the sequence container are rolled back. Without seeing the overall SSIS package, this should get you close to your goal.