DTS excel import question

[BlessedSoul]

Hey
Ive got an excel workbook, with multiple sheets, that i need to import into a table in SQL Server 2000.
Is it possible to use global variables in the package select list?
eg....at the moment it looks like this..

select [Week1$].F1..ect
from [Week1$]

Would anyone know how to go about this, please explain
Thx for the help.

[triqshot]

hi,

what tool are you going to use?.. are you writing a program for this?..
or do you just need to import these data once?

[BlessedSoul]

Im writting an import for excel student attendeance registers.
Every week about fifty seperate excel workbooks and all sheets inside need to be imported into a SQL Server 2000 table to give analysis information for both students and management.
Each excel workbook has 12 to 36 "Week" sheets (the length of a course), containing the students names, IDs, the days of the week with a tick, or A for absent, for every week.
There is also a few sheets of summary and assessment information.
I need to import every workbook, and all the sheets in each book.

For the moment, just getting 1 workbook imported, with all 12 or 36 sheets would be nice.
Im using a SQL DTS package to import a single workbook, and am copying the first excel sheet 'Week1' into SQL Server.
At the moment im specifying a query for the import that looks like..
Select [Week1$].F1, [Week2$].F2...
from [Week1$]

Ive tried using other SQL statement to create a var and iterate the week number but i dont think the package supports this.

Is it possible to use a global package variable, inside the import query of a package, and if so how would i set it up and iterate the global so i can import each sheet in a workbook?

Hope this helps....

[triqshot]

Oh.. never tried DTS before..

Thought you were just gonna do this once.
My idea was, you open your SQL database using MS Access and just import your excel files from there.

[triqshot]

Hey, just saw DTS..

I think you can use MS Access.. i think..