Script Task to Format Column Headings to an Excel Spreadsheet

• GBeezy

  SSCarpal Tunnel

  Points: 4723

  More actions

  July 25, 2012 at 8:12 am

  #278483

  I am needing to write a Script task to Format (Bold them and Set the column width) Column headings.

  I currently have SQL Task that has an Excel Connection and a CREATE TABLE statement. This will create the spreadsheet/worksheet, with the column headings. Then I have DFT that reads the data and gets into the spreadsheet.

  At some point I need to Bold the Column Headings and set the width so that the end user will not have to pull each cell to see the full column heading.

  I've googled this and it seems it can be done via script task and people have good examples, but for someone who can barely spell C, it is like staring at a foreign language.

  Any and all help will be greatly appreciated!!!

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  July 27, 2012 at 11:54 am

  #1518676

  If you're not versed in programming in C# (SSIS 2008+) or VB.NET against the Office Object Model, or at all, then I would recommend you create an empty Excel Workbook (i.e. a template) formatted as you like and deploy it with your SSIS package. Then, instead of issuing a CREATE TABLE statement in the Execute SQL Task use a File System Task to copy the 'template' into a new work folder and export your data into the copy.

  I have used this technique to gain formatting and it works well. This makes your template part of your codebase so you'll want to version it and refer to it by a path stored in a Package Configuration or a variable passed into DTExec.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• GBeezy

  SSCarpal Tunnel

  Points: 4723

  More actions

  July 30, 2012 at 7:11 am

  #1519129

  Thanks for the reply OPC...

  I did try that method as I researched out to get this done. My problem that I had with it, is that the File System Task could not see/use my Excel Connection Manager that I used for the template file. It seems as though the File System Task is only for flat files.

  Could you explain in more detail how you got that to work?

  Thanks Again...

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  July 30, 2012 at 9:22 am

  #1519233

  GBeezy (7/30/2012)

  ---

  Thanks for the reply OPC...

  I did try that method as I researched out to get this done. My problem that I had with it, is that the File System Task could not see/use my Excel Connection Manager that I used for the template file. It seems as though the File System Task is only for flat files.

  Could you explain in more detail how you got that to work?

  Thanks Again...

  What I typically do is create two variables, one to hold the path+filename to my 'Excel template' named ExcelTemplate and one to hold the path+filename to my 'Excel workfile' named ExcelWorkFile. Those two are themselves built via an Expression from other variables, but I won't go too deep into how I get there.

  The first task in my package is a File System Task (FST) that copies my 'template' file to my 'workfile' location using the variables values mentioned above:

  

  Optionally you may need a second FST to change the ReadOnly flag on the file. I version my templates so when they are deployed out of my source control system (TFS) they have their read-only flag turned on which prevents any Data Flow Tasks from writing to them. A second FST after the file copy with an Operation of SetAttributes to set the ReadOnly flag to False is required in my environment configuration.

  Then, in my Data Flow Task I use an Excel Connection to connect to the workfile. In the Excel Connection I use an Expression to set the ConnectionString property again using my variable, like this:

  "Data Source=" + @[User::ExcelWorkFile] + ";Provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 12.0;"

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato