Mahipal Padigela's Blog

Manipulating, Programming EXCEL in SSIS(2005) using ScriptTask -II

Following on from my previous post, this is how you’d automate Excel using SSIS. The following sample code inside a ScriptTask extracts a given worksheet from each workwook into another consolidated workbook. The ScriptTask is placed inside a ForEachLoop container that loops through given folder structure for .xls files.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.Office.Interop.Excel
Imports System.IO

Public Class ScriptMain

    Public Sub Main()
        'FolderPath,FullFilePath,CombinedFilePath
        Dim FolderPath As String = Dts.Variables("FolderPath").Value.ToString
        Dim FullFilePath As String = Dts.Variables("FullFilePath").Value.ToString
        Dim CombinedFilePath As String = Dts.Variables("CombinedFilePath").Value.ToString
        Dim fi As FileInfo
        Dim FileCreateddate As String

        If FullFilePath <> CombinedFilePath Then  'exclude combined file that we are building

            Dim app As Microsoft.Office.Interop.Excel.Application
            Dim SourceWb As Workbook
            Dim CombinedWb As Workbook
            Dim ws As Worksheet
            Dim FileName As String
            Dim i As Integer = 1

            fi = New FileInfo(FullFilePath)
            FileCreateddate = CStr(Format(fi.CreationTime, "yyyymmdd"))
            FileName = Replace(fi.Name.Substring(0, fi.Name.Length - 4), " ", "") ' exclude .xls

            If FileCreateddate >= CStr(Format(Today, "yyyymmdd")) Then  ' file satisfies required criteria

                app = New Microsoft.Office.Interop.Excel.Application
                app.DisplayAlerts = False 'suppress excel warnings/alers
                SourceWb = app.Workbooks.Open(FullFilePath, False, True)
                CombinedWb = app.Workbooks.Open(CombinedFilePath, False)

                For Each ws In SourceWb.Worksheets
                    If ws.Name.ToString = "Rates for SB" Then
                        ws.Name = FileName
                        ws.Copy(After:=CombinedWb.Sheets(i))
                        Exit For
                    End If

                Next

                SourceWb.Close(False) ' close source wb without saving
                CombinedWb.Save()
                CombinedWb.Close()
                SourceWb = Nothing
                CombinedWb = Nothing
            End If

            'cleanup
            app.Quit()
            If Not app Is Nothing Then
                Runtime.InteropServices.Marshal.ReleaseComObject(app)
                GC.Collect(0) 'because it's not a .net library, call GC to cleanup
            End If

        End If
        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

Tags: excel, Microsoft.Office.Interop.Excel, SSIS

Manipulating, Programming EXCEL in SSIS(2005) using ScriptTask – I

This can be achieved using Office Primary Interop Assemblies (PIAs).

Office XP PIAs can be downloaded from

http://www.microsoft.com/downloads/details.aspx?familyid=C41BD61E-3060-4F71-A6B4-01FEBA508E52&displaylang=en

STEPS:

• Instal Microsoft.Office.Interop.Excel.dll to GAC (C:\WINDOWS\assembly)
• Copy Microsoft.Office.Interop.Excel.dll to C:\WINDOWS\Microsoft.NET\Framework\v2.0.xxxxx (so SSIS ScriptTask can list it under References dialog)
• Register excel assembly — regedit /s Microsoft.Office.Interop.Excel.dll.reg

now we can add a reference to this library from ssis ScriptTask and start coding using excel objects!!

Tags: EXCEL interop library, Office PIA, SSIS

Connecting to Excel 2007 from SSIS

Create OLE DB Connection using Microsoft Office 12.0 ACE (Access Database Engine) OLEDB provider.

Use the following Extended properties:
Excel 12.0;HDR=YES
HDR –> 1st row is HEADER row (column Headings)





*****************without extended properties, you might get this error**********************
TITLE: Connection Manager
——————————

Test connection failed because of an error in initializing provider. Could not find file ‘C:\temp\Sample.xlsx’.

——————————

Tags: ace, ace oledb, excel 2007, oledb, SSIS