How to Dynamically and Iteratively Populate An Excel Workbook from SQL Server

  • So you mean that first I connect to the sample file in the Excel Destination and then change to the variable?

    and when I change to variable it will not ask again for column mappings.

    Thanks a lot for your help.

  • There is something still missing in this article. Can you please provide a step by step how you configure the Excel destination?

    I created an Excel file and connected to it the sample Excel file with column names. The excel destination was created successfully and I ran the package and it created the groups and subgroups successfully but without any records because all the records were filled in the sample excel file Excel.xls which is correct because that is the destination.

    Here are the successful configuration

    Excel connection Manager

    Table or view

    Sheet1$

    After that I modified the Excell Destination to assign the variable SubGroup but it won't allow me and it gives me the following error: A Destination table name has not been provided. The variable subGroup is being assigned in the foreach loop correctly but it won't accept it. I looked a the variable SubGroup and by default it is empty.

    Here are the failure configuration:

    Excel connection Manager

    Table or view from variable

    user::subGroup

    Am I missing anything here?

    You help is appreciated. Thanks in advance.

  • I'm having troubles too. This article is hard to follow and read.

  • one additinal note here of what I did:

    In order to be able to connect to the Excel File (Excel.xls the sample that has column names) I had to delete the expression of the connection manager and run the package and that's when it worked. After that I added the expression Filepath = @[User::filename] and that's when it didn't allow me to assign the subGroup variable.

    In the article at the variable section you are talking about Grouping and subgrouping variables. Are these the User::Group and User::SubGroup variables or there are other ones? I think they are but it is not working.

  • Listing Variables in the article:

    ExcelTab

    ExcelFileName

    SubDataSet

    obj_ListofGrouping

    Grouping

    SubGrouping

    This is a situation of variables within variables. Grouping is used to define the ExcelFileName which is used in the Excel Connection Manager

    Subgrouping is used to create the spreadsheet as:

    "CREATE TABLE" + " " + "`"+@[User::SubGrouping] +"`"+ " " + "(

    `Value1` NVARCHAR(35),

    `Value2` NVARCHAR(40),

    `Value3` NVARCHAR(200),

    `Value4` NVARCHAR(50),

    `Value5` NVARCHAR(15)

    which is the expression within the ExcelTab variable

    SubDataSet is the Select statement used to retrieve the data to populate the spreadsheets.

    For me the simplest was to create the variables. Then add the components and then define each of the components.

  • First, I'd like to thank the author for this article, it's very interesting and useful. I would recommend that in the future the author take larger/clearer screenshots (or link to expanded images) and break down the steps in greater detail. I also recommend the creation of dummy tables at the beginning so that anyone attempting to recreate this package is using the same data and not initially dealing with whatever idiosyncrasies they have in their own dbs. I found it exceedingly difficult to recreate this package and ended up spending a helluva lot of time squinting, muttering, and guessing, and it doesn't sound like I'm the only one.

    Thanks for the article and I look forward to more.

  • Yeah, I totally agree. I think I had hoped everyone could download the article so that way could expand the screenprints to any size they wanted. If you would like any screenprints please feel free to email me directly.

    Thanks again for all the comments which will only help to improve my writing.

  • Yes please, give us a link where we can get the full article, full screen shots and a link to download the working sample package.

    This really is a great article. It deserves a sequel.

    I already get a lot of traction from my management to piggy-back on it. When I spoke about your idea, my boss answered with a barrage of requests for new BI solutions. I have got work for at least 3 days 😀

    One side idea I have is to use existing Excel templates where I have pivot tables sourced from named ranges and auto-refresh on open set to true.

    This is like I like it, simple, smart and cheap.

    I even derived a version for Hyperion out of it :w00t:

    BI Guy

  • What about dynamic datasets? i.e. datasets that changes in columns, let's say for example 2 columns in dataset 1 and 15 columns in dataset 2

  • Good question! I have not tried it with changing numbers of columns/tab or file. Any ideas anyone?

    Hmmm, are we saying that the TABLE itself has multiple columns but not ALL of them have data at any one time? If so, I would try to select ALL the cols. Then either have blank cols on some tabs....or look for a way to suppress the blank/no data cols....

  • I have never found a way to replace data in an existing Excel file. The standard behavior is to append data. The only workaround is to have excel.exe on the server and use Interop. Too bad

    ' Microsoft SQL Server Integration Services Script Task

    ' Write scripts using Microsoft Visual Basic

    ' The ScriptMain class is the entry point of the Script Task.

    Option Strict Off

    Imports System

    Imports System.Data

    Imports System.Math

    Imports Microsoft.SqlServer.Dts.Runtime

    Imports Microsoft.Office.Interop.Excel

    Public Class ScriptMain

    ' The execution engine calls this method when the task executes.

    ' To access the object model, use the Dts object. Connections, variables, events,

    ' and logging features are available as static members of the Dts class.

    ' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

    '

    ' To open Code and Text Editor Help, press F1.

    ' To open Object Browser, press Ctrl+Alt+J.

    Private _app As Microsoft.Office.Interop.Excel.Application

    Private _books As Microsoft.Office.Interop.Excel.Workbooks

    Private _book As Microsoft.Office.Interop.Excel.Workbook

    Protected _sheets As Microsoft.Office.Interop.Excel.Sheets

    Protected _sheet As Microsoft.Office.Interop.Excel.Worksheet

    Public Sub Main()

    ' Add your code here

    Try

    Dim FileName As String

    Dim Tab As String

    FileName = CType(Dts.Variables("DestinationFile").Value, String)

    Tab = CType(Dts.Variables("AccessTB").Value, String)

    OpenExcelWorkbook(FileName)

    _sheet = CType(_sheets(Tab), Microsoft.Office.Interop.Excel.Worksheet)

    _sheet.Select(Type.Missing)

    Dim range As Microsoft.Office.Interop.Excel.Range = _sheet.Rows("2:65536")

    range.Delete(Microsoft.Office.Interop.Excel.XlDeleteShiftDirection.xlShiftUp)

    NAR(range)

    NAR(_sheet)

    CloseExcelWorkbook()

    NAR(_book)

    _app.Quit()

    NAR(_app)

    Dts.TaskResult = Dts.Results.Success

    Catch ex As Exception

    MsgBox(ex.ToString())

    End Try

    End Sub

    Protected Sub OpenExcelWorkbook(ByVal fileName As String)

    'try

    '{

    _app = New Microsoft.Office.Interop.Excel.Application()

    If _book Is Nothing Then

    _books = _app.Workbooks

    _book = _books.Open(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing)

    _sheets = _book.Worksheets

    End If

    '}

    'catch(Exception ex)

    '{

    ' Console.WriteLine(ex.ToString());

    '}

    End Sub

    Protected Sub CloseExcelWorkbook()

    _book.Save()

    _book.Close(False, Type.Missing, Type.Missing)

    End Sub

    Protected Sub NAR(ByVal o As Object)

    Try

    If Not o Is Nothing Then

    System.Runtime.InteropServices.Marshal.ReleaseComObject(o)

    End If

    Finally

    o = Nothing

    End Try

    End Sub

    ' To avoid the variable locking problem use this method to read package variables.

    ' No read variables need to be declared in the "ReadOnlyVariables" list of the script

    ' component if this method is used inside the code.

    Private Function ReadVariable(ByVal varName As String) As Object

    Dim result As Object

    Try

    Dim vars As Variables

    Dts.VariableDispenser.LockForRead(varName)

    Dts.VariableDispenser.GetVariables(vars)

    Try

    result = vars(varName).Value

    Catch ex As Exception

    Throw ex

    Finally

    vars.Unlock()

    End Try

    Catch ex As Exception

    Throw ex

    End Try

    Return result

    End Function

    ' To avoid the variable locking problem use this method to set package variables.

    ' No write variables need to be declared in the "ReadWriteVariables" list of the script

    ' component if this method is used inside the code.

    Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)

    Try

    Dim vars As Variables

    Dts.VariableDispenser.LockForWrite(varName)

    Dts.VariableDispenser.GetVariables(vars)

    Try

    vars(varName).Value = varValue

    Catch ex As Exception

    Throw ex

    Finally

    vars.Unlock()

    End Try

    Catch ex As Exception

    Throw ex

    End Try

    End Sub

    End Class

    BI Guy

  • How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET --- http://support.microsoft.com/kb/316934/en-us%5B/url%5D

  • delphigao (8/22/2008)


    How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET --- http://support.microsoft.com/kb/316934/en-us%5B/url%5D%5B/quote%5D

    This link is broken

    BI Guy

  • What am I missing here? I am using VS 2008 and nowhere can I find a way to

    "Add Execute SQL Task to the canvas .....".

    What version of VS are you using. I can't even find how this is done by searching MSDN.

    They describe how to configure it but do not describe how to create it in the first place

    which is typical of MS documentation.

    thanks, Otto Porter

  • Nice article...

Viewing 15 posts - 31 through 45 (of 59 total)

You must be logged in to reply to this topic. Login to reply