Updating a single Excel cell using SSIS

bsingha asked a question in the forums regarding how to update a single cell in one Excel sheet using the data from another sheet.

So, I decided to post a quick blog post on how to accomplish this.

First - make sure that SQL is installed and service packed up to date.  Make sure Excel is installed on the machine which will be used ( in my case, I used Excel 2007 because I already had it installed ).  Excel ( or office ) will also need to be service packed up to date.

Once that's all done do the following steps:

• Create a new SSIS package
• Drop a script component into the control flow
• Edit the script component
• Once inside the Visual Studio ( vb.net ) IDE, right click on the project ( mine has a random string of alpha-numerics as the project name ) and select properties.
• On the window that opens, select "References"
• Click the "Add..." button and the following window should appear.

•  Locate and add the "Microsoft.Office.Interop.Excel" .Net namespace to the project.
• Click "OK"
• Now close the properties window to return to the ScriptMain.vb code window.
• If you add the Imports statement now, Intellisense should pickup each portion as shown below..

Next, I created (2) Excel files.  I named one "Source.xls" and one "Destination.xls" and I set them up as seen below.  Note that source has a value in B1 and destination's B1 cell is empty.

Next, I created a routine in VB.Net to open both sheets.  Source is opened read-only and destination is opened in read-write mode.  To save you some typing - here is the source that I used in this example.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Excel = Microsoft.Office.Interop.Excel
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()
        Dim Excel1 As New Excel.Application
        Dim Excel2 As New Excel.Application
        Dim FileName1 As String = "C:\Users\Keith\Desktop\Excel_SSIS\Source.xls"
        Dim FileName2 As String = "C:\Users\Keith\Desktop\Excel_SSIS\Destination.xls"

        Excel1.Visible = False
        Excel2.Visible = False

        Dim MyIntPtr1 As IntPtr = Excel1.Hwnd
        Dim MyIntPtr2 As IntPtr = Excel2.Hwnd

        Excel1.DisplayAlerts = False 'we don't want to see dialogs popping up..
        Excel2.DisplayAlerts = False 'from either Excel instance in memory..
        
        Try
            ' In the following code, all of the "1"s represent source workbook.
            ' ..and the "2"s represent the destination workbook.
            Dim Workbook1 As Excel.Workbook
            Dim Workbook2 As Excel.Workbook

            Workbook1 = Excel1.Workbooks.Open(FileName1, , True) 'True = Read-only
            Workbook2 = Excel2.Workbooks.Open(FileName2, , False) 'False = read-write.

            Dim Worksheet1 As Excel.Worksheet = Nothing
            Dim Worksheet2 As Excel.Worksheet = Nothing

            Worksheet1 = Workbook1.Sheets(1)
            Worksheet2 = Workbook2.Sheets(1)

            ' Assign the "source" range value 
            Worksheet2.Cells(1, 2) = Worksheet1.Cells(1, 2) ' DST( "1B" ) = SRC( "1B" )
            ' into the dest range value.

            Workbook2.SaveAs(FileName2, , , , , Excel.XlSaveAsAccessMode.xlExclusive) 'Save the dest changes.
            Excel2.DisplayAlerts = True

            'Memory clean-up.  Not sure it's needed, but...why not.
            FileName1 = Nothing
            FileName2 = Nothing

            Worksheet1 = Nothing
            Worksheet2 = Nothing

            Workbook1 = Nothing
            Workbook2 = Nothing

            Excel1.DisplayAlerts = True
            Excel2.DisplayAlerts = True '..just in case.

            Excel1.Quit()
            Excel2.Quit()

            Excel1.Visible = True
            Excel2.Visible = True

            For Each proc As System.Diagnostics.Process In System.Diagnostics.Process.GetProcessesByName("Excel")
                If proc.MainWindowHandle = MyIntPtr1 Then
                    proc.Kill()
                End If
                If proc.MainWindowHandle = MyIntPtr2 Then
                    proc.Kill()
                End If
            Next

            Excel1 = Nothing
            Excel2 = Nothing

            Dts.TaskResult = ScriptResults.Success
        Catch ex As Exception

            Excel1.DisplayAlerts = True
            Excel2.DisplayAlerts = True '..just in case.
            Excel1.Quit()
            Excel2.Quit()
            For Each proc As System.Diagnostics.Process In System.Diagnostics.Process.GetProcessesByName("Excel")
                If proc.MainWindowHandle = MyIntPtr1 Then
                    proc.Kill()
                End If
                If proc.MainWindowHandle = MyIntPtr2 Then
                    proc.Kill()
                End If
            Next
            Excel1 = Nothing
            Excel2 = Nothing
            FileName1 = Nothing
            FileName2 = Nothing

            Dts.TaskResult = ScriptResults.Failure
        End Try
        
    End Sub

End Class

I built this code to ensure no errors, then saved it.  Then closed the Script editor windows clicking "OK" ( NOT Cancel ).

Finally, I saved the package and executed it.

The package ran the script - and as you can see in the screenshot below, the Destination file was updated and backed up.

I hope that saves you a little bit of headache in your Excel programming!

Keith Hyer