Foreach Loop Problem (Bug?)

We discovered that the Foreach loop isn't recalculating the number of files found upon each new loop iteration, so if any new files are placed into the receive directory during the looping, they are not detected/processed. is this a bug or product intention?

Jason,

I would guess that this is by design - and rightly so in my humble opinion.

-Jamie

|||Well comes problems when you have a listener that triggers a foreach loop, but when loop completes and goes back to listening, the files that were placed on directory during loop iteration just sit there until a new file or change occurs.|||

I am pretty sure the documentation does mention this.

You could wrap the for each in another loops until no files are processed.

ForEach Loop - Testing for when Enumerator is Empty

I have a SSIS package this set to run at a specific time each day. If there are no files for the ForEach tool to work upon...while it doesn't 'fail'...I would like to test for the condition that the enumerator was empty...so that I could send an email message reminding someone to followup and investigate.

What would be the best way to test for that condition?

Cordell,

You could add a counter variable to the package, use a Script Task within the ForEach loop to increment the variable, and then use an expression on a precendence constraint following the loop to decide to mail based on the count variable still being zero.

Here are some helpful links to get you started:
About variables - http://msdn2.microsoft.com/en-us/library/ms141085.aspx
Using variables in Script Tasks - http://msdn2.microsoft.com/en-us/library/ms135941.aspx
Precendence Constraint - http://msdn2.microsoft.com/en-us/library/ms141261.aspx

Cheers,
Patrik

|||

thank you Patrik for researching a solution for my need. I thought this is what I would have to end up doing, but wanted to make sure I was not missing something obvious.

It would be nice in the next major update of SSIS that this condition would be provided as an attribute/event to test for in the ForEach Loop tool.

...cordell...

p.s. Is there a place at MSDN to enter feature requests such as this?

|||

Cordell,

Feedback can be submitted to http://connect.microsoft.com.

Glad I could help,
Patrik

Force Modification of a User Defined Function

Is there a way to force the modification of a User Defined Function upon which depend other objects.

This is the message:

Msg 3729, Level 16, State 3, Procedure FunctionName, Line 22

Cannot ALTER 'FunctionName' because it is being referenced by object 'OtherObject'.

I have 48 objects related to the function and each time I need modify the function is a big pain.

Any help will be much appreciated.

Best Regards

ggpnetwork

Sorry, but AFAIK there is no switch to do so. If you have a computede column are something else bound to that function you have to drop and recreate the referencing object / Column or something else.

HTH; Jens Suessmeyer.|||We have currently ran into this issue but found the following solution helpful.

Originally, we were adding a scalar function to a computed column in a table. We received the same error message whenever we attempted to modify the scalar function.

However, if we created a view of the same table and included the scalar function as a column, we were able to modify the scalar without problems. That is to say, we were able to modify the scalar function definition (not the view result) without problems.

Hope this helps,
Daniel

Force Modification of a User Defined Function

Is there a way to force the modification of a User Defined Function upon which depend other objects.

This is the message:

Msg 3729, Level 16, State 3, Procedure FunctionName, Line 22

Cannot ALTER 'FunctionName' because it is being referenced by object 'OtherObject'.

I have 48 objects related to the function and each time I need modify the function is a big pain.

Any help will be much appreciated.

Best Regards

ggpnetwork

Sorry, but AFAIK there is no switch to do so. If you have a computede column are something else bound to that function you have to drop and recreate the referencing object / Column or something else.

HTH; Jens Suessmeyer.|||We have currently ran into this issue but found the following solution helpful.

Originally, we were adding a scalar function to a computed column in a table. We received the same error message whenever we attempted to modify the scalar function.

However, if we created a view of the same table and included the scalar function as a column, we were able to modify the scalar without problems. That is to say, we were able to modify the scalar function definition (not the view result) without problems.

Hope this helps,
Daniel