SSIS Script task to convert XLSX files with multiple sheets to CSV file

**** posting again moved previous post vb forums, the reason here on c#, vb code that can  fit ssis script task****

hello,

i have been strunggling find solution convert xlsx files multiple sheets csv file. can 1 please me.

requirements

>> convert xlsx file multiple sheets csv file

>> csv file names : xlsx filename + '_' + sheet name

>> scirpt has in vb using ssis 2005

>> started develping scirpt using micorosoft.office.interop.excel.dll . dll referenced script task.

>> found bellow web link useful.. because not god @ progarmming, couldn't understnad of it.

http://www.mssqltips.com/sqlservertip/2772/importing-data-from-excel-using-ssis--part-2/

http://www.dreamincode.net/forums/topic/278621-help-convert-xls-to-csv-vbnet-by-worksheet/

thanks in advance

jai

' microsoft sql server integration services script task ' write scripts using microsoft visual basic ' scriptmain class entry point of script task. imports system imports system.data imports system.math imports microsoft.sqlserver.dts.runtime imports excel = microsoft.office.interop.excel imports microsoft.office.interop.excel public class scriptmain     private shared mworkbook workbook     private shared mworksheets sheets     private shared mwsheet1 worksheet     private shared oxl excel.application     private shared errormessage string = string.empty     enum scriptresults         success = microsoft.sqlserver.dts.runtime.dtsexecresult.success         failure = microsoft.sqlserver.dts.runtime.dtsexecresult.failure     end enum     public sub main()         dts.taskresult = dts.results.success         try             dim sourceexcelpathandname string = "c:\users\vijay\documents\m.xlsx"             dim targetcsvpathandname string = "c:\users\vijay\documents\multi"             dim excelsheetname string = "sheet1"             dim columndelimeter string = "|#|"             dim headerrowstoskip integer = 0             if convertexceltocsv(sourceexcelpathandname, targetcsvpathandname, excelsheetname, columndelimeter, headerrowstoskip) = true                 msgbox("passed", msgboxstyle.information)             else                 dts.taskresult = cint(scriptresults.failure)             end if         catch ex exception             dts.taskresult = cint(scriptresults.failure)             msgbox(dts.taskresult, msgboxstyle.information)         end try         dts.taskresult = dts.results.success     end sub     public shared function convertexceltocsv(byval sourceexcelpathandname string, byval targetcsvpathandname string, byval excelsheetname string, byval columndelimeter string, byval headerrowstoskip integer) boolean         try             oxl = new excel.application()             oxl.visible = false             oxl.displayalerts = false             dim workbooks excel.workbooks = oxl.workbooks             mworkbook = workbooks.open(sourceexcelpathandname, 0, false, 5, "", "", _             false, xlplatform.xlwindows, "", true, false, 0, _             true, false, false)             mworksheets = mworkbook.worksheets             dim ws worksheet             each ws in mworksheets                 ws.select()                 dim range excel.range = ws.usedrange                 dim rngcurrentrow excel.range                 integer = 0 headerrowstoskip - 1                     rngcurrentrow = range.entirerow                     rngcurrentrow.delete(xldeleteshiftdirection.xlshiftup)                 next                 range.replace(vblf, " ", type.missing, type.missing, type.missing, type.missing, _                 type.missing, type.missing)                 range.replace(",", columndelimeter, type.missing, type.missing, type.missing, type.missing, _                 type.missing, type.missing)                 dim targetfile string                 targetfile = targetcsvpathandname & ws.name & ".csv"                 mworkbook.saveas(targetfile, xlfileformat.xlcsvmsdos, type.missing, type.missing, type.missing, type.missing, _                 microsoft.office.interop.excel.xlsaveasaccessmode.xlexclusive, type.missing, type.missing, type.missing, type.missing, false)                 if ws isnot nothing                     ws = nothing                 end if             next             if mworkbook isnot nothing                 mworkbook.close(type.missing, type.missing, type.missing)             end if             if mworkbook isnot nothing                 mworkbook = nothing             end if             return true         catch ex exception             errormessage = ex.tostring()             return false                     if oxl isnot nothing                 oxl.quit()             end if             system.runtime.interopservices.marshal.releasecomobject(oxl)             if oxl isnot nothing                 oxl = nothing             end if             gc.waitforpendingfinalizers()             gc.collect()             gc.waitforpendingfinalizers()             gc.collect()         end try     end function end class

SQL Server  >  SQL Server Integration Services