I've followed the steps on how to use the ForEach loop container using a Flat Text file and it's working fine. I tried reading the text file and put it in my SQL table. However, I have some DBF files as well and the ForEach loop does not work at all with my DBF files.
Here is my scenario:
A connection manager pointed to C:\DBF_FILES
On the Control Flow: a Foreach loop container with Foreach file enumerator. (checks for IFUL*.DBF files and retrieves the Fully qualified name.
Within the foreach loop container is my DataFlow (import)
Within the dataflow:
OLEDB datasource
data access mode: Table name or View Name variable
Variable nam: username::Filefound
Now from this point on, I'm already having problems, here is the error:
An OLEDB error has occured.
An OLEDB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers.
Data source name not found and no default driver specified.
Error at data flow task...
There is also some error that say i don't have a destination table specified. Plus the error is on the OLEDB datasource not having a destination table? I don't understand this part.
Please help!
From what I understand of your post in my thread, I think what you want to do is simple. On the Foreach loop set the Folder to C:\DBF_FILES, set Files to *.dbf and select the "Name only" radio box in teh Retrieve file name.
That should set your configured variable in the Variable mapping to the name of the dbf file for each iteration. In your DataFlow set the data access mode to "Table name of view name variable" and the variable to the one you used in the Foreach variable mapping.
|||Oh you should note, that you can only open one type of DBF file with that OLE DB Source. Meaning, all the files will need to have the same schema. If you have files with different content your going to either have to select them and handle the differences before this part. I have a couple ideas but it really depends on what you situation is.
|||I think I see your problem, I actually put something together really quick to test that idea. I'll have to think about it a bit more later.
|||Thanks Dan,
Yup, i only have the same schema for all the DBF files that I'm trying to retrieve. If I test the data flow with an implicit database name, it works fine. It's when I put it to the Foreach loop that I'm having the problem.
When I test the foreach loop with flat file, it works fine, but when I use the OLEDB, that's when it fails, I think I am following everything that needs to be done, it's just that all the samples for foreach loop has flat files for data source, and I expect it to be the same to the other types of data source. Maybe there is a missing link when the data source is not a flat file...
|||Opps, I didn't have my Connection manager pointing to the right directory. What I described does in fact work. I'll try and post up some screen captures of how I have the package setup and perhaps you can see what isn' t configured right on your end.
This is about what I can write-up at the momment.
http://www.infrandom.com/42/Blog/tabid/53/EntryID/2/Default.aspx
|||I've been doing and followed the steps exactly as you have laid out. My problem is on the OLEDB source. When I fil up the: OLEDB Connection Manager, data source mode and the variable name, when I click OK, it gives me the error:
Error at Data Flow task [OLE DB Source(431)] A destination table name has not been provided.
I really really appreciate the screen caps that you have provided. It really helps a lot. However, it is on that OLEDB source that I can't click OK due to the error, although my screen looks exactly like yours already.
Is there perhaps something that's not installed on my machine?
Thanks a lot.
|||Another thing that I'm playing around just to be able to fix this is giving my variable a valid initial value. I am sure that the DBF file exists in the list but the error that I get when I do that is:
Opening a rowset for 'TBLNAME' failed. Check that the object exist in the database.
I tried TBLNAME.DBF, I tried TBLNAME only, but it just can't find it.
I made DBASE IV a version V but it said couldn't find installable ISAM. So I put back the DBASE IV.
On OLEDB Source:
The thing is I made my table access mode as table or view, and I choose TBLNAME from the list, I am fine. The problem is when i make my table access mode as 'table name or view name variable' and on the variable, i select my variable with my initial value as TBLNAME, it just can't find it.
I'm already lost. I gotta get some fresh air.
|||View the variables, if you scroll the pane out, you can set the initial value of the variable in the designer. Give it a valid file name for a file in the directory. (There has to be a file present in the directory design time at least).
|||I got that error when my Foreach loop directory and my Connection Manager directory were not point to the same directory. Once I updated to connection manager to the same directory I was able to run the package. Again, once you set the detault value of the variable just name no extension I think you should have it.
|||Whew!!!
sometimes, a fresh air is really what it takes to get the mind cleared out.
Yup, I give my variable a valid file name. The problem was that only the DBF files were in that directory, not the index files. So I copied the index files as well, and it's now doing it's thing.
I followed everything that you have laid out on your screen caps.
Added a valid value to the variable. Made sure that the name retrieval is set to 'NAME only'. I set it to 'fully qualified path' at first, which contributed to the error. And finally, make sure that both the DBF and CDX files are present.
THANKS A LOT!!!
Really appreciate it.
|||Glad you got it working!
sql