Link to home
Create AccountLog in
Avatar of ironryan77
ironryan77

asked on

SSIS Import from multiple Excel files into a SQL table

I have a 32-bit laptop using SSIS 2008, VS 2008, SQL Server 2008.  However the SQL Server
is on a remote server.  I stored all of my Excel source files in a common directory:
C:\...\My Documents\Excel input files\

And all I want to do is insert all of the non-blank rows into an existing SQL table.  I got the data
conversion working to convert this data into the SQL table, but I am not able to get the loop
in SSIS to work correctly even after trying various solutions.  I want SSIS to automatically
add data from these Excel files without me having to do any manual work.  How can I achieve
this?  The more detail, the better, since I am new to SSIS.  Thanks!
Avatar of PedroCGD
PedroCGD
Flag of Portugal image

Take a look at the paper I wrote here:
http://biresort.net/blogs/pedrocgd/archive/2009/08/06/stepbystep-ssis-extract-data-from-multiple-excel-worksheets.aspx
Download the paper in the link above!
Helped?
regards,
Pedro
Avatar of ironryan77
ironryan77

ASKER

Thanks Pedro, but I am using 2008 and not 2005.  And also my problem is I have multiple Excel files, but your problem here was one Excel file.
yeah.. you are correct...
I)'m preparing an example for you... you want to read several excel files in SSIS dinamically, correct?
The schema of each excel file is the same, correct?
Thanks Pedro.  Yes, several excel files into SSIS dynamically.  And yes, schema of each excel file is the same but the names of the worksheets are different.  Each file has one worksheet named after the year of the file: 2009, 2010, 2011.  So for example I have 3 Excel files for each of these years of data.  And in each of these files, I just want data from the first worksheets.  Make sense?
Here you have an example
1. Create a new SSIS Project
2. Rename the txt file to dtsx and add the package to the project created in the previous step
3. Change the SSIS Variables setting the new filesystem location

Helped?
regards,
Pedro
SSIS-Interface.jpg
FILE-01.xls
FILE-02.xls
Package-EE137-dtsx.txt
Thanks for the helps, Pedro!  However, I can't seem to open the package you sent since it is a TXT file.  Can you please resend this as a .DTSX file?
rename the txt to dtsx and add it to an existant SSIS Project!
Yes, I tried this but it still keeps the TXT filetype regardless of what I do.  I have an XP OS.  How can I change the filetype?
ASKER CERTIFIED SOLUTION
Avatar of PedroCGD
PedroCGD
Flag of Portugal image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Thank you very much, Pedro!  This pretty much answered my questions!  And your response was super quick!
very good!!!!
OK... so, reviewing it in Visual Studio.  Where do I find the variables?  I was going to send you an email, but couldn't see it listed on your profile.
NM... found them.  Now if I want to have them referencing these non-specific filenames, what specifically am I changing them to?