Multiple File Import using SSIS

Hi,

Hope someone can help, I am trying to import multiple Excel Files version 2003 with a format such as this per file, all files will be in one folder.

File 1
Name     Address     PC     DOB
PD          3 Street      CH1   10/06/1970

File2
Name     Address     PC     DOB
EG          4 Street      CH3   22/04/1963

File3
Name     Address     PC     DOB
RS          5 Street      CH6   13/11/1954

I want them to populate just one table in a SQL Server 2005 database, I am trying to do it using SSIS but really not sure where to start as I have never used it before and due to very tight time contraints dont have the time to look around on the net for a different solution, something that I will be doing once I have got this out of the way, does anyone have a blow by blow account about what needs to be done.

Thanks
nighttressAsked:
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:
If worksheets in all those have the same names and files are in the same directory then it sould be easy to achieve.

Use this link: http://www.sqlis.com/55.aspx to see how you can iterate through files.

The only differences are:
- instead of *.txt files you will use *.xls mask,
- instead of setting dynamic ConnectionString property, you will set a dynamic ExcelFilePath (of Excel Connection Manager) property,
- in ForEach Loop container there should be a Data Flow Task containing data transfer between Excel Source and OLEDB Destination.
0
nighttressAuthor Commented:
hi brejk,

Thanks for the reply, I think I have followed everything to the letter but making the relevant changes due to using Excel, when I created the initial package, no problem it imported on file, I have put in the LOOP and assigned the variable but every time I run it I get a selection of errors, the first being :

Error at Data Flow Task [Excel Source [1]]:  The AquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.

Then a number of related errors, when checking back through the pack I can see that the Excel Connection Manager has nothing in the "Excel File path" drop down which I think has something to do with setting that variable.

Does any of this make sense as I am really not sure what is happening.

Thanks PD
0
brejkCommented:
You have to set ExcelFilePath as a dynamic expression (look for Expression property of Excel Connection Manager).
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

nighttressAuthor Commented:
If I understand what you have said I already have under the Expressions option

ConnectionString @[User::FileWeJustFound]

And have now put in the following as the ExcelFilePath

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +@[User::FileWeJustFound]+ ";Extended Properties=\"Excel 8.0;HDR=YES\";"

When I do the above I get a new error,
Could not set property:
The connetion string components cannot contain unquoted semicolons

Not sure if what I have put is correct, can you take a look please or even if I have understood you correctly, sorry for being a bit dim with this, not something that I have used before!

Thanks
0
brejkCommented:
Do not set the ConnectionString dynamically for Excel files. This is done by SSIS! Just put the file path into ExcelFilePath property (in Expressions choose ExcelFilePath and set its value to FileWeJustFound variable).
0
nighttressAuthor Commented:
I try to put just the file path into the property of the ExcelFilePath but when I click out it just deletes it and under expressions I do not get to choose ExcelFilePath so set its value.  I have attached a file with a screen grab of what I am seeing at the moment if that helps
example.doc
0
brejkCommented:
The screenshot shows that under Expressions you are still trying to set ConnectionString property... I have already written that under Expressions you need to set ExcelFilePath to your variable containing file name...
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
nighttressAuthor Commented:
I have set what you described as per new attached example but it is still coming back with the same errors.
example2.doc
0
nighttressAuthor Commented:
Hi brejk,

Thanks for all the help with this, I have finally found out where I was going wrong, I had declared there was a variable but hadn't set the dam thing or if I had I must of deleted it, all working now, thanks very much for your patience!
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 2005

From novice to tech pro — start learning today.