none
Excel source with has different sheet names

    Question

  • So I used to have a problem with the name of the excel file changing every day and I fixed that with a batch file that runs before the SSIS package runs and makes the name the same everyday. What I can't find out how to do is to change the name of the actual excel sheet if there are multiple sheets. I think i can do it with a variable but I have no idea how. I am running SSIS 2008 x64 
    Wednesday, June 29, 2011 5:57 PM

Answers

  • This code placed into a Script Task should rename an Excel worksheet: http://vijirajkumar.blogspot.com/2010/04/rename-excel-sheet-net.html, you will need to interact with your package variables I guess like this: http://codingstuffs.blogspot.com/2008/04/ssis-readwrite-package-variables-inside.html


    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, July 06, 2011 6:20 PM
  • This is going to sound stupid but is there no straight forward and easy way to go about doing this? These all look like really big work arounds but maybe its just me 
    If you can use third-party solutions, check the commercial CozyRoc Excel Task. One of the action it supports is worksheet rename. This is the easiest solution and it doesn't require programming skills.
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    Thursday, July 07, 2011 6:14 PM

All replies

  • Check out my blog;

    http://toddchitt.wordpress.com/

    I cover this exact scenario using a Script Task that reads all the Sheet names. It was initially developed for when you have multiple Excel files (one For Each loop) in a folder and each file can have multiple sheets all with the same meta-data.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
    Wednesday, June 29, 2011 6:11 PM
  • check http://plexussql.blogspot.com/2010/04/looping-through-excel-files-and-sheets.html

    it covers looping trought excel sheets one by one, you dont need to change the sheets names

    Nik


    Sincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
    • Proposed as answer by latha521 Wednesday, June 29, 2011 7:24 PM
    Wednesday, June 29, 2011 7:01 PM
  • This is going to sound stupid but is there no straight forward and easy way to go about doing this? These all look like really big work arounds but maybe its just me 
    Thursday, June 30, 2011 6:27 PM
  • You're not dealing with a straight-forward OLE DB or ADO.NET database that exposes the table names (sheet names) easily.

    It's almost like saying you want to do a SELECT ... FROM ... from several different tables in a database. You wouls still need to get that list of tables somehow. The solutions (Script Task and For Each loop) do just that.

    It's like querying the system tables in a SQL database to get the list of ones you want.


    Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.

    Thursday, June 30, 2011 6:30 PM
  • Is there anyway you can send me an example? I'm a very visual learner. show me how to do it once and I wont forget it, give me a written how to and Im lost for hours 
    Wednesday, July 06, 2011 4:20 PM
  • This code placed into a Script Task should rename an Excel worksheet: http://vijirajkumar.blogspot.com/2010/04/rename-excel-sheet-net.html, you will need to interact with your package variables I guess like this: http://codingstuffs.blogspot.com/2008/04/ssis-readwrite-package-variables-inside.html


    Arthur My Blog
    By: TwitterButtons.com
    Wednesday, July 06, 2011 6:20 PM
  • This is going to sound stupid but is there no straight forward and easy way to go about doing this? These all look like really big work arounds but maybe its just me 
    If you can use third-party solutions, check the commercial CozyRoc Excel Task. One of the action it supports is worksheet rename. This is the easiest solution and it doesn't require programming skills.
    SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
    Thursday, July 07, 2011 6:14 PM