Friday, December 2, 2011

SSIS: How to generate HyperLink in Excel output

This post shows how you can generate hyperlink in excel output file using Script Task

Reading data and exporting it to Excel is one of the very common task in SSIS professional life. One of the very intersting thing which I come across is generating Hyperlink in Excel output files.

Let us assume we have following data in a SQL table. The table name is tblCompany inside the database [HumanResource] in my machine.

We need to export this data to an Excel file and want to generate the Hyperlink in excel file using SSIS. I want to generate the Hyperlink in Website and EmailID column. So when the file reaches to the end user they can navigate to the websites of companies listed in the excel file.

I started with creating three variables in the SSIS package.

ExcelFilePath
This variable is to store the excel file path. Currently this file has only columns heading (Company, Website and EmailID).

varColumnCount
This variable is to store the number of columns it has to read from the source i.e. from the SQL table mentioned above. Currently the SQL Table has only three columns.

varRowCount
This variable is to store the number of rows it has to read from the source i.e. from the SQL table mentioned above. Currently the SQL Table has only six rows.

The Control flow and Data Flow of my SSIS package looks like this

Execute SQL Task
This control flow task does one simple thing. It reads the number of columns we have in the SQL table. I have used the following SQL Statement.

select COUNT(*) as 'Columns' From sys.columns where object_id=object_id('[HumanResource].[dbo].[tblCompany]')

This task returns the result set in variable varColumnCount

Data Flow Task
The Data Flow task simply read all the data from SQL Table mentioned in picture 1 and store it in excel file. In between it assign the Row count to the variable varRowCount

Script Task
The script task is one which plays the major role in the assigning Hyperlink in the Excel output file.

If you have experienced the formulas in Excel you might be aware of a formula called HYPERLINK. This excel formula takes two arguments; Link_Location and Friendly_name. So if you simply open an excel workbook and write following formula it will show the Hyperlink.

=HYPERLINK("http://www.google.com","Go to Google Homepage")

Our Script task will be using the same Excel Formula to generate the Hyperlink. It will loop through all the cells in excel and if it finds the http or @ keyword it will create a hyperlink.

All the three variables are set as ReadOnlyVariables in the Script task.

Ater that we need to click on Edit Script button to launch the Code window. We need to add following two references in our Script task

The code snippet I have used in Script Task is below

After setting up all the components I run the SSIS package. The SSIS package runs successfully and generated the Hyperlink in the excel file.

Output Excel File has hyperlinks now.

This approach has some advantages and some disadvantages. One of the disadvantage is that it is using COM based Excel object libraries which needs to be present if you plan to use this approach. This is just one of the ways to generate Hyperlink. This can be done in SSIS using other mechanism.

I hope you enjoy the post.

Environment: Ms-Office 2007 and SQL Server 2008.

If you want to explore more with the Source code of this post; please visit Download Zone.

Related Article:
Labels: , ,

Popular Posts

Real Time Web Analytics