Generating large numbers of SSAS partitions using Excel

Link to Chris Webb’s BI Blog page:


Tags: , ,

Friday, March 2nd, 2012 ssas No Comments

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()
        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
                        Exit For
                    End If


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

            If Not app Is Nothing Then
                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: , ,

Tuesday, December 9th, 2008 SSIS No Comments