How do I replace CRLF with LF in a .CSV file with a Script Task in SQL 2005 SSIS?
Posted on 2008-10-14
I am developing an SSIS package in SQL 2005 that downloads files via FTP and validates them prior to processing. The files are .CSV and generated in a Unix environment, the top line has a Checksum in the first column followed by a line of empty columns followed by the data within the file. The checksum generated is based upon the contents of the file minus the first two lines.
In a script task I am able to read the lines of the file into an array, eliminate the first two lines and calculate the checksum based on the remaining data. My problem is my checksum does not match the checksum provided, most likely because the LF is being replaced with CRLF.
How can I remove the CRLF programatically within VB.NET so I can automatically remove the two lines from the top of the file, eliminate any formating changes Windows may have made (LF to CRLF) and validate the checksum?
This must be an entirely automated process with no intervention of users and also maintain performance as high as possible. My process has 90 minutes to process thousands of these files with 10's of thousands of lines in each file.