How to import hundreds mdb files to onr table?
-
I have folder which contains hundreds subfolders. Each subfolder has *.mdb file. Already I have created data flow task where I made ole db source and destination. I have imported one table from one *.mdb. It works fine. Moved data flow to Each Loop Container.
But in Each Loop Container although I have checked subfolders can't load data from all subfolders.
How can I import data to one table from dozen subfolder?
-
are you sure your wildcard is in the right place?
I use traverse subfolders without any hassles
~PD
-
I see your reply to user about importing excel files:
http://www.sqlservercentral.com/Forums/Topic470858-148-1.aspx
I DO NOT UNDERSTAND HOW TO :
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.
Variable contain your folder name. What does it mean? In variable I see only variable name, scope, data type, name space and raise change event. If I see properties for variables which I created I can't see any path for pointing to. Or in Foreach loop editor under variable mappings I can choose only variable to map to the collection value and can't change index. Does it mean that I have just to write folder name in name for variable?
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.
How to drop my first variable?
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...
-
Yes, that reply was almost a month ago...
Whatever is written in there still holds true, just tag it to traverse subfolders in your case.
Sigh, okey, lets start from the begin... What are you struggling with?
-
Hi,
Is it possible to send me an example like *.dtsx.
I could send you 2 folders with mdb files and just
make this project if u can?
I have cretated new database and transfer data from mdb file. I took only one table with name weraretst. Did it with ole db source JET to created table weraretst under created database.
path to my mdbs is d:\mdb folder with 2 subfolders which I send u in attachment.
thanks
JAgger
-
Uhhhhmmmmmm, with all due respect, I can guide you, but you have to sweat this one out yourself. Else I will always be stuck with the For Each kinda knowledge, and you may struggle with your next one (copying and pasting will only get you so far).
So here is what we are gonna do.
You are going to create a .dtsx project, with your structure, and as much of the variables as you possibly can.
From there, if you get stuck, post with your .dtsx file, as well as some .mdb examples, and we will work through them.
~PD
ps> Look at the samples on MSDN. That is what I learnt from and I didnt get stuck from day 1.
-
I had a look at your mdb databases, and there are some seriously wide tables in here.
I will make a dtsx out of a single table, I just aint gonna have the "free" time to do more than that.
Also, its gonna be the smallest table (user info).
You gonna owe me some serious kudos for this one man...
~PD
-
I need only last table from mdb file. Name is weraretst.
Now I am out of office and I'll be back tomorow with my *.dtsx.
-
Cool. You go do waretst, with its more than 100 columns, and I do an example of tblUserInfo with 10 columns.
Little bit of advice, unicode is going to bite you in Access, please remember to cater for it
~PD
-
This is very hardcoded, I didnt add a lot of dynamics.
For example, I assumed that the directoryname would sorta look the same each time.
I add a variable date and time column derivation simply to illustrate the different mdb's it loaded from, each sitting in their respective folder.
Note that this solution is to simply illustrate traversing of subfolders and connecting to different mdb files. It shouldnt be used for anything else but reference material, as it for sure isnt productional strength
~PD
-
I've got a video I did last week on working with for each loops. Give me till tomorrow and I'll post it for you.
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
-
Super! Thanks.
-
Hi. I don't know where I have mistake. Please attached find my project and please let me know what is wrong. Mdb file is located to d:\mdb. Thank you.
-
Briefly glanced at a couple of things:
a) Your mdb variable just needs to contain the folder name, you are confusing the wildcard with the foldername. In other words, it just needs to contain D:\MDB\. That will tell the for each filename loop to traverse on D:\MDBb) In your for each loop container, look at the collection section. You do have the right options set - (folder and files). What you wanna do is to add an expression. The property must be set to directory, and the expresion must be set to @[User::Mdb] - use the dropdown. All that this will do is to allow you to set where the for each loop enumerator works outside of the enumerator
c) You need a filename variable. Make sure to set the scope equal to Package. This filename is what the For Each Enumerator will use to set the file it finds as. In the For Each Enumerator, there is a variable mapping. I see you mapped this to FieldForce. This is wrong. Your FieldForce variable contains the connectionstring. The connectionstring has the filename embedded in it
Differences:
FileName: D:\MDB\FieldForce.mdb
FieldForce: Data Source=D:\MDB\FieldForce.mdb;Provider=Microsoft.Jet.OLEDB.4.0;
d) You need a scripting task which will change the FieldForce variable each time the loop enumerates. Look at the example that I gave you (look at
the "Reset varMDBConnectionString" scripting task).
Try the above, and post when you get stuck, or if it works.
~PD
-
Here's that video I promised. I've got another one, but it's not ready yet.
http://midnightdba.itbookworm.com/ssisforeachloopintro/ssisforeachLoopIntro.html
Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARantMinion Maintenance is FREE:
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply