March 18, 2008 at 8:24 am
Hi, I need to import +/- 500 xls files into my sql server 2005 database. Is there a way I can create a package to import all these files simultaneously.
Thanks....
March 18, 2008 at 9:17 am
u can import all the file using a single package if it does have the same attributes ....create a package with foreachlloop in which set the option for for each file and creat a variable in the package ...it gets executed one by one .....if need more detailed let meknow and plz post with mpre information abt ur project .....
Thanks,
Chinna
Its the Journey which gives you Happiness not the Destination-- Dan Millman
March 18, 2008 at 5:40 pm
Without creating explicit connections for each file, I doubt if you can do them simultaneously. However, as the original responder indicated you can run them sequentially by using a For Each loop.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
March 19, 2008 at 1:06 am
Hi Kurraraghu, im a newbe with regards to integration services..I have got +/- 500 excel files located in a specific folder. All the files are in the same layout and format..I just need to get all the files into one of my tables in my DB. If u can please assist with more detail on creating the package, I would really appreciate it.. Thanks
May 27, 2008 at 7:09 pm
i'm trying to do the exact same thing on this... any additional info on how
to set this up would be G R E A T L Y appreciated!
thanks
May 29, 2008 at 1:14 am
Hi,
The For Each Loop Container in SSIS is actually reaaaaally simple.
Here is a simple guide:
a) Create a dataflow task.
Specifiy any one of the excel files you have in your connection manager.
Specify your SQL connection as an OLEDB connection in your connection manager.
Drag an excel source and an OLEDB destination to your data flow from your toolbox.
Configure the excel source to point to your excel connection. This will ensure that your column headers and metadata is picked up automatically.
Click on your excel source, and drag the green arrow to your OLEDB destination. Do this before configuring the destination so that the transformation mapping is done automatically
Configure the OLEDB destination to point to your SQL connection. Specify the table that you would like to load data to.
Tada, one file will now import to SQL
b) Create 2 variables. Make sure that these variables are configured while you are not in the dataflow task. Why? Because this will ensure that the scope of the variables are set to package and not the data flow task.
One variable contains your folder name that you are pointing to, and the other variable contains the filename that will be populated by the for each loop enumerator.
c) Drag a for each loop enumerator from your toolbox onto the control flow, and then move your dataflow task into the enumerator
Double click on the enumerator to see the properties
On the collection section, there are a few things to watch out for.
Change the enumerator to a For Each File (this will tell SSIS that it needs to loop on files)
Specify the folder where the loop needs to enumerate on (in other words, if your files are stored in c:\MyExcelMillionsOfFiles\, that is what you will put in there)
Specify the file name that the loop needs to enumerate on (for example MyExcelFile*.xls)
You can even specify that it needs to enumerate on all the subdirectories)
Here comes the first little trick. On the expressions, add an expression. You will notice that there are a lot of cool properties here, the one you are after is the directory. Drop your first variable for the directory in there. This will allow you to change the variable value later on without having to physically change anything else
On the variable mappings section, add your second variable containing the filename. This will let the enumerator pass the next filename to the variable
d) Last trick... on your connection manager, the excel file that you added needs to be modified.
On the expressions, add an expression, and use the connection string. Point the connection string to the variable containing the filename
TADA
What will happen?
The enumerator will loop on the directory you specified, changing the file name variable each time it loops
Once the file name is changed, it will execute the dataflow task, which will now have the new filename present
Really simple...
July 3, 2008 at 7:59 am
Hello
i jsut read the post above and have been trying to get the foreach loop working, but i am having the following issue and i hope you can point me in the right direction.
once I add the expression to the excel source it errors out and the connection string property changes from this
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Import\test.xls;Extended Properties="Excel 8.0;HDR=YES";
to this
Provider=Microsoft.Jet.OLEDB.4.0;
and then the process does not run.
I belive that it might be to do the creating the variables, I have not used variables in SSIS at all so if you could just give me a little more help on that - for example the directory / folder variable, I is it correct to assume that it is a sting and the value - should it only be the folder name or the whole path?
and help would be great
August 28, 2008 at 10:28 am
Use this connection string
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ @[User::packagepath] +";Extended Properties=" +"\"" +"Excel 8.0;HDR=YES" + "\"" + ";"
packagepath:- is a varable that has to be created
January 1, 2009 at 6:23 pm
Hi all,
I am getting this error after doing the last step.
the connection string is not valid. it must consist of one or more components of th form X=Y, seperated by ;(semicolon). this is the error occurs wehn a connection string with zero component is set on database connection manager.
the result of the expression @user::variable on property "connectionString" cannot be written to property the expression is evaluated but cannot be set on the property?
Pleaes help urgent???
Thanks
Pat
January 27, 2009 at 5:15 am
I'm having the same problem with my Connection String. Can anyone provide a solution? Thanks
March 20, 2009 at 9:21 am
Buchireddyg ,
Bingo.. It worked for me , Thanks a lot
Thanks a lot,
Hary
April 27, 2009 at 7:28 am
I have tried this method several differant ways.
I get the fowwong error
TITLE: Package Validation Error
------------------------------
Package Validation Error
------------------------------
ADDITIONAL INFORMATION:
Error at Data Flow Task [Excel Source [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
Error at Data Flow Task [DTS.Pipeline]: component "Excel Source" (1) failed validation and returned error code 0xC020801C.
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow Task: There were errors during task validation.
Error at Excel_ForEach_Loop [Connection manager "Excel Connection Manager"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not find installable ISAM.".
(Microsoft.DataTransformationServices.VsIntegration)
------------------------------
BUTTONS:
OK
------------------------------
and this error on the Excel connection Manager
TITLE: Microsoft Visual Studio
------------------------------
Error at Excel_ForEach_Loop [Connection manager "Excel Connection Manager"]: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Could not find installable ISAM.".
Error at Data Flow Task [Excel Source [1]]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020801C (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
BUTTONS:
OK
------------------------------
Now before I added the changes this connection to the file worked.
April 27, 2009 at 11:32 am
check this Link...I executed using this.
http://bi-polar23.blogspot.com/2007/08/loading-multiple-excel-files-with-ssis.html
April 27, 2009 at 11:54 am
Demin99
That is exactly the one that gives me the error. I started with that example and move to the msdn link
May 14, 2009 at 5:58 pm
right-click on the Excel Connection Manager
select properties
click on the + sign by "Expressions"
click on the elipsis ...
select "ConnectionString" in the Property drop-down
copy and past exactly, including quotes, no spaces:
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::FileName] + ";Extended Properties=\"Excel 8.0;HDR=YES\";"
click OK
close the properties dialog box
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply