InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 20 Jun 2013 02:47:27 GMT20http://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxHello to all Gurus,I am a newbie and need a step by step help. I am attaching herewith sample .dtsx and .bak ( both zipped separately) from my environment.The scenario i was trying is given below :Tables in focus dbo.CustTable & dbo.CustGroupboth tables are linked with the field CustGroup.There are different working company for an application instance which is defined as "dataareaId" in each table.I would try to create one file dynamically for each distinct dataareaid in CustTable and within a workbook create worksheets for a distinct CustGroup and each worksheet will then have the CustTable data for that CustGroup.I have tried to follow the article but i am stuck at the excel destination . It says "the excel file name is not provided".Help meThere would be a need to change the connection to use the attached the database instead of the ince in the .dtsx file.I have now attached the updated files for your review. Pls helpTue, 27 Oct 2009 03:51:53 GMTshekhardiptimanhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxHi,Pls help me with more details on the @User::SubsidiaryName as there is nothing mentioned in articleSun, 25 Oct 2009 23:16:54 GMTshekhardiptimanhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxHi,Pls help me with more details on the @User::SubsidiaryName as there is nothing mentioned in articleSun, 25 Oct 2009 23:12:55 GMTshekhardiptimanhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxnice ressurection of one of my more favorite bookmarked discussions. :)Fri, 16 Oct 2009 11:20:24 GMTquayludioushttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxNice article. Does this work with the 64 bit Excel driver?Fri, 16 Oct 2009 10:50:09 GMTmacrochaotichttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxGood, well-written article and having worked with SSIS, not too hard to understand and follow. I suppose if you had to generate this kind of Excel output on a regular basis, this code would be most helpful.However, it also highlights the often painful complexity of SSIS to the uninitiated and one must consider whether simply running a bunch of queries then copying and pasting the results - or - simply running the queries from the excel side are not more overall time-efficient, less tedious and complex approaches. Still, excellent and creative work, nicely documented.Fri, 16 Oct 2009 10:35:14 GMTblandryhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxExcellent and informative article. I, though using SSIS for last couple of years but still love the simple DTS designer tool of SQL Server 2000 version than the complicated SSIS of 2005/2008. I agree, with SSIS we can accomplish more than what one can with DTS. But still, DTS is DTS.Thanks for the article.Fri, 16 Oct 2009 09:39:58 GMTSanjayAttrayhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxGlad it was useful for you!Thu, 12 Mar 2009 17:27:00 GMTmdeschen1http://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxTerrific article. I used it with some modifications to realize a huge time-saving project.Many thanks!- Simon DThu, 12 Mar 2009 14:55:22 GMTSimon E Doubthttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxAnyone having problems reading the graphics please note your contact address/email here and I will send you a copy of the article. (if you don't mind)At this time I am not actually working with SQL Server, rather with Oracle tools so don't have them loaded right now. Sorry about that!Wed, 10 Sep 2008 18:15:45 GMTmdeschen1http://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxWhat is the actual Expression used in the Excel Connection Manager? The screen shots in your original article are too blury to read, and your followup post simply left it out:"Set the Excel Connection (notice that usually you specific connection 1, connection 2 etc but here NO specified number) to use a New Property expression. In the New Property expression and add the variable defined for the Excelsheet name. This variable references back to the cursor (called in this case 'Region' . . ."Could you simply write the expression, please?Really appreciate it.Wed, 10 Sep 2008 11:44:58 GMTCJ-414083http://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxWhat is the actual Expression used in the Excel Connection Manager? The screen shots in your original article are too blury to read, and your followup post simply left it out:"Set the Excel Connection (notice that usually you specific connection 1, connection 2 etc but here NO specified number) to use a New Property expression. In the New Property expression and add the variable defined for the Excelsheet name. This variable references back to the cursor (called in this case 'Region' . . ."Could you simply write the expression, please?Really appreciate it.Wed, 10 Sep 2008 11:34:59 GMTCJ-414083http://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxIt would be an excellent article if it was better written. Poorly written which takes a while to read through and understand which is unfortunate as there are some very good and informative points raisedFri, 29 Aug 2008 03:10:25 GMTCowboy DBAhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxVery nice article. I am sure I will be using the techniques introduced here in the near future. Some minor complaints: the screenshots at the end (dealing with variables) seem a bit off (specifically the one showing ExcelTab variable, which in fact belongs to ExcelFileName), also the package provided for download is a little different (variable names) from the article, but like I said, this is not a big deal.Thank you!Wed, 27 Aug 2008 11:55:36 GMTmishalubahttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxNice article...Tue, 26 Aug 2008 06:18:04 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxWhat 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 placewhich is typical of MS documentation.thanks, Otto PorterFri, 22 Aug 2008 09:56:08 GMTOtto Porterhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspx[quote][b]delphigao (8/22/2008)[/b][hr][url=http://support.microsoft.com/kb/316934/en-us]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[/url][/quote]This link is brokenFri, 22 Aug 2008 09:20:30 GMTPhilippe Candhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspx[url=http://support.microsoft.com/kb/316934/en-us]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[/url]Fri, 22 Aug 2008 00:10:30 GMTdelphigaohttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxI 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[code] ' 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 OffImports SystemImports System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports Microsoft.Office.Interop.ExcelPublic 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 SubEnd Class[/code]Thu, 21 Aug 2008 22:56:25 GMTPhilippe Candhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxGood 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....Thu, 31 Jan 2008 08:33:35 GMTmdeschen1http://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxWhat 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 2Thu, 31 Jan 2008 07:50:43 GMTpieter.olivierhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxYes 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 :DOne 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:Thu, 10 Jan 2008 18:55:08 GMTPhilippe Candhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxYeah, 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.Thu, 10 Jan 2008 18:15:20 GMTmdeschen1http://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxFirst, 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.Thu, 10 Jan 2008 17:39:05 GMTomnicrondelicioushttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxListing Variables in the article:ExcelTabExcelFileName SubDataSetobj_ListofGroupingGroupingSubGroupingThis is a situation of variables within variables. Grouping is used to define the ExcelFileName which is used in the Excel Connection ManagerSubgrouping 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 variableSubDataSet 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.Thu, 10 Jan 2008 17:00:48 GMTmdeschen1http://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxone 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.Thu, 10 Jan 2008 16:35:50 GMTimaddaouhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxI'm having troubles too. This article is hard to follow and read.Thu, 10 Jan 2008 16:28:16 GMTTony Mungorhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxThere 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 configurationExcel connection ManagerTable or viewSheet1$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 ManagerTable or view from variableuser::subGroupAm I missing anything here?You help is appreciated. Thanks in advance.Thu, 10 Jan 2008 16:20:52 GMTimaddaouhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxSo 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.Thu, 10 Jan 2008 12:30:20 GMTimaddaouhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxWhen creating the solution did you have an excel workbook with at least one spreadsheet in it containing the appropriate headings?I believe we all ran into the same thing and that seemed to resolve the issue of not being able to add the variable during the Data Flow Task. ONCE able to add the variable then the 'sample' workbook is deleted.As an aside...really want to thank people for all the comments...made me go back and DOUBLE check that the solution runs...and in fact it is running right now for two Groups with over 12 spreadsheets in each etc etc..but I can see how the document could be much tighter for next time. :-) (and unfortunately longer)Thu, 10 Jan 2008 12:23:30 GMTmdeschen1http://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxok, you are right with all what you mentioned but the problem lies by assigning the Excel Destination in the DataFlow Task. When I assign the variablename it gives me the error map columns.What is the problem with this step? and how it should be configured?Thanks a lot for the explanations.Thu, 10 Jan 2008 12:08:07 GMTimaddaouhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxSorry, wrong button!To continue:By setting the pre-defined variable 'Listof Groups' equal to INDEX 0 you then have all the values you populated in the 'List of Countries' object from the select statement for Region.In other words....whatever is in the initial select statement is populated in order (0, 1, 2, 3) into a BIG cursor/bucket. You then reference each set of data, list of data, within the Foreach Loop in the Variable Mappings section of the Foreach Loop Editor by attaching a variable to an INDEX number which is the populated order.ok,other variables.ExcelTab: Is populated using a standard CREATE table that is used when pushing from sql server to excel such as:"CREATE TABLE" + " " + "`"+@[User::ListofSubgroups] +"`"+ " " + "( `Region` NVARCHAR(35), `Subsidiary` NVARCHAR(40), `Value1` NVARCHAR(200), `Value2` NVARCHAR(50), `Value3` NVARCHAR(15))"This creates the tab/spreadsheet with the proper subgroup name and adds the titles across the columns.SubDataSet - Is used in the OLE DB Source component of the Data Flow Task. Placed in the 'Variable name' box with syntax: User::SubDataSet. Populate it with a select from your database. Make sure the columns match the column headings in your Create table statement above when creating the tab/spreadsheet otherwise this will fail at this point.Hope this helps a little?Thanks for everyone's comments....definitely help to hone the article and help each other.Thu, 10 Jan 2008 11:27:34 GMTmdeschen1http://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxHi,What you mentioned is already clear. The place where we have problems in is the execute SQL Task in the Foreach loop and the Excel Destination in the DataFlow Task.Thanks for your remarksThu, 10 Jan 2008 11:26:32 GMTimaddaouhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxSomeone asked for all the variables so we go:ExcelTab - which populates the excel tab/spreadsheet. This value actually comes from the query in the first Execute Sql Task (Called Create New File with tabs) before the Foreach Loop. [b]The sql is[/b]: select distinct replace(a.Region,' ',''), b.SubsidiaryNamefrom dbo.Marie_Regions a INNER JOIN dbo.Geography bON a.region = b.regionwhich populates a SINGLE object called 'List of Countries' with two sets of values. These values are references as '0' and '1'.When you are setting up the Foreach Loop Set the Variables equal to one of the populated value lists in the object 'List of Countries'. What does that mean? Well, define the variable 'ListofGroups' and set the INDEX as 1ExcelFileNameSubDataSetThu, 10 Jan 2008 11:05:04 GMTmdeschen1http://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxah ok; I posted this link but the sample here is a little bit different.Thu, 10 Jan 2008 08:38:14 GMTimaddaouhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxHi,I di use use dataflowTask , and went through cursor.Problem was in executeSql task within dataFlowtask and ExcelTab variable.When I changed from file name into Create Table .... (using sample from this link) it worked.http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.htmlradovanThu, 10 Jan 2008 08:31:18 GMTrdobrichhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxDo you have a complete sample where you can e-mail it and check what is missing? Where the DataFlow is included as well. My problem is with the Destination Excel File where I can't define the variable. it asks for column mapping and there are no columns in the variable. :)ThanksThu, 10 Jan 2008 08:27:44 GMTimaddaouhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxHmmm, something must be lost in the translation... :-) This solution indeed will create multiple files and each can have multiple tabs/spreadsheets with their own specific data.The key to that really is the population of the initial 'cursors' which is done in the Execute Sql task before the Foreach Loop. By populating the Group cursor from a table in the database containing the names for the files and populating the Sub-Group cursor from a different table in the database containg the names for the tabs/spreadsheets WITHIN the Groups you create the files and tabs/spreadsheets within the tabs.Next the specific Select statement against the rest of the database uses the Group and/or the Sub-Group cursor value to select the correct data for just that Sub-group to populate the correct tab/spreadsheet. It really depends on your database schema. In our case we had to create a Group only table as all the rest of the data is in the schema. If I may, it is a matter of buckets of data within buckets of data and then using those buckets to pull what you want. Hoping this helps a bit....Thu, 10 Jan 2008 08:11:29 GMTmdeschen1http://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxThanks, but this means you didn't use the DataFlow Task. What you mentioned is only creating several files with one sheet or one file with several sheets. I just wanted to see if you were able to do the whole sample where we can create multiple excel files with multiple sheets dynamically as the article explains.I think the article is not complete or there is something missing somewhere.Any way thanksThu, 10 Jan 2008 08:01:32 GMTimaddaouhttp://www.sqlservercentral.com/Forums/Topic440424-1177-1.aspxHi,I little change sample, so I use this variables:1)FilePath='c:\temp\sample.xls'(but, I can dynamicly create file name through expression properties, like"c:\\temp\\test_" + substring((DT_STR, 50,1250) GETDATE(),1,10)+".xls"if I need file namec:\temp\test_2008-01-10.xlsEvaluateAsExpression property needs to be 'True'2)In Excel connection manager in Expression Property I set 'excelFilePath' to @[User::FilePath]3)ExcelTab Variable is set throuh Expression to(EvaluateAsExpression property needs to be 'True')"CREATE TABLE `" + @[User::region] + "` (`my_field` VARCHAR(50))"4) In Execute SQL Task, I SetConnection Type to 'Excel'Connection to existing Excel connection manager (created in step 2)SQl source type to 'Variable'SourceVariable to 'User::ExcelTab' (created in step 3)Execute workws perfectly if excel file with @[User::region] doesn't exists.It will fail if it exists.radovanThu, 10 Jan 2008 05:05:56 GMTrdobrich