SQL 2005 SSIS Importing from Excel

Create a Package level string variable (let's name it ExcelQuery). Set the default value of the variable to:

SELECT [YourSheetName$].* FROM [YouSheetName$]

where YourSheetName is the name of the sheet in the workbook set in your Excel connection manager (the first Excel file to be imported in the loop).

Then put a Script Task in the beginning of ForEach Loop Container task sequence. Put ExcelQuery variable in the ReadWriteVariables property of the Script Task  (double click on the Script Task, go to Script tab and in ReadWriteVariables write: ExcelQuery). Then in the script (click Design Script... in Script tab of Script Task Editor Window) do the following:

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.IO

Public Class ScriptMain

    Public Sub Main()
        Dim f As New FileInfo(Dts.Variables("User::FileName").Value.ToString())
        Dts.Variables("User::ExcelQuery").Value = "SELECT * FROM [" + _
          f.Name.Substring(0, Len(f.Name) - Len(f.Extension)) + "$" + "]"
        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

I assume your variable used for storing the filename in ForEach Loop container is named FileName (or rather User::FileName). Then go to Data Flow Task in which you extract the data from your Excel files, double-click on Excel Source and change "Data access mode" property to "SQL command from variable". Choose User::ExcelQuery as the "Variable name".

This should work.

thanks brejk i am going in just now and will try it

Brejk
I must still be doing something stupid, the loop works picking up the different filenames but the worksheet stays the same.When I put a script task in the loop and get it to change the worksheet name it throws a metadata  out of synch error.When i also change the excelsource to validateexternalmetadata to false it just fails.
I think this will work I just need to figure out how to get it to change the worksheet name without knocking the metadata out of synch.

Am i missing something or just being totally stupid ?

Thanks for your help so far it has been great and is definately pushing me in the right direction.

Are you sure you have configured Expression property of your Excel Connection Manager? You should configure it so Connection String should be equal @[User::FileName] (the value of FileName variable).

Your right I knew i was doing something stupid......
although I am now getting a connection string error....aarrrgh

Sorry, you should set dynamically ExcelFilePath not Connection String.

@martin05

If you need some example package, let me know.

dont believe this I am now getting a failed to load error

If you need some example package, let me know.

That would be great brejk

I will download them tonight brejk ,iam on my laptop over a gprs connection nearing its monthly limit.
Thanks once again for your help and i will keep you up to date tomorrow

Thanks for your help Brejk i have awarded the points
I will be posting another question relating to this I hope you can help with that too

Still no points for this question :-) Have you chosen the right answer for your question?

Excellent Answer with examples

Doh........

Thanks ;-)