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!
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!
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?
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?
ASKER
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
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
ASKER
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!
ASKER
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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