Click here to Skip to main content
Rate this: bad
good
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";
            try
            {
                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();                
                try
                {
                    CommOledb.CommandText = SqlString.ToString();
                    CommOledb.CommandType = CommandType.Text;
                    CommOledb.Connection = ConnOledb;
                    dataReader=CommOledb.ExecuteReader();
                    DtUsers.Load(dataReader);
                    foreach (DataRow DrRows in DtUsers.Rows)
                    {
                        DrRows["Column_Name"].ToString();
                    }              
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }
        
        
    }
 

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

1 solution

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

Solution 1

Hurrrrraaaaaaayyyyyyyyyy!!!!!!!!!!!!
 
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")
 
        Try
            cmdCommand.CommandText = strSQL.ToString
            cmdCommand.CommandType = CommandType.Text
            cmdCommand.Connection = ConnOledb
 
            drReader = Nothing
            drReader = cmdCommand.ExecuteReader()
            If drReader.HasRows Then
                dtDetails.Load(drReader)
            End If
            drReader.Close()
 
        Catch ex As Exception
            drReader = Nothing
        End Try
 
        MsgBox(dtDetails.Columns.Count)
        MsgBox(dtDetails.Rows.Count)
 

 
        Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass()
        Dim Format As XlFileFormat = XlFileFormat.xlExcel8
 
        With excel
            .SheetsInNewWorkbook = 1
            .Workbooks.Add()
            .Worksheets(1).Select()
 
            '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
            Next
            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
                Next
 
                k += 1
 
            Next
 
            .ActiveCell.Worksheet.SaveAs("C:\testing.xls", Format)
            .Workbooks.Close()
 
        End With
 
        excel.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel)
        excel = Nothing
 
        Dts.TaskResult = ScriptResults.Success
    End Sub
 
End Class
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 398
1 CPallini 333
2 George Jonsson 270
3 OriginalGriff 159
4 Prasad Khandekar 135
0 OriginalGriff 4,613
1 CPallini 3,340
2 Sergey Alexandrovich Kryukov 2,771
3 George Jonsson 2,254
4 Gihan Liyanage 2,020


Advertise | Privacy | Mobile
Web03 | 2.8.140905.1 | Last Updated 28 Dec 2012
Copyright © CodeProject, 1999-2014
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