[SSIS Errors] The process cannot access the file because it is being used by another process

This is an error message that took me a few hours because

1. I could not find that particular solution on the internet and …
2. I did not look properly at the SSIS output

So I’d like to share it with you.

Information: 0x402090DC at Load Observations, Flat File Destination [268]: The processing of file “D:\test\Log.txt” has started.
Information: 0x402090DC at Load Observations, Flat File Destination 1 [283]: The processing of file “D:\test\Log.txt” has started.
Warning: 0x80070020 at Load Observations, Flat File Destination 1 [283]: The process cannot access the file because it is being used by another process.
Error: 0xC020200E at Load Observations, Flat File Destination 1 [283]: Cannot open the datafile “D:\test\Log.txt”.
Error: 0xC004701A at Load Observations, SSIS.Pipeline: Flat File Destination 1 failed the pre-execute phase and returned error code 0xC020200E.

When I first encountered this error it took me a long while to figure out what the problem was. Unfortunately I only looked at the error message Error: 0xC020200E at Load Observations, Flat File Destination 1 [283]: Cannot open the datafile “D:\test\Log.txt”. and not the helpful and informative warning before that.

I looked around the internet, asked my supervisor and our sysadmin for help (we thought it might be a permission issue) but then I finally read the three lines before the error.

• Flat File Destination [268]: The processing of file “D:\test\Log.txt” has started.
• Flat File Destination 1 [283]: The processing of file “D:\test\Log.txt” has started.
• Flat File Destination 1 [283]: The process cannot access the file because it is being used by another process.

BA-DUM-TSS! Do you see the problem? The file is already used by another process, namely, another Flat File Destination.

My setup was the following: one data flow with a few transformations, from which 2 should write possible errors to an error file.

The problem here is that in the data flow task, things do not necessarily happen sequentially. The data “flows” as it should through the data flow transformations that you define and when it is finished with one transformation it continues to the next. This means that several of the transformation might be “working” at the same time (have you ever noticed that rows do not need to be in the same order as when you extracted them?). This also means that a file connection I open at a data transformation node does not have to be closed when the first rows “reach” another data transformation with another connection to the same file.

Lessons learned:

• Don’t try to open the same file connection twice in the same package.
• Always read a few lines before the actual error message if the error message is not helpful