Script Task to Format Column Headings to an Excel Spreadsheet

  • 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!!!

  • 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

  • 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...

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply