Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Expressions on ForEach enumerators

I've noticed a few people on the SSIS forum lately asking about expressions on the Foreach Loop. They are aware that its possible to set expressions on properties of the Foreach container but are flummoxed when it comes to setting expressions on the properties of the enumerators themselves. That's understandable because the properties of the chosen enumerator are not available from the "Property Expressions Editor" on the "Expressions" tab of the "Foreach Loop Editor"  (which is where they usually appear). You can see that here:

 

All is not lost however. There's a seperate route into expressions on enumerator properties via the "Collection" tab of the "Foreach Loop Editor". I've highlighted that on the next screenshot that shows properties being set on the Foreach File Enumerator.

Note the "Expressions" section on the "Collection" tab. That's the bit you're interested in.

 

Dead easy when you know where to find it! I must admit it took me a while until I discovered it.

 

-Jamie

 

Published Friday, October 13, 2006 10:35 PM by jamie.thomson

Comments

 

Danny Crowell said:

Can we set the directory at runtime from a user variable? I tried this using the Directory property in the expression editor of the foreach loop. It is not working.

The directory gets set to whatever the variable is at design time rather than runtime. How can I change the directory value to be set at runtime?

June 21, 2007 4:32 PM
 

Danny Crowell said:

I gave up on using the Foreach loop container. I recreated the login using System.IO in a script task. I find myself using script more and more with SSIS.

June 23, 2007 4:51 AM
 

Nick Jones said:

Hi Jamie,

I'm trying to set the "Directory" and "FileSpec" properties on a ForEach Loop programmatically but it doesn't seem to be working. I am probably missing something fundamental.

Microsoft.SqlServer.Dts.Runtime.Application a;

ForEachEnumeratorInfo forEachEnumeratorInfo = a.ForEachEnumeratorInfos["Foreach File Enumerator"];

ForEachEnumeratorHost forEachEnumeratorHost = forEachEnumeratorInfo.CreateNew();

DtsProperties hostProps = forEachEnumeratorHost.Properties;

hostProps["Directory"].SetValue(forEachEnumeratorHost, "c:\\somefolder");

hostProps["FileSpec"].SetValue(forEachEnumeratorHost, "*.txt");

I've checked the forums but can't find anything. Any help would be appreciated.

Cheers

Nick

July 16, 2007 12:50 PM
 

jonesynick said:

Hi Jamie,

Worked it out - I was nearly there. Thought I'd post the corrected code snippet just in case anyone else is struggling in this area.

First problem was that I wasn't setting the Properties to an expression. Secondly, I needed to set the ForEachLoop.ForEachEnumerator object to the forEachEnumeratorHost object after I'd updated the properties.

ForEachLoop forEachLoop = mPkg.Executables.Add("STOCK:FOREACHLOOP") as ForEachLoop;

forEachLoop.Properties["Name"].SetValue(forEachLoop,"Loop through files");

ForEachVariableMapping forEachVariableMapping = forEachLoop.VariableMappings.Add();

forEachVariableMapping.VariableName = "User::CurrentFileName";

forEachVariableMapping.ValueIndex = 0;

ForEachEnumeratorInfo forEachEnumeratorInfo = a.ForEachEnumeratorInfos["Foreach File Enumerator"];

ForEachEnumeratorHost forEachEnumeratorHost = forEachEnumeratorInfo.CreateNew();

forEachEnumeratorHost.Properties["CollectionEnumerator"].SetExpression(forEachEnumeratorHost, "False");

forEachEnumeratorHost.Properties["Directory"].SetExpression(forEachEnumeratorHost, "\"c:\\\\somefoldername\\\\somesubfoldername\"");

forEachEnumeratorHost.Properties["FileSpec"].SetExpression(forEachEnumeratorHost, "\"*.txt\"");

forEachEnumeratorHost.Properties["Recurse"].SetExpression(forEachEnumeratorHost, "False");

forEachLoop.ForEachEnumerator = forEachEnumeratorHost;

Cheers

Nick

July 16, 2007 3:38 PM
 

nick (not the same one) said:

Thank you for posting this - you would not believe the amount of time I wasted today trying to change the Directory property through code in a script task (visual basic).  If you have any idea how to do so, by the way, I would be grateful to hear it, but in the meanwhile, thank you thank you thank you.  a thousand nights of peace on the heads of you and yours.

December 14, 2007 12:56 AM
 

jamie.thomson said:

Nick,

Glad to hear it was useful :)

Its not possible to do it using a script task by the way.

-Jamie

December 14, 2007 9:01 AM
 

fsdf said:

fdsf

February 20, 2008 5:19 PM
 

Preet said:

I am tring to use ForEach Variable Enumerator in For Each Container, I get the folder path from table and save it in variables before entering in For Each Loop container, then in For Each Variable  Enumerator how do I set so that it loops through all files of that folder?

I have added Data Flow Task under loop and in that Data Flow Task I am using Excel source by providing path to exisiting file and then using expressions to use variable name as file name. It's giving errors as "can not detach from one or more processes, object invoked has disconnected from its clients, do you want to terminate them instead"

Could someone please help me out?

Thanks in advance,

-Preet

February 20, 2008 5:25 PM
 

imomin said:

I am new to SSIS and I was wondering if it is possible to download bunch of *.txt or *.csv file from FTP and ForEach file order by datetime created import the data into MSSQL db.

I know its possible but don't know. Also it would be nice to have video blog demonstrating some of these cool discoveries.

May 2, 2008 5:14 AM
 

jamie.thomson said:

imomin,

The existing enumerators don't allow you to enumerate over a list of files on an FTP server although that would be a GREAT idea. Why don't you submit a request for it at http://connect.microsoft.com/sqlserver/feedback/?

-Jamie

May 2, 2008 10:24 AM
 

Adnan said:

What about specifying the traversal order of files in a directory using the foreach loop task? Is that possible? For example, I would like to traverse all .csv files in a given folder according to the date on which they were created, ascendingly. Thanks in advance.

May 6, 2008 12:31 PM
 

jamie.thomson said:

Adnan,

Its not possible to set the order as far as I know. You get the list back in whatever order the file system gives it to you.

What you COULD do is populate an object variable with the list of files in the order that you want and then iterate over that. Instructions are provided, in part, here: 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 and there are links to similar posts elsewhere.

-Jamie

May 7, 2008 10:15 AM
 

Rama said:

I need to confirgure my for each loop using a variable whihc has some comma seperated values. I want to loop through each of those values in that variable. Can anyone please guide me how to go for it.

Many Thanks!!!

June 25, 2008 1:42 PM
 

jamie.thomson said:

June 25, 2008 2:16 PM
 

rubyA said:

Hello Jamie,

       I am having source query and target query in SQL server.Both the query returns two or more columns each. In SSIS i want to compare the value of these columns respectively.How it can be done?

I am using Foreach ADO enumerator to enemerate rows.Each row has source and target queries.

Thanks

July 11, 2008 9:08 AM
 

jamie.thomson said:

rubyA,

Not sure I understand. Do you mean that the queries that you want to execute are, themselves, stored in a table somewhere?

-Jamie

July 11, 2008 9:54 AM
 

rubyA said:

Yes Jamie,

I got the solution...I used foreach loop and ADO enumerator to loop through the rows to get queries.Then i used Execute SQL tasks to execute the query and have used object type variable to store the values.Then i took a script component to read the values from the object variable and stored the result in an array.Then i concatenated the array into a string and then i compared the strings.A bit complicated but i could not find any other solution for this.

The queries that you want to execute are, themselves, stored in a table somewhere? Yes queries are stored in the SQL table.

Regards

Ruby

July 14, 2008 2:08 PM
 

jamie.thomson said:

Ruby,

Rather than do all the concatenation yourself you could have used T-SQL's CHECKSUM function to provide a value per row, sum them all up to find a value for the whole dataset, and then compared the summaried value for each dataset. If they're the same then the datasets match.

-Jamie

July 14, 2008 2:22 PM
 

rubyA said:

Thanks Jamie.

I am facing a problem in postgresql. As i have mentioned i am using a object variable in execute task and then accessing it in script component.But in case of postgresql i am getting an error :-

Error: The script threw an exception: Unable to cast object of type 'System.Int64' to type 'System.Data.DataTable'.

