Hey there,
I have a Data Flow Task within a ForEach loop container. The source of the flow is ADO.NET connection and the destination is a Flat File Connection. I loop through a collection of strings in the ForEach loop. Based on the string content, I write some data to the same destination file in each iteration overwriting the previous version.
I am running into following Errors:
[Flat File Destination [38]] Warning: The process cannot access the file because it is being used by another process.[Flat File Destination [38]] Error: Cannot open the datafile "Example.csv".
[SSIS.Pipeline] Error: Flat File Destination failed the pre-execute phase and returned error code 0xC020200E.
I know what's happening but I don't know how to fix it. The first time through the ForEach loop, the destination file is updated. The second time is when this error pops up. I think it's because the first iteration is not closing the destination file. How do I force a close of the file within Data Flow task or through a subsequent Script Task.
This works within a SQL 2008 package on one server but not within SQL 2012 package on a different server.
Any help is greatly appreciated.
Thanks!