使用SSIS动态创建Excel [英] Dynamically creating an excel using SSIS

问题描述

我有一项任务，SSIS包（作业）应该在一个月内运行四次，并且必须向某些用户触发有关作业状态的邮件，并且必须生成报告。邮件触发部分已经完成，但是我在创建一个动态报告的部分被认为是一个Excel文件。



我的问题很简单我们动态创建Excel文件吗？我浏览了很多链接，但我得到的解决方案不适合我的问题。应该以这样的方式生成Excel文件：NO COLUMNS应该被映射，并且所有内容都必须是动态的，包括列标题，希望许多使用SSIS的人都会记住这个问题。请指导我。





提前致谢：）

I have a task that, a SSIS package (job) should run four times in a month and a mail has to be triggered to certain users about the job status and a report has to be generated. Mail triggering part is done but I''m stuck up in the part of creating a dynamic report which is supposed to be a Excel file.

Precisely my question is how do we create an Excel file dynamically. I surfed through lot of links but the solution I got was not appropriate for my question. Excel file should be generated in such a way that NO COLUMNS should be mapped and everything has to be dynamic including the column headers, hope many people using SSIS will have this question in mind. Please do guide me.


Thanks in advance :)

推荐答案

首先，SSIS适用于预定义的映射。因此任何动态列标题都会产生运行时错误。



我建议创建一个模板并将其存储在某处。然后您的包复制（脚本任务将执行）报告生成位置。断开与复制的新文件的连接。让数据流填充Excel。使用脚本任务发送电子邮件/或使用发送邮件任务。



您可以参考我的文章进行连接设置（动态）等：

使用SSIS加载动态Excel文件 [ ^ ]

Firstly, SSIS works on predefined mapping. So any dynamic column header will produce run time error.

I would suggest create a template and store it somewhere. Then your package copy(script task will do) to report generation location. Se the connection to the new file copied. Let the data flow fill the Excel. Use script task to send the email/or use send mail task.

You may refer my article for connection setting(dynamic) etc :
Dynamic Excel file loading with SSIS[^]

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



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

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

I solved it myself . I created excel file dynamically I''ve used VB to do it. I got the solution. I''ve created headers toooooooooo . Everything is getting created dynamically.

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

这篇关于使用SSIS动态创建Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！