I have a simple SSIS package with a ForEach Loop Container with 1 Data Flow task that triggers when a file is present. If that task succeeds, I want to move the input file to a "success" folder. If it fails, I want to move the file to an "error" folder. I am not seeing an error (red arrow) from the data flow task. How do I access the error condition for the data flow task?
Conditionally move files in SSIS
ssis
Related Solutions
And so to force the same session to be used, in your connection manager, under the Properties settings, you can set the RetainSameConnection to True to force a single connection for your package versus using a pool.
Yes. You will need to configure all the servers involved with your process to be of DTC.
When you use the native SSIS transaction capability, if the connection managers are entirely self contained, then behind the scenes a standard BEGIN TRAN...COMMIT|ROLLBACK are issued. When there are multiple servers involved, that's when the distributed transaction coordinator comes into play. Even if you're only reading from a source table, SSIS will attempt to enlist that SELECT statement in a transaction.
We learned this the hard way when dealing with DB2 as they apparently have the ability to enable/deny transaction stuff. We also had issues using an antiquated MySQL instance as it didn't support the concept of a transaction. In that instance, we had two data flows. One ran without a transaction and wrote to a local file, then used a transaction controlled data flow to read that file into our target table. Not an advisable approach for very large data sets but worked fine for our problem set.
Best Answer
I found the answer to my question.
The answer that worked for me was to add the 2nd move File System Task. Then when connecting to previous Data Flow Task, right click on the Precedence Constraint and click the "Failure" option. I now have my error condition!