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



我自己解决了。我动态创建了excel文件：D我已经用VB来做了。我得到了解决方案。我创建了标题太oooooooo：D。一切都是动态创建的。

进口系统
进口System.Data 
进口System.Math 
导入Microsoft.SqlServer.Dts.Runtime 
导入Microsoft.Office.Interop.Excel 


< system.addin.addin（scriptmain，> _ 
< system.clscompliantattribute（false）> _ 
部分公共类ScriptMain 
继承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）
 cmP arams = 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（）
如果drReader.HasRows那么
 dtDetails.Load（drReader）
结束如果
 drReader。关闭（）

 Catch ex As Exception 
 drReader = Nothing 
结束尝试

 MsgBox（dtDetails.Columns.Count）
 MsgBox（ dtDetails.Rows.Count）



 Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass（）
 Dim Format As XlFileFormat = XlFileFormat.xlExcel8 

使用excel 
 .SheetsInNewWorkbook = 1 
 .Workbooks.Add（）
 .Worksheets（1）.Select（）

''创建列标题
 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 
 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）
 .Workbooks.Close（）

 End with 

 excel.Quit（）
 System.Runtime.InteropServices。 Marshal.ReleaseComObject（excel）
 excel = Nothing 

 Dts.TaskResult = ScriptResults.Success 
 End Sub 

 End Class 

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

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