Foreach loop for DBASE IV problem

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

Foreach Loop and distributed files

Hi - I'm new to SSIS and am having problems figuring out how to do the following.

I need to load data from flat files into SQLserver 2005 and have created the data flows ok, but my data files are *not* located in a single directory so I cannot use the foreach file enumerator option in the foreach loop container collection. Please correct me if I'm wrong?

My approach has been to execute a SQLcommand to get the filenames from another database table and to use the foreach ADO enumerator option and mapping the returned filenames to a project scoped variable (data type object since it is a rowset).

My problem comes when I edit the properties of the connection manager to try to use that variable for the connectionstring property in the expression editor. I get an error because the datatype of the variable is not supported in an expression.

Can anyone tell me how to correct this or outline another way to solve my problem?

thanks

Brian McLean wrote:

I get an error because the datatype of the variable is not supported in an expression.

Why not? You should be posting the result of the foreach loop into a string variable.|||

But you cannot return a recordset into a string! I tried and the sql execution failed with the following error...

Error: 0xC001F009 at DAOphotLoad: The type of the value being assigned to variable "User::FileList" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC002F210 at Select Catalog files from HLA DB, Execute SQL Task: Executing the query "Select DAOcat_filename from ImgFileInfo where DAOCat_status like '%Processed%'" failed with the following error: "The type of the value being assigned to variable "User::FileList" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

|||You return a recordset into an object typed variable. Then the foreach loop works on that object variable. Using the variable mappings on the foreach loop, you can store the pieces of data in the object variable in string, int, whatver, variables.|||

Brian McLean wrote:

My approach has been to execute a SQLcommand to get the filenames from another database table and to use the foreach ADO enumerator option and mapping the returned filenames to a project scoped variable (data type object since it is a rowset).

You are in the right track; but you are missing one part; you need to shred the rowset into string variables:

Jamie has a sample package here; pay special attention to Collection and Variable mapping tabs inside of the forEach loop container:

http://blogs.conchango.com/jamiethomson/archive/2005/07/04/SSIS-Nugget_3A00_-Execute-SQL-Task-into-an-object-variable-_2D00_-Shred-it-with-a-Foreach-loop.aspx

ForEach Loop and Data Flow

In my Control Flow, I execute a data flow that opens a flat file and populates the file into a recordset.

Back to my Control Flow, I have a ForEach container that uses a ForEach ADO Enumerator. Inside the ForEach, I execute an "Execute SQL Task" that updates a table.

This is where I'm confused, while in the ForEach, I also want to call a Data Flow and use the current record (record in my ForEach) and perform several lookup tasks. Unfortunately, I'm now sure how to use the ForEach record as a source in my Data Flow....What am I missing?

Thanks,
Gary

We need to get the column values for each record in the "ForEach ADO Enumerator" in some variable.
Then for the tasks inside the ForEach container - we can use those variables.

Similar example is @. http://www.codeproject.com/useritems/foreachadossis.asp

Thanks,
Loonysan

sql

For Each Container for XML Source Adapter

I know how to For Each Loop a Flat File... But there is not File connection to reference for the XML Source adapter.

How do I run XML files through my DataFlow from a directory with the For Each Loop?I am going to assume two things:
1. the schema is the same for all of the files, and you have already set up the XmlSrc using valid data and schema to get the metadata initialized
2. you have, in your for loop, a variable with the name of the current file

In your XmlSrc, in the advanced editor, Component Properties, under Custom Properties, set the AccessMode to 1 (this means the file name is in a variable), and set the XMLDataVariable property to the name of the variable with the file name.

Let me know if that works for you.
Thanks,
Mark|||Marc,

Thank you for the Late Night answer. It worked perfectly, and I would have not figured that out without you.

Your Money!