May 4, 2007 at 3:16 pm
I am unable to import data from multiple mdb files that have the same file structure, data type but different file names.
I am able to use multiple flat files inside the etl solution to import different file names into SQL Server 2005 database by inserting a foreach loop container setting the enumerator to file enumerator making sure the file path is the correct one and using the <base file name>_*.txt in the collection pane. Then right click flat file manager to bring up properties and click ...button located in expressions, which brings up Property Expressions Editor. Choose connection string and click expression ...button and expand variables file to click and drag user::<FileName> that was created inside the foreach loop container from above into the expression box. Take the data flow task into the foreach loop container and it worked without issue.
However, the issue that I am having is because it isn't a flat file, but rather a mdb file where you cannot use the flat file connector to accomplish the same issue that I was able to resolve from above. Has anyone else experienced similar issue, if so, what was the solution?
I appreciate any insights that you may have to offer on this subject!
Thanks!!
May 7, 2007 at 6:01 am
Create a single connection to a dummy access database and use a file system task and a loop container to copy each of your databases to that file name / location.
You can overwrite the database each time you go through the loop with the one that you want.
Be cure that you do not leave connections open - if the file is in use, you cannot overwrite it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply