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)
