SQL 2005 SSIS Importing from Excel

I am trying to loop through a folder with excel files in it and load the data into a database.
My files come in daily like data20080901.xls,data200080902.xls with the worksheet name being the same,(data20080901$,data20080902$).
I have read through both microsoft documents and the blogs on congango on how to do this but i can get the connector to dynamically change the worksheet name.

I wont be on site until tomorrow but if anyone has any  help/links to something that may solve this problem please let me know, i am pulling my hair out with it.
I know i must be doing something stupid.......  
martin05Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brejkCommented:
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.
0
martin05Author Commented:
thanks brejk i am going in just now and will try it
0
martin05Author Commented:
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.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

brejkCommented:
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).
0
martin05Author Commented:
Your right I knew i was doing something stupid......
although I am now getting a connection string error....aarrrgh
0
brejkCommented:
Sorry, you should set dynamically ExcelFilePath not Connection String.
0
brejkCommented:
@martin05

If you need some example package, let me know.
0
martin05Author Commented:
dont believe this I am now getting a failed to load error
0
martin05Author Commented:
If you need some example package, let me know.

That would be great brejk
0
brejkCommented:
Be my guest: http://potasinski.pl/download/TodayCreatedFiles.zip

View readme.txt for configuration.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
martin05Author Commented:
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
0
martin05Author Commented:
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
0
brejkCommented:
Still no points for this question :-) Have you chosen the right answer for your question?
0
martin05Author Commented:
Excellent Answer with examples
0
martin05Author Commented:
Doh........
0
brejkCommented:
Thanks ;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.