locked
Looping through an excel spreadsheet RRS feed

  • Question

  • Being new to SSIS I wish to loop through a series of excel spreadsheets and within each workbook loop through each sheet. I am aware of the For Each container but how can the each sheet in the workbook be referenced?

    Steve

    Thursday, February 23, 2006 8:20 PM

Answers

  • Here is the draft of a revised BOL topic, copied into plain text because the HTML can't be copied neatly.

    How to: Loop through Excel Files and Tables

    Introduction
    The procedures in this topic describe how to loop through the Excel workbooks in a folder, or through the tables in an Excel workbook, by using the Foreach Loop container with the appropriate enumerator.

    Procedures

    To loop through Excel files by using the Foreach File enumerator
    1. Create a string variable that will receive the current Excel path and filename on each iteration of the loop. (The sample expression shown later in this procedure uses the variable name ExcelFile, with no initial value.)
    2. Optionally, create another string variable that will hold the value for the Extended Properties argument of the Excel connection string. This argument contains a series of values that specify the Excel version and determine whether the first row contains column names, and whether import mode is used. (The sample expression shown later in this procedure uses the variable name ExtProperties, with an initial value of Excel 8.0;HDR=Yes.)
    3. Add a Foreach Loop container to the Control Flow tab and configure it as described in How to: Configure a Foreach Loop Container.
    4. On the Collection page of the Foreach Loop Editor, select the Foreach File enumerator, specify the folder in which the Excel workbooks are located, and specify the file filter (normally *.xls).
    5. On the Variable Mapping page, map Index 0 to a user-defined string variable that will receive the current Excel path and filename on each iteration of the loop. (The sample expression shown later in this procedure uses the variable name ExcelFile.)
    6. Close the Foreach Loop Editor.
    7. Add an Excel connection manager to the package.
    Note   To avoid validation errors later as you configure tasks and data flow components to use this connection manager, assign a default Excel workbook in the Excel Connection Manager Editor. After creating and configuring the package, you can delete this value in the Properties window. However, after you delete this value, a validation error may occur because the connection string property of the Excel connection manager is no longer valid until the Foreach Loop runs. In this case, set the DelayValidation property to True on the connection manager, on the tasks in which it is used, or on the package.
    8. Select the new Excel connection manager, click the Expressions property in the Properties window, and then click the ellipsis.
    9. In the Property Expressions Editor, select the ConnectionString property, and click the ellipsis.
    10. In the Expression Builder, enter the following expression:
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""
    Note the use of the escape character "\" to escape the inner quotes required around the value of the Extended Properties argument.
    11. Create tasks within the Foreach Loop container that use the Excel connection manager to perform the same operations on each Excel workbook that matches the specified file location and pattern.

    To loop through Excel tables by using the Foreach ADO.NET Schema Rowset enumerator

    1. Create an ADO.NET connection manager that uses the Microsoft Jet OLE DB Provider to connect to an Excel workbook. On the All page of the Connection Manager dialog box, make sure that you enter Excel 8.0 as the value of the Extended Properties property.
    2. Create a string variable that will receive the name of the current table on each iteration of the loop.
    3. Add a Foreach Loop container to the Control Flow tab. For information on configuring the ForeachLoop, see How to: Configure a Foreach Loop Container.
    4. On the Collection page of the Foreach Loop Editor, select the Foreach ADO.NET Schema Rowset enumerator.
    5. As the value of Connection, select the ADO.NET connection manager that you created previously.
    6. As the value of Schema, select Tables.
    Note   The list of tables in an Excel workbook includes both worksheets (which have the $ suffix) and named ranges. If you have to filter the list for only worksheets or only named ranges, you may have to write custom code in a Script task for this purpose. For more information, see Working with Excel Files with the Script Taskb8fa110a-2c9c-4f5a-8fe1-305555640e44.
    7. On the Variable Mappings page, map Index 2 to the string variable created earlier to hold the name of the current table.
    8. Close the Foreach Loop Editor.
    9. Create tasks within the Foreach Loop container that use the Excel connection manager to perform the same operations on each Excel table in the specified workbook.

    Thursday, March 2, 2006 10:09 PM

All replies

  • Use the ForEach file enumerator.

    -Jamie

     

    Thursday, February 23, 2006 9:34 PM
  • Thanks Jamie, my problem is for each spreadsheet I loop through how do I reference each sheet / tab within the spreadsheets returned by the For EachLoop.

     Thanks

     Steve

    Thursday, February 23, 2006 9:44 PM
  • Hi Steve,

    Both of our problem is the same. I couldn't loop through Each Sheet in a Excel File. I tried with ForEach Loop File Enumerator, but  I couldn't acheive it.

    Jamie,

    Need your help.

    Thanks & Regards,

    Prakash Srinivasan

    Friday, February 24, 2006 5:05 AM
  • Hi Guys,

    Any updates or any ideas?

    Urgent Please.

    Thanks & Regards,

    Prakash Srinivasan

    Monday, February 27, 2006 4:51 AM
  • You can use a Foreach Loop and the Foreach ADO.NET Schema Rowset enumerator, return a TABLES rowset, and loop through each table. Note that both worksheets (with the $ suffix) and named ranges are TABLES in Excel. The SP1 refresh of BOL will include a new topic that discusses this and another aspect or two of working with Excel files.

    -Doug

     

    Tuesday, February 28, 2006 10:17 PM
  • Hi Doug,

    As you mentioned, I tried with ForEach Loop ADO.NET Schema Rowset Enumerator, but I am not able to provide the Connection for Excel Files. I tried with both Microsoft Jet 4.0 OLEDB Provider as well as ODBC for Excel, but it is giving me an error.

    So if you explain this in detail it will be very much helpful to me.

    Expecting your reply ASAP.

    Thanks & Regards,

    Prakash Srinivasan.

    Wednesday, March 1, 2006 5:47 AM
  • try this:
     
    set the delay validation to TRUE in your package properties, this may fix the error you are getting from the foreach loop going thru your sheets
    Wednesday, March 1, 2006 9:55 AM
  • Hi,

    I tried this setup (delay validation as true) very long back. Now my concern is like how do we create the connection for Excel when you are trying with Foreach ADO.NET Schema Rowset Enumerator.

    It is not supporting for Excel Files. Please advice.

    Thanks for your help.

    Prakash Srinivasan

    Wednesday, March 1, 2006 11:57 AM
  • Hi All,

    Setting delayvalidation  to true does not seem to help, I have a foreach loop for all the excel files, then a forech loop for the sheet names, how do I assign the variables to the for the filename and the sheet name, i am current generating an SQL qury varaible for the sheet ie select * from [sheetname$] , however the excel data source refuses to work, has anybody got a worked example or simple explanation.

    Many thanks

    Steve

     

     

    Wednesday, March 1, 2006 12:08 PM
  • You need to use an ADO.NET Connection Manager, the Jet Provider, and on the All page of the editor, enter "Excel 8.0" as the value of the Extended Properties argument.

    -Doug

     

    Wednesday, March 1, 2006 7:32 PM
  • Steve,

    When looping through tables, I assume that you will want to use "Table name from variable" in the Excel Source,

    When looping through Excel files, you will need to concatenate the filename into the connection string by using an expression. There is a sample in the following thread:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=103273&SiteID=1

    As for the validation issue, you can either put a valid file path in the ConnectionString property of the connection manager to avoid a validation error (if you've set an expression, this value will never be used), or set DelayValidation as you've done.

    -Doug

    Wednesday, March 1, 2006 7:35 PM
  • Hi,

    I tried with "Table Name from Variable" option in Excel Source also. But I am not at all able to close that dialog box as it is giving an error message.

    Also I tried giving DelayValidation as True only for DataFlow Task. Still it doesn't work. So if you can send me the process in detail, that will be really helpful to me to get this resolved.

    Thanks in advance.

    Regards,

    Prakash Srinivasan.

    Thursday, March 2, 2006 6:41 AM
  • Yes,

    I would appreciate it spelt out as I am finding this thoroughly confusing. Will keep persevering though.

     

    Steve

    Thursday, March 2, 2006 8:46 AM
  • Here is the draft of a revised BOL topic, copied into plain text because the HTML can't be copied neatly.

    How to: Loop through Excel Files and Tables

    Introduction
    The procedures in this topic describe how to loop through the Excel workbooks in a folder, or through the tables in an Excel workbook, by using the Foreach Loop container with the appropriate enumerator.

    Procedures

    To loop through Excel files by using the Foreach File enumerator
    1. Create a string variable that will receive the current Excel path and filename on each iteration of the loop. (The sample expression shown later in this procedure uses the variable name ExcelFile, with no initial value.)
    2. Optionally, create another string variable that will hold the value for the Extended Properties argument of the Excel connection string. This argument contains a series of values that specify the Excel version and determine whether the first row contains column names, and whether import mode is used. (The sample expression shown later in this procedure uses the variable name ExtProperties, with an initial value of Excel 8.0;HDR=Yes.)
    3. Add a Foreach Loop container to the Control Flow tab and configure it as described in How to: Configure a Foreach Loop Container.
    4. On the Collection page of the Foreach Loop Editor, select the Foreach File enumerator, specify the folder in which the Excel workbooks are located, and specify the file filter (normally *.xls).
    5. On the Variable Mapping page, map Index 0 to a user-defined string variable that will receive the current Excel path and filename on each iteration of the loop. (The sample expression shown later in this procedure uses the variable name ExcelFile.)
    6. Close the Foreach Loop Editor.
    7. Add an Excel connection manager to the package.
    Note   To avoid validation errors later as you configure tasks and data flow components to use this connection manager, assign a default Excel workbook in the Excel Connection Manager Editor. After creating and configuring the package, you can delete this value in the Properties window. However, after you delete this value, a validation error may occur because the connection string property of the Excel connection manager is no longer valid until the Foreach Loop runs. In this case, set the DelayValidation property to True on the connection manager, on the tasks in which it is used, or on the package.
    8. Select the new Excel connection manager, click the Expressions property in the Properties window, and then click the ellipsis.
    9. In the Property Expressions Editor, select the ConnectionString property, and click the ellipsis.
    10. In the Expression Builder, enter the following expression:
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  @[User::ExcelFile] + ";Extended Properties=\"" + @[User::ExtProperties] + "\""
    Note the use of the escape character "\" to escape the inner quotes required around the value of the Extended Properties argument.
    11. Create tasks within the Foreach Loop container that use the Excel connection manager to perform the same operations on each Excel workbook that matches the specified file location and pattern.

    To loop through Excel tables by using the Foreach ADO.NET Schema Rowset enumerator

    1. Create an ADO.NET connection manager that uses the Microsoft Jet OLE DB Provider to connect to an Excel workbook. On the All page of the Connection Manager dialog box, make sure that you enter Excel 8.0 as the value of the Extended Properties property.
    2. Create a string variable that will receive the name of the current table on each iteration of the loop.
    3. Add a Foreach Loop container to the Control Flow tab. For information on configuring the ForeachLoop, see How to: Configure a Foreach Loop Container.
    4. On the Collection page of the Foreach Loop Editor, select the Foreach ADO.NET Schema Rowset enumerator.
    5. As the value of Connection, select the ADO.NET connection manager that you created previously.
    6. As the value of Schema, select Tables.
    Note   The list of tables in an Excel workbook includes both worksheets (which have the $ suffix) and named ranges. If you have to filter the list for only worksheets or only named ranges, you may have to write custom code in a Script task for this purpose. For more information, see Working with Excel Files with the Script Taskb8fa110a-2c9c-4f5a-8fe1-305555640e44.
    7. On the Variable Mappings page, map Index 2 to the string variable created earlier to hold the name of the current table.
    8. Close the Foreach Loop Editor.
    9. Create tasks within the Foreach Loop container that use the Excel connection manager to perform the same operations on each Excel table in the specified workbook.

    Thursday, March 2, 2006 10:09 PM
  • Hi,

    Thanks a lot for your detailed response. But still I couldn't get that, because I assigned a variable called "SheetName" and set the index as 0 only in the ForEach Loop ADO.NET Schema Rowset Enumerator and I am trying to print that value inside my Script Task as Msgbox(dts.variables("SheetName").Value.ToString), but it is coming as blank only.

    Also I have created the ADO.NET connection as you mentioned in your post and given that connection in the ForEach Loop container. But no result.

    And if you can explain using the Excel Source control also, it will be really greatful to you.

    Thanks in advance.

    Regards,

    Prakash Srinivasan.

     

    Friday, March 3, 2006 6:07 AM
  • Hi,

    Anyone have answer and explain this?

    Quite Urgent.

    Thanks & Regards,

    Prakash Srinivasan.

    Tuesday, March 7, 2006 5:55 AM
  • Hi all -

     

    I'm having exactly the same issue as Prakash  here - How do you pick up the name of the worksheet from the connector in the loop? - this isn't at all clear.

    Look forward to getting somehelp on this

     

    Cheers

    Nathan

    Thursday, March 16, 2006 11:36 AM
  • Was there ever a solution to this?  I am in the same boat as these other guys.  Does setting the sheet name variable to a String work?  I can only get it to complete if is set that variable to an Object.

    Any help would be appreciated.

    Mike

    Monday, May 22, 2006 10:50 PM
  • The procedure described in the BOL topic, "How to: Loop through Excel Files and Tables," referenced in an earlier posting and found online at http://msdn2.microsoft.com/en-us/library/ms345182(SQL.90).aspx, works for this purpose. But after reviewing it carefully, thanks to your question, I found one small but significant typo: it is Index 2, not Index 0, in the Tables schema that contains the individual table name. Apart from that, the instructions in the "To loop through Excel tables by using the Foreach ADO.NET Schema Rowset enumerator" procedure in that topic appear to be complete and correct.

    Note that this should work as written without error; however, Index 0 does not contain the table name.

    If you are going to examine or display the changing table names in a Script task, of course the variable needs to be added to the ReadOnlyVariables property of the Script task.

    -Doug

    Monday, May 22, 2006 11:15 PM
  • Doug,

    Thanks for the insight on this - I've been racking my brain for a few days now on this.  Would you be able to point me in the direction for the spcific documentation on this?  I have only seen the "how to" examples.

    Thanks again for your help.

    Mike

    Tuesday, May 23, 2006 1:30 PM
  • I'm glad that your question led me to discover and fix the Index in the Tables schema rowset (2, not 0, which Excel does not use) that needs to be mapped to the variable intended to hold the table name.

    The ADO.NET Schema Rowset Enumerator is a powerful tool that I'm afraid isn't used as often as it could or should be because only ADO programmers are likely to have worked with schema rowsets. A list of the schema rowsets can be found at http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbschemaguid_members(VS.80).aspx, and the Tables rowset with its 4 columns is documented at http://msdn2.microsoft.com/en-us/library/system.data.oledb.oledbschemaguid.tables.aspx.

    As for the Foreach Loop container, it's not so easy to grasp the configuration in the abstract, without actually stepping through an example or using it a few times, but the main BOL topic is at http://msdn2.microsoft.com/en-us/library/ms141724(SQL.90).aspx.

    -Doug

     

    Tuesday, May 23, 2006 4:13 PM
  • Doug,

    Thanks to your help, I am able to loop through the sheets (tables) of an Excel workbook.  Some strangeness I ran into:  The enumerator passes the sheets back in reverse order.  That in itself isn't so bad, but a problem arises when a sheet is empty.  When the loop hits and empty sheet, it throws an error on the Data Flow Task.

    So, questions:  Is there a way to change the order that the sheets are passed back?

    Is there a way to determine if a sheet is empty, or use something in the Collection Restrictions?

    Is there a way to get the name of the first sheet?

    I may be going about this incorrectly.  In the case I'm developing for, I will only want the data that is in the first sheet.  Would a script be a better route?

    Thanks.

    Mike

    Tuesday, May 23, 2006 7:56 PM
  • Mike,

    I don't believe there is an easy solution to any of these issues without switching to the Script task. I suggested some starting points in this topic: http://msdn2.microsoft.com/en-us/library/ms403358(SQL.90).aspx.

    However the Excel driver doesn't care about physical order of tables, so if you mean that you want the worksheet that you've positioned as the first tab physically within the workbook, the driver doesn't care about that. The Excel API would let you determine this, but automating the Office apps on the server, especially in packages that often run unattended, is discouraged, unsupported, and generally Naughty: http://support.microsoft.com/default.aspx?scid=kb;EN-US;257757.

    Of course if you knew the NAME of the first worksheet, you could loop until you come to that name, or even set a restriction to retrieve that sheet only.

    -Doug


     

    Tuesday, May 23, 2006 10:17 PM
  • Would you by chance have any sample code that illustrates the actions that you mentioned in post. If so, please could you kindly mail it to me mohammed.okoro@RiyadBank.com.

    Thanks

    Regards

    Mohammed

    Tuesday, October 31, 2006 5:31 AM
  • The BOL topic mentioned in the first paragraph of my previous post contains some Script task code for that will help to point you in the right direction.

    =Doug

     

    Tuesday, October 31, 2006 2:23 PM
  • You tell me, I'm only a user with a Privacy problem and a small part of a PC left. 2 against 1000, thank you very much, I'm not playing, it's your game, I have other fishs to fry, voila.
    Thursday, January 25, 2007 7:07 PM
  • Hello All,

     

    I am tearing my hair out over the following issue:

    I have multiple excel files in a folder

    Each excel file has only one tab. But the tabs in each file are named something different.

    Each tab in each excel file has a different column name (however, they all have the same number of columns).

     

    I cannot get these files to get populate a SQL Server 2005 staging table using SSIS. I do have the same thing working in SQL 2000 DTS.

     

    Thanks a ton in advance.

     

    Ehsan 

    Tuesday, April 10, 2007 8:52 PM
  •  Atina wrote:
    You tell me, I'm only a user with a Privacy problem and a small part of a PC left. 2 against 1000, thank you very much, I'm not playing, it's your game, I have other fishs to fry, voila.

     

    ha ha.

     

    What on earth are you talking about?

    Who are you talking to?

     

    Tuesday, April 10, 2007 9:19 PM
  • Douglas, Would you please provide the link for the BOL Topic of the First Post
    Wednesday, April 11, 2007 10:46 AM
  •  

    So is there anywhere that actually takes you step by step on how to do this? The books online only tells you about 70% of what you actually need to know and assumes that you able to do the rest on your own.

     

    A good example is step 5:

     

    As the value of Connection, select the ADO.NET connection manager that you created previously.

     

    When I click on the connection dropdown there is no connection manager listed and I have to create a new one for the foreach container. I am sure that I am doing something wrong or I am SUPPOSED to create a new connection here, but being a newb I am just not getting it. Any further explanations or tutorials would be greatly appreciated.

    Wednesday, July 2, 2008 10:01 PM
  • Step 5 refers back to Step 1:

     

    "Create an ADO.NET connection manager that uses the Microsoft Jet OLE DB Provider to connect to an Excel workbook. On the All page of the Connection Manager dialog box, make sure that you enter Excel 8.0 as the value of the Extended Properties property. For more information, see How to: Add a Connection Manager to a Package."

     

    Creating a new connection manager from within the Foreach Loop Editor is fine also.

     

    I had hoped that I did a little better than 70%. Please use the Feedback link on every online Books Online page to get page-specific feedback promptly to the writer who is responsible. Thanks,

     

    -Doug

     

    (I will be out of the office for a while and may not have an opportunity to respond, if you post a reply. Thanks for your understanding.)

    Wednesday, July 2, 2008 11:51 PM
  • Thanks for the reply. The question about the connection manager helped a bunch. From the instructions it appears that you should be able to see a previously created connection manager. This just does not seem to work. I can see connection managers that I have created on a Data Flow object in other Data Flow Objects but I cannot see them in the list of connection managers for the Foreach Loop container. Also if I create a connection manger for the Foreach Loop I cannot see that connection manager from any of my Data Flow objects. Just threw me for a loop when it said to use the previously created Connection Manager.

     

     

    Ok on to my next and hopefully last question. I am having trouble applying the variable I have created to the Excel Data Flow properties. What I am not clear on is how to take the results of the Foreach loop and apply them to the Data Flow.

     

    When I create the Excel Source and attempt to use the variable I have created the variable must have the name of a valid worksheet as its value or it will not work. Well I am supposed to be getting the value from the return from the Foreach loop. It has not run yet so the variable is not populated so there is no value for the Excel Source so I am just stuck.

     

    I am sure that I am just “not getting” some small part of the process so once again any help is appreciated.

    Thursday, July 3, 2008 9:20 PM
  •  Pete-O wrote:

    Thanks for the reply. The question about the connection manager helped a bunch. From the instructions it appears that you should be able to see a previously created connection manager. This just does not seem to work. I can see connection managers that I have created on a Data Flow object in other Data Flow Objects but I cannot see them in the list of connection managers for the Foreach Loop container. Also if I create a connection manger for the Foreach Loop I cannot see that connection manager from any of my Data Flow objects. Just threw me for a loop when it said to use the previously created Connection Manager.

     

     

    Ok on to my next and hopefully last question. I am having trouble applying the variable I have created to the Excel Data Flow properties. What I am not clear on is how to take the results of the Foreach loop and apply them to the Data Flow.

     

    When I create the Excel Source and attempt to use the variable I have created the variable must have the name of a valid worksheet as its value or it will not work. Well I am supposed to be getting the value from the return from the Foreach loop. It has not run yet so the variable is not populated so there is no value for the Excel Source so I am just stuck.

     

    I am sure that I am just “not getting” some small part of the process so once again any help is appreciated.

     

    i'm experiencing difficulty in understanding exactly what you're attempting to accomplish.  you don't state the format of your foreach loop results.  you also don't state the foreach loop's goal.

     

    i need more information in order to assist you.

    Friday, July 4, 2008 7:44 AM
  •  

    Thanks for your reply. Glad to see you were doing double duty working on the 4th!

     

    Here is what I am trying to do. I have a single excel spreadsheet that I want to import. The data structure remains the same but the worksheet names changes every so often. I simply need to read the single spreadsheet and no matter the worksheet name import the data.

     

    The format of the foreach container is this:

     

    1.Foreach ADO.NET Schema Rowset Enumerator. The connection is set up to the Excel data source and the schema is set to table.

    2. The variable mapping is set to use the Sheet Name package variable (this is the same variable I am trying to apply to my Data Flow task) and the index is set to 0.

     

    Is this enough info?

    Monday, July 7, 2008 2:36 PM
  •  Pete-O wrote:

     

    Thanks for your reply. Glad to see you were doing double duty working on the 4th!

     

    Here is what I am trying to do. I have a single excel spreadsheet that I want to import. The data structure remains the same but the worksheet names changes every so often. I simply need to read the single spreadsheet and no matter the worksheet name import the data.

     

    The format of the foreach container is this:

     

    1.Foreach ADO.NET Schema Rowset Enumerator. The connection is set up to the Excel data source and the schema is set to table.

    2. The variable mapping is set to use the Sheet Name package variable (this is the same variable I am trying to apply to my Data Flow task) and the index is set to 0.

     

    Is this enough info?

     

    do you know the name of the excel file at design-time?  if not, you can determine it at run-time by using the file system task.

     

    hth

    Wednesday, July 9, 2008 6:01 AM
  • Yes the name of the Excel file is always the same. The name of the worksheet has changed in the past. I want it to pull the data from that worksheet regardless what the worksheet name is.

    Monday, July 14, 2008 8:37 PM
  • Hi Doug,

     

    I have an Excel workbook with multiple worksheets. I followed the instruction in the article on how to loop through the tables. The FOREACH loop working just find and with a TASK SCRIPT, I was able to verify that it indeed returning the worksheet name via the variable assigned.

     

    However, I run into problem when trying to use the variable in my excel connection manager. In the excel Source Editor window, I created a new connection to point to the same excel file specified in the Foreach loop container. Then I chose "Table name or view name variable" in the Data Access Mode.  And the variable name is the variable that was created earlier as instructed in the article.

     

    I got an error says "Error at Data Flow Task [Excel Source [1]]: A destination table name has not been provided. Additional Information: Exception from HResult: 0XC0202042 (Microsoft.SqlServer.DTSPipelineWrap)".

     

    I couldn't figure out what I did wrong. Would you please shed some light on this?

     

    Thanks!!

     

    Wednesday, November 5, 2008 3:12 PM
  •  tabbey wrote:

    However, I run into problem when trying to use the variable in my excel connection manager. In the excel Source Editor window, I created a new connection to point to the same excel file specified in the Foreach loop container. Then I chose "Table name or view name variable" in the Data Access Mode.  And the variable name is the variable that was created earlier as instructed in the article.

     

    I got an error says "Error at Data Flow Task [Excel Source [1]]: A destination table name has not been provided. Additional Information: Exception from HResult: 0XC0202042 (Microsoft.SqlServer.DTSPipelineWrap)".

     

    I couldn't figure out what I did wrong. Would you please shed some light on this?

     

    Thanks!!

     

     

    the excel source component expects an ole db connection manager, not an excel connection manager.  are you using an ole db connection manager to connect to your excel file?  if not, then try doing so.

     

    btw, which version of excel generated your file?

    Thursday, November 6, 2008 2:56 AM
  • I finally got it to work by assigning a valid worksheet name to the variable. Apparently the excel connection manager checking for the worksheet.

     

    Anyway, you have been really helpful throughout this entire thread which give me a jump start. thank you very much!

     

     

     

    Friday, November 7, 2008 5:06 AM
  • doug,

    sorry im an ssis newb...no scripting experience either.

    in the excel source of my data flow task i'm specifying a range in my select statement. currently it says from [sheet1$A10:T2000]

    how do i change this so that it refers to the parameter we created and also maintain the range?

    or actually how do i set the variable value to get the new sheet name on each iteration of the foreach loop?

    Thanks!
    Monday, January 19, 2009 6:19 PM