Excel source with has different sheet names
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
June 29th, 2011 1:59pm

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.
Free Windows Admin Tool Kit Click here and download it now
June 29th, 2011 2:14pm

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 NikSincerely SH -- MVP, MCITP 2008, MCTS 2008 & 2005 -- Please kindly mark the post(s) that answered your question and/or vote for the post(s).
June 29th, 2011 3:03pm

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
Free Windows Admin Tool Kit Click here and download it now
June 30th, 2011 2:29pm

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.
June 30th, 2011 2:33pm

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
Free Windows Admin Tool Kit Click here and download it now
July 6th, 2011 12:21pm

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.htmlArthur My Blog
July 6th, 2011 2:22pm

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/
Free Windows Admin Tool Kit Click here and download it now
July 7th, 2011 2:16pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics