blogs.conchango.com

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?

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.

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

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

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.

fdsf

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

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.

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.

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!!!