Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL SQL-Server 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 Smile | :)
Posted 14-Dec-12 2:35am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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
  Permalink  

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

  Print Answers RSS
0 mhegazy94 460
1 Sergey Alexandrovich Kryukov 450
2 Kornfeld Eliyahu Peter 305
3 Ravi Bhavnani 200
4 Shemeemsha RA 160
0 Sergey Alexandrovich Kryukov 6,890
1 OriginalGriff 6,761
2 CPallini 5,350
3 George Jonsson 3,609
4 Gihan Liyanage 2,797


Advertise | Privacy | Mobile
Web01 | 2.8.140922.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