January 10, 2008 at 12:30 pm
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.
January 10, 2008 at 4:20 pm
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.
January 10, 2008 at 4:28 pm
I'm having troubles too. This article is hard to follow and read.
January 10, 2008 at 4:35 pm
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.
January 10, 2008 at 5:00 pm
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.
January 10, 2008 at 5:39 pm
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.
January 10, 2008 at 6:15 pm
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.
January 10, 2008 at 6:55 pm
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
January 31, 2008 at 7:50 am
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
January 31, 2008 at 8:33 am
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....
August 21, 2008 at 10:56 pm
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
August 22, 2008 at 12:10 am
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
August 22, 2008 at 9:20 am
delphigao (8/22/2008)
This link is broken
BI Guy
August 22, 2008 at 9:56 am
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
August 26, 2008 at 6:18 am
Nice article...
Viewing 15 posts - 31 through 45 (of 58 total)
You must be logged in to reply to this topic. Login to reply