Error: 0xC020200E at DO, Flat File Destination [7676]: Cannot open the datafile "C:\Temp.txt".
Error: 0xC004701A at DO, DTS.Pipeline: component "Flat File Destination" (7676) failed the pre-execute phase and returned error code 0xC020200E.
I am new to SSIS, so I'm sure I have a setting wrong or something. Is the problem that SSIS is trying to write to a file from which it is simultaneously reading data?
Thank you.
Allen H wrote:
I have a package set up basically with two consecutive data flows. The first flow takes data from an OLE DB Source and stores it into a Flat File Destination. The second flow uses this same flat file as a source, alters the data, and stores the data in the same flat file, overwriting the old file. I set DelayValidation to True on the flat file. Still, here are the error messages I am receiving: Error: 0xC020200E at DO, Flat File Destination [7676]: Cannot open the datafile "C:\Temp.txt".
Error: 0xC004701A at DO, DTS.Pipeline: component "Flat File Destination" (7676) failed the pre-execute phase and returned error code 0xC020200E.
I am new to SSIS, so I'm sure I have a setting wrong or something. Is the problem that SSIS is trying to write to a file from which it is simultaneously reading data?
Thank you.
You'll have to set DelayValidation=TRUE on the second data-flow.
You should probably use raw files rather than flat files. Unless that is you want to use the files elsewhere, other than in SSIS.
-Jamie
|||I tried some things in this order:1) I set DelayValidation = TRUE on the second data flow. This was when I still had the flat files implemented. Same error as before.
2) I took out all of the flat files and put in raw files to store the data. Now when I run I get these new errors, based in the second data flow:
Error: 0xC0202069 at DO, Raw File Source [7848]: Unexpected end-of-file encountered while reading 4 bytes from file "C:\Temp.raw". The file ended prematurely because of an invalid file format.
Error: 0xC0047038 at DO, DTS.Pipeline: The PrimeOutput method on component "Raw File Source" (7848) returned error code 0x80004005. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at DO, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at DO, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at DO, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
3) This is just a question. Why raw files for this instead of flat files? The raw file is about 60% larger than the flat file after the data has been populated in each.
|||
I tried some things in this order: 1) I set DelayValidation = TRUE on the second data flow. This was when I still had the flat files implemented. Same error as before.
2) I took out all of the flat files and put in raw files to store the data. Now when I run I get these new errors, based in the second data flow:
Error: 0xC0202069 at DO, Raw File Source [7848]: Unexpected end-of-file encountered while reading 4 bytes from file "C:\Temp.raw". The file ended prematurely because of an invalid file format.
Error: 0xC0047038 at DO, DTS.Pipeline: The PrimeOutput method on component "Raw File Source" (7848) returned error code 0x80004005. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
Error: 0xC0047021 at DO, DTS.Pipeline: Thread "SourceThread0" has exited with error code 0xC0047038.
Error: 0xC0047039 at DO, DTS.Pipeline: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
Error: 0xC0047021 at DO, DTS.Pipeline: Thread "WorkThread0" has exited with error code 0xC0047039.
Strange. Its as if the second data-flow is trying to read the file before it has finished been written. This would be consistent with the behaviour you were getting with flat files.
3) This is just a question. Why raw files for this instead of flat files? The raw file is about 60% larger than the flat file after the data has been populated in each.
They're much much quicker.
-Jamie
|||
I have a package set up basically with two consecutive data flows. The first flow takes data from an OLE DB Source and stores it into a Flat File Destination. The second flow uses this same flat file as a source, alters the data, and stores the data in the same flat file, overwriting the old file. I set DelayValidation to True on the flat file. Still, here are the error messages I am receiving: Error: 0xC020200E at DO, Flat File Destination [7676]: Cannot open the datafile "C:\Temp.txt".
Error: 0xC004701A at DO, DTS.Pipeline: component "Flat File Destination" (7676) failed the pre-execute phase and returned error code 0xC020200E.
I am new to SSIS, so I'm sure I have a setting wrong or something. Is the problem that SSIS is trying to write to a file from which it is simultaneously reading data?
Thank you.
I think your problem is that you are trying to use the same flat file as a source and destination in the second data flow. SSIS has to get a write lock on the file to use it as a destination, but it probably can't since you've already got it open for reading. I'd alter Jamie's suggestion a bit: write the results of your first data flow to a RAW file, then use that as the source in your second data flow. Then have the second data flow write to the text file as it's destination. That way you are not trying to modifiy the source you are reading from, and you shouldn't get locking errors.
|||
Allen H wrote:
I have a package set up basically with two consecutive data flows. The first flow takes data from an OLE DB Source and stores it into a Flat File Destination. The second flow uses this same flat file as a source, alters the data, and stores the data in the same flat file, overwriting the old file. I set DelayValidation to True on the flat file. Still, here are the error messages I am receiving: Error: 0xC020200E at DO, Flat File Destination [7676]: Cannot open the datafile "C:\Temp.txt".
Error: 0xC004701A at DO, DTS.Pipeline: component "Flat File Destination" (7676) failed the pre-execute phase and returned error code 0xC020200E.
I am new to SSIS, so I'm sure I have a setting wrong or something. Is the problem that SSIS is trying to write to a file from which it is simultaneously reading data?
Thank you.
I think your problem is that you are trying to use the same flat file as a source and destination in the second data flow. SSIS has to get a write lock on the file to use it as a destination, but it probably can't since you've already got it open for reading. I'd alter Jamie's suggestion a bit: write the results of your first data flow to a RAW file, then use that as the source in your second data flow. Then have the second data flow write to the text file as it's destination. That way you are not trying to modifiy the source you are reading from, and you shouldn't get locking errors.
Yeah John's spot on. I glossed over this bit initially: "...and stores the data in the same flat file..."
Sorry about that.
Thanks John.
-Jamie
