Adding data to an excel file using C# , SSIS

Question

4.00/5 (1 vote)

See more:

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 :)

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

SadiqMohammed

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

SadiqMohammed · Accepted Answer · 2012-12-28T01:57:00

Hurrrrraaaaaaayyyyyyyyyy!!!!!!!!!!!!

I solved it myself . I created excel file dynamically :D I've used VB to do it. I got the solution. I've created headers toooooooooo :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