There is a ssis package I am building where ultimately its in a loop. It takes data from an oledb source and sends it over and creates the csv file dynamically. Now as a loop it will create files based on the array system so let say it loops 5 times and creates 5 different files.
A situation I don't want to happen is if let's say there are 50,000 records from oledb source. That is being sent over to the flat file destination [the csv file] but for some reason it fails. As a result only 20,000 records is sent with that file being created. But this is wrong because this is a partial file since it only has 20,000 records when it should have 50,000 records theoretically.
Is there a way to prevent that partial file from being created in the first place when the job fails?
So what I would want is the same scenario 50,000 records sent over to csv file, but job fails in the middle, only was able to send 20,000 records, but the csv file isn't created because it failed.
Is this possible?
I tried to add in a counter mechanism but it only works if the file is created or not but doesn't account for the contents of the file.
1条答案
按热度按时间ldfqzlk81#
You can achieve this by dividing the process into two steps: the first one being the creation of a temporary file, and the second being the renaming or moving of this file to its final destination, only if the first process completes successfully. If the process fails, you'll just be left with a temporary file that doesn't interfere with your final output and can be deleted.
Create a temporary flat file connection (i.e.,
temp_output.csv) and send your OLEDB data to this temporary file.Once the data flow task finishes successfully, use a File System Task in the Control Flow to rename
temp_output.csvto the final desired filename (i.e.,final_output.csv).If the Data Flow Task fails for any reason (like when only 20,000 of 50,000 records were sent), the file is not renamed, and the partially complete
temp_output.csvremains. You can add another step to delete the temporary file at the beginning of your package execution to ensure that a failed previous execution does not interfere with the new one.