Reputation: 479
The problem:
I have an SSIS package that loops through 100+ Excel files and reads the data, then copies the contents over to a SQL Server Table. In these Excel files, this one column has hyperlinks. The column text itself says something like DSH-LN-4, but clicking on it in Excel opens up a folder that contains some images. How do I copy the underlying link in this column rather than the actual text in the cells?
What have I tried so far:
I haven't really tried anything because I found absolutely no resources on how to do this in SSIS. Manually adding a column to the Excel files is NOT possible, since there are 100's of files. The only resource I found was in this SO Question, but this does not indicate the process of doing this without manually manipulating the Excel files.
What I would like:
In my ForEach loop container, I have a data flow task that gets the Excel contents and shoves it into the SQL Table. The column that contains hyperlinks is called PhotoReference
(since these hyperlinks open the folder that has the photos). I would like this PhotoReference
column to copy over the underlying hyperlink of the cell and add that to the SQL column.
For instance, I want the PhotoReference
column to contain this:
www.companyname.box.com/asjdfbgkjb134kjbsdafo2bm21n4bk
If I can manage to do this, my Power BI report running off of this underlying data could contain a clickable text that would open the image directly.
Any help would be appreciated.
UPDATE:
I was able to try two different methods to extract the hyperlinks from my column, but each of these have their own issues:
Method 1: I added a Script Task component to my ForEach
container and as I loop through each Excel file, used Microsoft.Office.Interop.Excel.Hyperlinks
assembly to get the hyperlink from my Excel column. BUT, I don't know what to do with it after. I figured the only thing to do is to overwrite the Excel columns' content with my extracted hyperlink, but I really rather not change my Excel files in any manner.
Method 2: I added a Script Component object inside my data flow task in between my Excel source and SQL Destination. In this method, I could not get nearly as far because the Input0_ProcessInputRow
method that is auto-generated has the argument Row
of type Input0Buffer
. I am not able to apply any Microsoft.Office.Interop.Excel
properties to my Input0Buffer
object. So I am stuck.
Upvotes: 3
Views: 1414
Reputation: 37313
If you have to right to alter the excel files, you can simply add a Script Task before the data flow task to replace the URL column value with the hyperlink.
In this answer, I will provide a step-by-step solution to solve this problem:
First of all, I created some Excel files with the following columns:
The file content looks like the following:
Now, Open the data flow task and add an Excel source and an OLE DB destination and configure the columns mapping between them.
Open the Script Task configuration, and select the ExcelFilePath variable (created in step 2) as a readonly variable as mentioned in the image below:
On the top of the script you should add the following line:
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
In the Main() function add the following lines:
Excel.Application excel = new Excel.Application();
string originalPath = Dts.Variables["User::ExcelFilePath"].Value.ToString();
Excel.Workbook workbook = excel.Workbooks.Open(originalPath);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
Excel.Range usedRange = worksheet.UsedRange;
int intURLColidx = 0;
excel.Visible = false;
excel.DisplayAlerts = false;
for (int i = 1; i <= usedRange.Columns.Count; i++)
{
if ((worksheet.Cells[1, i] as Excel.Range).Value != null &&
(string)(worksheet.Cells[1, i] as Excel.Range).Value == "Photo")
{
intURLColidx = i;
break;
}
}
for (int i = 2; i <= usedRange.Rows.Count; i++)
{
if ((worksheet.Cells[i, intURLColidx] as Excel.Range).Hyperlinks.Count > 0)
{
(worksheet.Cells[i, intURLColidx] as Excel.Range).Value2 = (worksheet.Cells[i, intURLColidx] as Excel.Range).Hyperlinks.Item[1].Address.ToString();
}
}
workbook.Save();
Marshal.FinalReleaseComObject(worksheet);
workbook.Close(Type.Missing, Type.Missing, Type.Missing);
Marshal.FinalReleaseComObject(workbook);
excel.Quit();
Marshal.FinalReleaseComObject(excel);
Dts.TaskResult = (int)ScriptResults.Success;
In the lines above, first we searched for the column index that contains the hyperlink (in this example the column name is "Photo", then we will check for each line if the Hyperlink address is not empty we will replace the column value with this hyperlink address)
After running the package, if we open an Excel file we will see that the Cell value is replaced with the URL:
And as shown in the image below, data are imported successfully to SQL Server:
Upvotes: 2
Reputation: 1336
What you will probably need to do is some hackery involving the Excel COM API, or macros. In fact, since you should stay away from using the Office COM API in SSIS.
You could pre-process excel to take that value with non-standard operations in SSIS, like using script component.
These are the steps you need to follow to import that data using the Script component:
Drag and drop a script component and select "source" as the script option type.
By default the script language is Microsoft Visual C# 2008 and I have done this sample with Microsoft Visual Basic 2008. Change this if you need to.
Define your output columns with the correct data type in "data type properties"
Edit the script. In the IDE you should add reference:
(if that reference doesn´t work, try with Microsoft.Excel 5.0 Object Library)
Imports Microsoft.Office.Interop.Excel
Public Overrides Sub getHyperlink()
Dim oExcel As Object = CreateObject("Excel.Application")
Dim FileName As String
FileName = Variables.FileName
Dim oBook As Object = oExcel.Workbooks.Open(FileName)
Dim oSheet As Object = oBook.Worksheets(1)
Output0Buffer.AddRow()
// change A1 with your correct col & row
Output0Buffer.Address = cell.range("A1").Hyperlinks(1).Address & "#" & cell.range("A1").Hyperlinks(1).SubAddress
End Sub
(keep in mind that it is a code that may not run, it is by way of illustration)
You could see code in C# here: C# Script in SSIS Script Task to convert Excel Column in "Text" Format to "General"
The only issue with the script method is you need to have the Excel runtime installed.
More about script component here: https://www.tutorialgateway.org/ssis-script-component-as-transformation/
Upvotes: 0