Open BIDS
Create new integration Service Project, create a new
package say “ImportMultipleExcelFiles.dtsx”
1. Create a Folder ExcelFiles (C:\ExcelFiles)and then create multiple
Excel files in folder C:\ExcelFiles but make sure all Excel files have identical schema (format)
Assuming all Excel files above contain data in worksheet “Sheet1”
with below schema
City |
Value |
AY |
2000 |
BY |
3000 |
CY |
4000 |
You can have your own sheet name but keep a note, worksheet
name has to be uniform across all Excel files that contains data to be uploaded.
2. Right Click on Control Flow
Window, Select Variables
Add a variable “FileName”
at Package Level having data type string
3. Go to Control Flow add a “For Each Loop Container” Component.
Right-click on the Foreach Loop container and select Edit.
Then, Click on Collection “Collection” tab. Assign folder path and file type as
shown below
Then go to “Variable
Mappings“ tab and map variable created above like
below
6. Drag a “Data Flow Task “inside
“Foreach Loop Conatainer”, double clickson Data flow task
Drag one “Excel Source” task ,
double click on this to get “Excel Source Editor “ Window
Now choose new and new window will
open , now browse to your first excel file that is C:\ExcelFiles\First.xls
Under “Name of the Excel Sheet” on “Excel
Source Editor “, choose Sheet1$
7. Now drag “OLE DB Destination”
task , connect “Excel Source” to “OLE DB Destination”
Point this conenction to your database and create new table or use an existing table
Map both like below
8. Go to the Properties
of “Excel Connection Manager”
Expand “Expressions”
Choose “Connection String” property and assign value of Expression like below
“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + @[User::FileName] +
“;Extended Properties=\”Excel 8.0;HDR=YES\”;”
9. Click on button “Evaluate Expression” at the bottom left of above window to check for any errors
10.Set property DelayValidation=TRUE on the Data Flow task
10.Execute your Package
hi,
this page is really great. i’m new to work with SSIS and this example to integrate a set of files into the database works very well. 🙂
I have also a question.. in parallel I work with sql server 2005 (on XP Prof. 32-bit system) and with sql server 2008 (windows 7 with 64-bit system)
This example works with sql 2005 very well on the 64-bit system with sql server 2008 it doesnt work 😦 now i wonder whats the reason?
Have you any ideas how i could solve this problem?
greetings amy :o)
Thanks Amy.
Can I have error details please ?
hi,
here I have the error code..
Error: 0xC00F9304 at Package, Connection manager “Excel Connection Manager”: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.
Error: 0xC020801C at Data Flow Task, Excel Source [1]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC00F9304. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: component “Excel Source” (1) failed validation and returned error code 0xC020801C.
the excel import is not supported in 64 bit system 😦 (thats a clear message – but its ~.~ not so fine…)
greetings :o)
Hi Amy,
This is know issue already reported here http://msdn.microsoft.com/en-us/library/ms141766.aspx
As far as i know only solution is to run your package in 32-bit SSIS runtime environment!
SQL Agent Job:
If you are running your package using a SQL Agent Job, Go to job Properties ,open the step that call this package ,Go to Execution Options tab and then select check box(at the bottom) to Use 32 bit Runtime.
Command Line:
But If you’re executing the package using DTEXEC via some command-line then
“C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtexec.exe” /f “Path to your SSIS package” /X86
ok, thx a lot. 🙂 i will try it.
great help bc’z u have clearity on steps
How do you do this bit:
Step 2………. “and assign path to your first Excel File”…………
This is after you have created the variable, then it says assign path, but do you mean to the variable?
Yes
At step 3 I could not see the enumerator configuration browse button or text boxes. I found that if you click on the ForEachFileEnumerator dropdown and reload it then the objects will appear.
Also it would help if you made it clear that you need to insert the path to the excel file in the ‘Value’ box in the variable properties.
At ..
Right-click on the Foreach Loop container and select Edit.
Then, Click on Collection
Also it..
You can edit text box below “Value” and assign path of first file.
Why to assign value of first file name? To retrieve metadata information from excel to avoid validation error
Couldn’t find evaluate expression button, where is that?
Go to the Properties of ”Excel Connection Manager”
Expand “Expressions”
Choose “Connection String” property and assign value of Expression like below
“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + @[User::FileName] +
“;Extended Properties=\”Excel 8.0;HDR=YES\”;”
Check button “Evaluate Expression” at the bottom left of this window.
Another question why did you create three files and only use one?
This post explains to Import data from “multiple Excel files “
I assigned first file to variable FileName to get metadata information as all files in my example are having similar schema.
Check Step1 above where I have mentioned “First.xls, Second.xls and Third.xls, having identical schema in all three“
“For Each Loop Container” Container will assign file names (First.xls, Second.xls and Third.xls) to variable FileName dynamically, I mean all files one by one to variable FileName.
I Tried to follow the same steps, but whn i create excel source ans set the connection string and delay validation property and reopen agn the excel source file and click columns its throwing me an error. but if i dont open it n run the package its working fne….
can some one help me…
the error am getting isss
TITLE: Microsoft Visual Studio
——————————
Error at Package2 [Connection manager “Excel Connection Manager”]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: “Microsoft JET Database Engine” Hresult: 0x80004005 Description: “Invalid argument.”.
Error at Data Flow Task [Excel Source [1]]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.
——————————
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
——————————
BUTTONS:
OK
——————————
Hi Sandesh,
Assign absolute path to one of the Excel file to the variable “FileName” like C:\ExcelFiles\First.xls
Very informative info – Thanks
The problem I see with this is if you assign an absolute value, the first import works, however, say first.xls is removed from that folder, the import does not work because it is mapped to the first.xls file
Bill, it will work but don’t forget step 10.
————————————————————————-
Set property DelayValidation=TRUE on the Data Flow task
————————————————————————-
Thanks Amit… This is what I exactly looking for…
The above has worked out for me so far and has been so easy to follow! I’m looking to have the SSIS package look in a folder location for any Excel file for upload and not based on a starting point. Do you know how I can write this to look in a specified folder and pick up any Excel file for upload? My problem is if i remove the starting file for upload then the package breaks. I would also like to move the files that were uploaded to another folder location. Do you happen to have any information on this also? 🙂 Any help or shared links would be greatly appreciated.
Even if you remove your first file it will work but you have to set the property DelayValidation=TRUE (Step 10) .
Just to add this is not depended on the first file , if you make dynamic first then how will you map source to target in your SSIS package ? I have used first file to have metadata information for the initial mapping and then i making entire package dynamic .You have to provide metadata of all the files to SSIS once and for that i have used First file.
Please let me know if it doesn’t work for you
To move the files that were uploaded: Use File System Task in SSIS.
Hi
I tried on 64 bit server , it was not working , the simple solution I got on youtube
video , you have to change exclefilepath instead of connectionstring .
check this
Thanks
Thanks for the info Henry
It’s really informative and saved me lot of time. Thanks.
Thanks Napolean
Hey Can you help me with this???
I have 4 folders with one excel sheet in each folder which is having same scheme/structure only name and data will be different. I want to extract all data from those files from different folder in one table in DB. can we do this with one data flow task included in foreach container?
Sorry for the late response , point foreach container to the root folder that contains all four folders mentioned above as sub folders inside , then check “Traverse Subfolder” option shown at the bottom of below image :https://dwhanalytics.files.wordpress.com/2009/10/fel.jpg?w=500
Most of the process above worked but I keep getting this error:
Error: Failed to lock variable “User::filename” for read/write access with error 0xC0010001 “The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.”.
Any help will this error will be appreciated.
Bethel, please check this post :
http://social.msdn.microsoft.com/Forums/en/sqlintegrationservices/thread/067f723e-6524-4a59-90d3-b16dcecb1e4f
hope this helps
Awesome article! successfully imported 20 excel files within seconds! Thanks!
[…] Import Data from Multiple Excel Files using SSIS. […]
Very helpful… thanks
Hi . Is there a way to move excels to backup folder after processing is done. ?
@James: use File System Task
Hi thanks for your help , But i am getting the following error can anyone help on this please…
Attempt to parse the expression ““Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + @[User::FileName] +
“;Extended Properties=\”Excel 8.0;HDR=YES\”;”” failed. The token ” ” at line number “0”, character number “0” was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.
I got a link on the net on how to load excel with multiple sheets in a table but I got this error :
[Excel Source [53]] Error: Opening a rowset for “Sheet1$_” failed. Check that the object exists in the database.
[SSIS.Pipeline] Error: “component “Excel Source” (53)” failed validation and returned validation status “VS_ISBROKEN”.
Can anyone help me how to resolve this problem? Thanks
please check the sheet name that you are using for your excel tab, if the name of the sheet from where you are loading data is Sheet1 then not sure why you are getting Sheet1$_, I mean extra underscore at the end , please correct me in case I have misunderstood
To support .XLSX format (Excel 2010 for instance), use the following driver syntax instead:
“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” + @[User::FileName] + “;Extended Properties=\”Excel 12.0 XML;HDR=YES\”;”
Hi,
Thanks for this post. However, I’m unable to get the Connection String in the Excel connection Manager. The error log is given below.. Please help,
===================================
Expression cannot be evaluated.
——————————
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2008&ProdVer=9.0.30729.1&EvtSrc=Microsoft.DataTransformationServices.Controls.TaskUIFramework.TaskUIFrameworkSR&EvtID=FailToEvaluateExpression&LinkId=20476
===================================
Attempt to parse the expression ““Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + @[User::FileName] +
“;Extended Properties=\”Excel 8.0;HDR=YES\”;”” failed. The token ” ” at line number “0”, character number “0” was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.
(Microsoft.DataTransformationServices.Controls)
——————————
Program Location:
at Microsoft.DataTransformationServices.Controls.ExpressionEvaluator.Validate(IDTSVariableDispenser100 vars)
at Microsoft.DataTransformationServices.Controls.ExpressionBuilder.EvaluateExpression()
Please replace ” with ” everywhere