' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the 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 As Workbook
    Private Shared mWorkSheets As Sheets
    Private Shared mWSheet1 As Worksheet
    Private Shared oXL As Excel.Application
    Private Shared ErrorMessage As 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 As String = "C:\Users\Vijay\Documents\m.xlsx"
            Dim targetCSVPathAndName As String = "C:\Users\Vijay\Documents\multi"
            Dim excelSheetName As String = "Sheet1"
            Dim columnDelimeter As String = "|#|"
            Dim headerRowsToSkip As Integer = 0
            If ConvertExcelToCSV(sourceExcelPathAndName, targetCSVPathAndName, excelSheetName, columnDelimeter, headerRowsToSkip) = True Then
                MsgBox("Passed", MsgBoxStyle.Information)
            Else
                Dts.TaskResult = CInt(ScriptResults.Failure)
            End If
        Catch ex As 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 As String, ByVal targetCSVPathAndName As String, ByVal excelSheetName As String, ByVal columnDelimeter As String, ByVal headerRowsToSkip As Integer) As Boolean
        Try
            oXL = New Excel.Application()
            oXL.Visible = False
            oXL.DisplayAlerts = False
            Dim workbooks As 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 As Worksheet
            For Each ws In mWorkSheets
                ws.Select()
                Dim range As Excel.Range = ws.UsedRange
                Dim rngCurrentRow As Excel.Range
                For i As Integer = 0 To 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 As 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 Then
                    ws = Nothing
                End If
            Next
            If mWorkBook IsNot Nothing Then
                mWorkBook.Close(Type.Missing, Type.Missing, Type.Missing)
            End If
            If mWorkBook IsNot Nothing Then
                mWorkBook = Nothing
            End If
            Return True
        Catch ex As Exception
            ErrorMessage = ex.ToString()
            Return False
        Finally
            If oXL IsNot Nothing Then
                oXL.Quit()
            End If
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL)
            If oXL IsNot Nothing Then
                oXL = Nothing
            End If
            GC.WaitForPendingFinalizers()
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
        End Try
    End Function
End Class

• Marked as answer by V_jai Friday, December 12, 2014 2:15 PM

This did it! I was looking to extract several worksheets into separate .csv files so I can then import using SSIS. Thanks V_jai!

• Edited by MystklFrets1 7 hours 21 minutes ago