How to use the object type variable in script task for postgresql?Any help will be very helpful for me.

Thanks

July 15, 2008 1:12 PM
 

rubyA said:

I found the solution for the above mentioned issue :--

ttp://blogs.simplifi.com/brucet/archive/2006/01/27/668.aspx

July 23, 2008 6:40 AM
 

rubyA said:

Hello Jamie,

     I am using a script component to execute a query which returns the following result set.

Count        Value

13 00501

11 00544

13 00601

11 00602

11 00603

11 00604

I am storing the resultset in a object variable.By using a for each loop and assigning the two variables in variable mapping i can loop over these rows and columns. But i have a different situation.

I am fetching the query from a SQL table so the number of columns can be diffrent. How to get the values in this case when i am having different number of columns each time?

Regards

July 23, 2008 6:46 AM
 

jamie.thomson said:

rubyA,

I doubt you can. SSIS relies heavily on metadata and if it doesn't get the metadata that it is expecting then it probably won't work.

it dpends where you are using the metadata. If you are appending columns that you aren't going to use then the Foreach loop *might* still work.

-Jamie

July 23, 2008 11:04 AM
 

Kunal said:

I am stuck with a ForEach Enumerator

In fact, I am using two ForEach containers

The first one Uses the result set returned from a query Containing 2 Columns

[FileMask]     [Path]

Xyz    C:\Dirx

abc    C:\Diry

This ForEach Iterates through each of the rows returned

And Selects The [FileMask] and [Path] into 2 variables

In the variable Mappings tab

For each Row Returned the Second ForEach File Enumerator Iterates through The given Directory returned as @Path Set As Directory in Expression and using the @FileMask as FileSpec

And Selects the Matching FileNames into a variable @FileName

I want to Access The Complete Path of the flie Inside this second foreach File Enumerator container i.e @Path+"\"+@Filename

The File is actually an Xml file and I need the full path for an XML TASK

How to proceed

Please help

August 13, 2008 4:00 PM
 

jamie.thomson said:

Kunal,

I'm not sure I understand the problem.

If you already have [Path] stored in a variable then its simply a concatenation problem is it not?

-Jamie

August 14, 2008 2:44 PM
 

Kunal said:

Oh, BTW the previous problem is solved just had to use

Fully Qualified Filename Among the options in the ForEach File Enumerator.

But now I am facing a new problem:

I need to Run the XML-XSD validation For all Xml files that are returned in the ForEach Loop, and use a data flow task to fill Tables with the data in XML.

But the thing Stops after the first XML file that doesn't get validated.

So I guess I need to set the MaximumErrorCount for the ForEach container to the number of files returned.

How can I find out the no.of files in the Enumerator??

August 16, 2008 1:26 PM
 

jamie.thomson said:

Kunal,

There's no way of doing that that I know about. Iits a good idea though, why not request it at connect.microsoft.com?

-Jamie

August 18, 2008 7:35 PM
 

Shoebox said:

Hi Jamie,

Do you know of a SSIS example of Alan Mitchell's venerable "Looping, Importing, Archiving DTS package?"

http://www.sqldts.com/246.aspx

I think a lot of folks are looking for a SSIS version of this to speed up a lot of file tasks. I've written one myself but found that xp_cmdshell and the DIR /b was actually easier to debug and customize for particular environments.  If I had more time in my day, I would try it all in SSIS and in Script.  I'm finding the ForEach Loop useful but only for things I know are fairly predictable.  

August 29, 2008 2:38 AM
 

jamie.thomson said:

Shoebox,

Does this answer your question:

Enumerating files in a Foreach loop

(http://blogs.conchango.com/jamiethomson/archive/2005/05/30/SSIS_3A00_-Enumerating-files-in-a-Foreach-loop.aspx)

-Jamie

August 29, 2008 9:12 AM
 

padamjain said:

Hi,

I am using a foraeach loop in SSIS to process multiple files from a folder and populate the data in DB. I want foreach loop to continue even if one file is failed to process. I am not getting how to to do and It is urgent.

Earlier response is appreciated.

Thanks,

Padam

January 29, 2009 9:06 AM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems