Switching Between Worksheets in an Excel Source

Question

Switching Between Worksheets in an Excel Source

cdun2

SSCertifiable

Points: 7893
More actions
February 24, 2007 at 4:59 pm

#72628

Hello,
I need to pull data from an Excel Workbook where the data is stored in worksheets that are each named one of the twelve months of the year. The package is fired by a job that runs once a month. Currently, I have to go into the select statement of the Excel source before the process runs and change the name of the Worksheet that the data is pulled from.
How can I set up the sql statement for the Excel source so that I can pull from the Worksheet that contains the name of the current month?
Thank you for your help!
CSDunn

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Terry-239890 Ten Centuries Points: 1387 More actions · Answer 1

I thought I could replace the Excel table name dynamically using Global Variable but Excel syntax is different from SQL syntax. So I failed to deliver which could have been a lot simpler than what I provide here.

So this is an alternate solution.

1. Create an ActiveX script task:

Function Main()
dim pkg
dim conTextFile
dim stpEnterLoop
dim stpJan, stpFeb, stpMar ', stpApr ,etc for all the months

set pkg = DTSGlobalVariables.Parent
' create all the month steps objects
set stpJan = pkg.Steps("Create Table [Database].[dbo].[Jan$] Step")
set stpFeb = pkg.Steps("Create Table [Database].[dbo].[Feb$] Step")
set stpMar = pkg.Steps("Create Table [database].[dbo].[Mar$] Step")
' Initialise all the steps to disabled
stpFeb.DisableStep = True
stpFeb.DisableStep = True
stpMar.DisableStep = True ' .... for other months as well
' Enable the step for the current month
Select Case Month(Date)
      Case 1
   stpJan.DisableStep = False
      Case 2
   stpFeb.DisableStep = False
      Case 3
   stpMar.DisableStep = False
'      Case 4
'   stpApr.DisableStep = False
End Select
Main = DTSTaskExecResult_Success
End Function

2. Create SQL Execute Tasks for month tables: names shown in the ActiveX script, e.g. "Create Table [Database].[dbo].[Jan$] Step". You may have to create 12 of these.

3. Create 12 Transform data task for each month.

So, main idea is only one of the 12 SQL Execute Tasks is enabled while all the others are diabled. If anybody find correct syntax for the dynamic Global Variable replacement for Excel please let us know. I hope this could still help you.

cdun2 SSCertifiable Points: 7893 More actions · Answer 2

Thanks for this feedback. I'm also considering use of the Dynamic Properties task, or I may set the Workbook up as a linked server and see where that gets me.

CSDunn