How to Convert Multiple excel files to CSV using SSIS

Hi, I'm not sure if you want to convert each sheet of those excel files into a separate CSV file, or each excel file to a CSV or everything into a single CSV, but nevertheless here is a Script Task in C# that will convert everything into a single CSV:

string[] excelFilePaths = { "Book1.xls", "Book2.xls", "Book3.xlsx" };
var csvFile = new ExcelFile();
var csvSheet = csvFile.Worksheets.Add("Sheet");
int csvRowI = 0;
 
foreach (var ef in excelFilePaths.Select(path => ExcelFile.Load(path)))
    foreach (var ws in ef.Worksheets)
        for (int ri = 0; ri < ws.Rows.Count; ri++, csvRowI++)
            for (int ci = 0; ci < ws.Rows[ri].AllocatedCells.Count; ci++)
                csvSheet.Cells[csvRowI, ci].Value =
                    ws.Cells[ri, ci].GetFormattedValue();
 
csvFile.Save("Result.csv");

You need to add a reference to this Excel component for .NET and also as you can see it reads both Excel formats (older XLS and newer XLSX) in same manner in .NET.

http://msbi-tips.blogspot.com/2012/02/convert-csv-to-excel-dynamically.html

http://stackoverflow.com/questions/17843053/ssis-using-script-task-convert-xls-to-csv

http://social.msdn.microsoft.com/forums/sqlserver/en-US/b6edae1c-1b3a-4a78-b7fc-1df081ba074e/using-ssis-convert-excel-in-to-csv