Custom Excel Reports in SSIS with C#

<edit 2014-05-22>

you must add the folder

C:\Windows\SysWOW64\config\systemprofile\Desktop (C:\Windows\System32\config\systemprofile\Desktop if it is 32bit)

or running this job will fail under SQL Server Agent login

scheduled as a SQL Server Agent job.

</edit>

If you need more customized excel reports (What exec or salesperson these days are happy with PDF reports anymore), especially custom templates with multiple worksheets (different report per worksheet), you can do this with a script component in SSIS fairly easily.

I'm not going to cover making an SSIS project, data flow task, or ADO NET (SQL statement) source as those are pretty easy and there are plenty of guides on the net already.

This is dealing specifically with the "script component" as Destination from the SSIS Toolbox as:










You will need to select the ADO NET columns you need for the new script component as usual:

For this simple example I am only selecting 4 columns from the database.
As an addition step (not required), I added a "filename" user variable to the project:



What good is an excel report if you can't email it to someone?
In this example we are creating a new xlsx file from scratch (not from an existing formatted template) and naming it with the date and time in the filename for historical purposes (archiving distributed reports is one of our business requirements)

Add the "filename" variable to the script component on the first section under readwrite variables:




I use C# as the scripting language because I prefer C# to VB, but you could translate the script to VB if you like.

Now click on Project->Add Reference:

You will need to add two references:

COM section

Microsoft Excel 11.0 Object Library (or version available)

Assemblies section

Microsoft.CSharp

Then you can copy and past the script below and edit it.
NOTE: You will HAVE to edit the script. Parts of it are generic sure, but you must set the column
names in the variable declaration, the starting line number, the number of columns, etc. to exactly match
what input columns you are passing into the script component.
Also, I am saving the excel files to my C:\SSIS folder in my VM. You will need to change that to suit the machine you are running it from.
I'm only using one worksheet. If you are outputting multiple worksheets you will have to add the logic for that yourself.

If you have any questions drop me a line.


------------------------------------------------------------

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.Office.Interop.Excel;

#endregion

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    Microsoft.Office.Interop.Excel.Application    ivar_Excel                = new Microsoft.Office.Interop.Excel.Application();
    Microsoft.Office.Interop.Excel.Workbook        ivar_Workbook;
    Microsoft.Office.Interop.Excel.Worksheet        ivar_Worksheet;
    Microsoft.Office.Interop.Excel.Range            ivar_Range;

    //                    il_Row should be far enough down to skip your Header section
    long                il_StartDataRow        =                10;
    long                il_Row                    =                10;
    long                il_Rowcount                =                0;

    // YOU MUST FILL THIS PART IN!
    String[]            iS_ColumnNames            =                {"Employee ID","Name","Title","Employee Number"};
    String[]            iS_WorksheetNames        =                {"Employee Data"};


    public override void PreExecute()
    {
            base.PreExecute();
       
            // CREATE a new excel file to output to and saveas
            ivar_Excel.Visible                =    false;
          
            ivar_Workbook                        =    ivar_Excel.Workbooks.Add();
            ivar_Worksheet                        =    ivar_Workbook.Sheets[1];

            // set the name of the worksheet
            ivar_Worksheet.Name                =        iS_WorksheetNames[0];
            ivar_Worksheet.Cells[1,1]        =        "Date:";
            ivar_Worksheet.Cells[1,2]        =        DateTime.Now.ToString("u").Replace( "Z","" );

            // set the 1st row Header Text
            for(    long  ll_Index=1;
                    ll_Index <= iS_ColumnNames.Length;
                    ll_Index++ )
            {
                    ivar_Worksheet.Cells[il_StartDataRow-1,ll_Index]        =        iS_ColumnNames[ ll_Index -1];
            };

            //change column widths
            for(    long ll_Index=1;
                    ll_Index <= iS_ColumnNames.Length;
                    ll_Index++ )
            {
                    ivar_Range    =    ivar_Worksheet.Range[    ivar_Worksheet.Cells[ il_StartDataRow-1,ll_Index ],
                                                                        ivar_Worksheet.Cells[ il_StartDataRow-1,ll_Index ] ];
                    ivar_Range.EntireColumn.ColumnWidth =    50;
            };

            // title row color and bold
            ivar_Range    =    ivar_Worksheet.Range[    ivar_Worksheet.Cells[ il_StartDataRow-1, 1],
                                                                ivar_Worksheet.Cells[ il_StartDataRow-1, iS_ColumnNames.Length]  ];
            ivar_Range.Font.Color                                    =    XlRgbColor.rgbBlack;
            ivar_Range.Interior.Color                                =    XlRgbColor.rgbYellow;
            ivar_Range.Font.Bold                                        =    true;
            ivar_Range.EntireRow.RowHeight                        =    20;


    }

    public override void PostExecute()
    {
            string    ls_filename;


            base.PostExecute();

            //change column colors
            for(    long  ll_Index=1;
                    ll_Index <= iS_ColumnNames.Length;
                    ll_Index++ )
            {
                    ivar_Range    =    ivar_Worksheet.Range[    ivar_Worksheet.Cells[ il_StartDataRow,ll_Index ],
                                                                        ivar_Worksheet.Cells[ il_Row,ll_Index ] ];
                    ivar_Range.ColumnWidth =    35;
                    ivar_Range.Font.Color                    =    XlRgbColor.rgbBlack;
                    ivar_Range.Interior.Color                =    XlRgbColor.rgbWhite;
            };
          

            // set font bold and yellow for summary row
            ivar_Range                                                =    ivar_Worksheet.Range[    ivar_Worksheet.Cells[ il_Row, 1 ],
                                                                                                            ivar_Worksheet.Cells[ il_Row, iS_ColumnNames.Length ] ];
            ivar_Range.Font.Color                                =    XlRgbColor.rgbBlack;
            ivar_Range.Interior.Color                            =    XlRgbColor.rgbYellow;
            ivar_Range.Font.Bold                                    =    true;
            ivar_Range.EntireRow.RowHeight                    =    20;

            // rowcount
            ivar_Worksheet.Cells[ il_Row, 1 ]                =    "Rows: ";
            ivar_Worksheet.Cells[ il_Row, 2 ].Formula        =    "=COUNTA(B2:B" + il_Rowcount.ToString() + ")";

            // build save filename with date in it      
            ls_filename    =        DateTime.Now.ToString("u") + ".xlsx";
            ls_filename    =        ls_filename.Replace( ":","-" );
            ls_filename    =        ls_filename.Replace( "Z","" );

            ls_filename =        "\\\\brucemdev\\C$\\SSIS\\" + ls_filename;

            //System.Windows.Forms.MessageBox.Show(ls_filename);
            ivar_Workbook.SaveAs( ls_filename );
            ivar_Workbook.Close();
            ivar_Excel.Visible = false;

            Variables.filename        =        ls_filename;



    }

    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
            long        ll_columncount;

            // THIS SECTION IS NOT DYNAMIC YOU MUST CODE IT FOR EVERY COLUMN
            ivar_Worksheet.Cells[il_Row,1]        =   Row.employeeid.ToString();
            ivar_Worksheet.Cells[il_Row,2]        =   Row.name.ToString();
            ivar_Worksheet.Cells[il_Row,3]        =   Row.title.ToString();
            ivar_Worksheet.Cells[il_Row,4]        =   Row.number.ToString();
          

          


            il_Row++;
            il_Rowcount++;


    }

}


You will end up with an excel output that looks like:

Pretty crude as far as a report you would send to an end user, I know, but you can add additional formatting in the script as desired. ( title, subtitle, description, arguments, etc)