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