ssis script task to dynamically export from a SQL Server database to an Excel sheet

Question

I am trying to create a SSIS package that will select all the value from a table in SQL Server to a table in Excel. The table is created on the run because it will change every time I run it. I can create the new table in the Excel sheet but I am having a lot of trouble getting the data in there.

I cannot do an openrowset query because the company I work for will not allow it. It cannot be done through the data task flow because i don't know what the headers at the start.

I have tried some script tasks but cannot figure out how to get it to the Excel sheet

Does anyone have any sample code or anything that will show me how to export from SQL Server to Excel dynamically?

    Dim cn As New OleDbConnection
    Dim sqlcn As New SqlConnection
    Dim adapter As New OleDbDataAdapter
    Dim dtset As New DataSet
    Dim dt As New DataTable
    Dim cmd As New OleDbCommand
    Dim sqlcmd As New SqlCommand
    Dim dr As DataRow

    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Excel 8.0;Database=E:\sheet.xls;" + "HDR=Yes;Readonly=False;IMEX=0;"
    sqlcn.ConnectionString = "Data Source=DB_NAME;Initial Catalog=Main;Integrated Security=True"
    cn.Open()
    sqlcn.Open()


    Dim da As New SqlDataAdapter("Select * from Temp_Totals", sqlcn)
    da.Fill(dt)

So far now I need to insert in to the Excel from the dt and I just am having trouble with that I think this will work I am not sure. If some one has a better idea I would love to hear it

Tim Taber · Accepted Answer · 2013-07-31 02:22:45Z

Here's a quick, down and dirty way to copy from a datatable to excel without having to iterate through each column/row of the datatable:

Private Sub ExportToExcel(ByVal dt As DataTable, ByVal outputPath As String)
    Dim xlApp As Application = CreateObject("Excel.Application") 
    Dim xlWorkbook As Workbook = xlApp.Workbooks.Add(Type.Missing)
    Dim sheetIndex As Integer = 0
    Dim col, row As Integer
    Dim xlSheet As Worksheet
    Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object

    For col = 0 To dt.Columns.Count - 1
        rawData(0, col) = dt.Columns(col).ColumnName
    Next

    For col = 0 To dt.Columns.Count - 1
        For row = 0 To dt.Rows.Count - 1
            rawData(row + 1, col) = dt.Rows(row).ItemArray(col).ToString
        Next
    Next

     Dim finalColLetter As String = String.Empty
    Dim colCharset As String = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
    Dim colCharsetLen As Integer = colCharset.Length

    If dt.Columns.Count > colCharsetLen Then
        finalColLetter = colCharset.Substring((dt.Columns.Count - 1) \ colCharsetLen - 1, 1)
    End If

    finalColLetter += colCharset.Substring((dt.Columns.Count - 1) Mod colCharsetLen, 1)


    xlSheet = CType(xlWorkbook.Sheets.Add(xlWorkbook.Sheets(sheetIndex), Type.Missing, 1, XlSheetType.xlWorksheet), Worksheet)

    xlSheet.Name = dt.TableName


    Dim xlRange As String = String.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1)
    xlSheet.Range(xlRange, Type.Missing).Value2 = rawData


    Dim firstrow As Range = CType(xlSheet.Rows(1, Type.Missing), Range)
    firstrow.Font.Bold = True
    firstrow.Select()
    firstrow.AutoFilter(1, Type.Missing, XlAutoFilterOperator.xlAnd, Type.Missing, True)
    xlSheet.Application.ActiveWindow.SplitRow = 1
    xlSheet.Application.ActiveWindow.FreezePanes = True
    xlSheet.Columns.EntireColumn.AutoFit()
    xlSheet.Range("A1").Select()
    xlSheet.PageSetup.Orientation = XlPageOrientation.xlLandscape
    With xlSheet.PageSetup
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .BottomMargin = 0.25
        .TopMargin = 0.25
        .LeftMargin = 0.25
        .RightMargin = 0.25
        .HeaderMargin = 0
        .FooterMargin = 0
    End With
    firstrow = Nothing
    xlSheet = Nothing


    For Each xls As Worksheet In xlWorkbook.Worksheets
        If xls.Name.Contains("Sheet") = True Then xls.Delete()
    Next

    xlWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)
    xlWorkbook.Close(True, Type.Missing, Type.Missing)
    xlWorkbook = Nothing


    xlApp.Quit()
    xlApp = Nothing


    GC.Collect()
    GC.WaitForPendingFinalizers()

End Sub

Thank you so much this is excaly what i was asked for BUT this i going to go on a server that does not have office installed there for i can not use Microsoft.Office.Interop.Excel
You can download the interop assemblies to install (without needing office) here: microsoft.com/en-us/download/details.aspx?id=3508

Collectives™ on Stack Overflow

ssis script task to dynamically export from a SQL Server database to an Excel sheet

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related