Click here to Skip to main content
11,482,039 members (66,374 online)
Rate this: bad
Please Sign up or sign in to vote.
I have a SSIS package in which I've added a Script Task. In the Script Task I've given a oledb connection manager and acquired a data table from the database and the query is just working fine. Now I have to put the data table into an excel file. This is the primary solution I need to arrive at.
There are two scenarios though

Scenario 1 : Creating an excel file template WITHOUT HEADERS and put the data into that excel file (DataTable has headers too) including DataTable's headers

Scenario 2 : Dynamically creating an excel file and put the data into it (I'm searching an answer for this scenario for a long time).

Please guide me through to find a solution Smile | :)

Below is my code which I've implemented in my Script Task :

public void Main()
            DataTable DtUsers = new DataTable();           
            ConnectionManager ConnManager;
            OleDbConnection ConnOledb;
            OleDbDataReader dataReader;
            string SqlString = "MYQUERY";
                Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 ConnParams;
                ConnManager = Dts.Connections["OledbConnectionUnit"];
                ConnParams = (Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100)ConnManager.InnerObject;
                ConnOledb = (OleDbConnection)ConnParams.GetConnectionForSchema();
                OleDbCommand CommOledb = new OleDbCommand();
                OleDbDataAdapter DaUsers = new OleDbDataAdapter();                
                    CommOledb.CommandText = SqlString.ToString();
                    CommOledb.CommandType = CommandType.Text;
                    CommOledb.Connection = ConnOledb;
                    foreach (DataRow DrRows in DtUsers.Rows)
                catch (Exception ex)
                    throw ex;
                Dts.TaskResult = (int)ScriptResults.Success;
            catch(Exception ex)
                throw ex;

Thanks in advance
Posted 26-Dec-12 21:21pm

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1


I solved it myself . I created excel file dynamically Big Grin | :-D I've used VB to do it. I got the solution. I've created headers toooooooooo Big Grin | :-D . Everything is getting created dynamically.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel

<system.addin.addin("scriptmain",> _
<system.clscompliantattribute(false)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()

        Dim cmConnMgr As ConnectionManager
        Dim cmParams As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100
        Dim ConnOledb As OleDb.OleDbConnection
        cmConnMgr = Dts.Connections("OLEDBConn")
        cmParams = CType(cmConnMgr.InnerObject, Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100)
        ConnOledb = CType(cmParams.GetConnectionForSchema(), OleDb.OleDbConnection)


        Dim strSQL As System.Text.StringBuilder = New System.Text.StringBuilder
        Dim drReader As OleDb.OleDbDataReader
        Dim cmdCommand As New OleDb.OleDbCommand
        Dim dtDetails As New System.Data.DataTable
        strSQL.Append("SELECT first_name,email FROM xxxx where user_id <=2000")
            cmdCommand.CommandText = strSQL.ToString
            cmdCommand.CommandType = CommandType.Text
            cmdCommand.Connection = ConnOledb
            drReader = Nothing
            drReader = cmdCommand.ExecuteReader()
            If drReader.HasRows Then
            End If
        Catch ex As Exception
            drReader = Nothing
        End Try

        Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass()
        Dim Format As XlFileFormat = XlFileFormat.xlExcel8
        With excel
            .SheetsInNewWorkbook = 1
            'Create Column Header
            Dim i As Integer = 1
            For col = 0 To dtDetails.Columns.Count() - 1
                .Cells(1, i).value = dtDetails.Columns(col).ColumnName.ToString
                .Cells(1, i).EntireRow.Font.Bold = True
                i += 1
            i = 2

            Dim k As Integer = 1
            For col = 0 To dtDetails.Columns.Count() - 1
                i = 2
                For row = 0 To dtDetails.Rows.Count() - 1
                    .Cells(i, k).Value = dtDetails.Rows(row).Item(col).ToString
                    i += 1
                k += 1
            .ActiveCell.Worksheet.SaveAs("C:\testing.xls", Format)
        End With
        excel = Nothing
        Dts.TaskResult = ScriptResults.Success
    End Sub
End Class

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Advertise | Privacy | Mobile
Web03 | 2.8.150520.1 | Last Updated 28 Dec 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100