Foreach loop with parallel execution

Is is possible to get the iterations in a foreach loop to run in parallel? What I need to do is to spawn an arbitrary number of parallel execution paths that all look exactly the same. The number is equal to the number of input files, which varies from time to time. Any help is appreciated!

Regards,
Lars R?nnb?ck

Nope. This feature was available in some beta releases, so you may notice it references in newsgroups and forums. But it was cut due to complexity and quality issues.

|||Thanks for the answer, albeit not what I was hoping for. Could it be "simluated" by having a loop containing only an Execute Package Task and the ExecuteOutOfProcess flag set to true?

Regards,
Lars|||ExecuteOutOfProcess does not change synchronous behavior of Execute Package Task - the task still waits for the package to finish.

If you want to start child packages really asynchronously - i.e. start child and continue execution of parent package, use Execute Process Task to start dtexec, specify
application="cmd.exe" and
parameters="/c start dtexec.exe /f package file ..."

You'll also need to configure child package using DTEXEC's command line (where I've left '...').|||Note that
1) parent can't reliably get execution result from the children, since it may exit before all children finish,
2) in some cases you may get even worse performance compared to sequential execution - since all these packages will clash for processor and memory.|||Thank you very much for your help Michael. I will try the proposed solution and compare performance with running everything sequentially, which we might end up doing then. When support for parallelism was included I suppose that was done in way to minimize clashes for processors and memory, so my final question is if it will reappear in a later version or service pack?

Thanks,
Lars|||3) You don't have any control over the degree of parallelism. If you have 100 files, but only want to process three at a time for example.
|||

lasa wrote:

Thank you very much for your help Michael. I will try the proposed solution and compare performance with running everything sequentially, which we might end up doing then. When support for parallelism was included I suppose that was done in way to minimize clashes for processors and memory, so my final question is if it will reappear in a later version or service pack?

Thanks,
Lars

The smart money says this will appear in a later version. Alot of people are asking for it.

-Jamie|||

Wouldn't it be possible to achieve control over the degree of parallelism using a Dummy package and the /MaxConcurrent flag of dtexec? Say I start four "real" packages in parallel using cmd.exe and use /MaxConcurrent 4 as an option to dtexec, then I start one dummy package using dtexec directly with the option /MaxConcurrent 1. The way I have understood it, the dummy package will now be queued for execution and will start only when the number of parallell processes goes below 1, i e when all four "real" packages are finished?
I am going to try this out and will report back.
Regards,
Lars

|||Since the experiment above didn't work out the way I thought (the dummy package started regardless of the fact that four other packages were running) I am guessing that I have misunderstood the /MaxConcurrent option. Taken from BOL:

Specifies the number of executable files that the package can run concurrently. The value specified must be a non-negative integer, or -1. A value of -1 means that SSIS will allow a maximum number of concurrently running executables that is equal to the total number of processors on the computer executing the package, plus two.

What kind of executable files is the text referring to? Those that are called using the "Execute Process Task" within a package? It made more sense that the SSIS engine would only allow a certain number of concurrently running packages.

Regards,
Lars|||Executables are tasks in a single package. The SSIS runtime nor DTExec do not do any interprocess communication to limit the number of packages or tasks running across process boundaries.

Matt

Foreach loop with parallel execution

Is is possible to get the iterations in a foreach loop to run in parallel? What I need to do is to spawn an arbitrary number of parallel execution paths that all look exactly the same. The number is equal to the number of input files, which varies from time to time. Any help is appreciated!

Regards,
Lars R?nnb?ck

Nope. This feature was available in some beta releases, so you may notice it references in newsgroups and forums. But it was cut due to complexity and quality issues.

|||Thanks for the answer, albeit not what I was hoping for. Could it be "simluated" by having a loop containing only an Execute Package Task and the ExecuteOutOfProcess flag set to true?

Regards,
Lars|||ExecuteOutOfProcess does not change synchronous behavior of Execute Package Task - the task still waits for the package to finish.

If you want to start child packages really asynchronously - i.e. start child and continue execution of parent package, use Execute Process Task to start dtexec, specify
application="cmd.exe" and
parameters="/c start dtexec.exe /f package file ..."

You'll also need to configure child package using DTEXEC's command line (where I've left '...').|||Note that
1) parent can't reliably get execution result from the children, since it may exit before all children finish,
2) in some cases you may get even worse performance compared to sequential execution - since all these packages will clash for processor and memory.|||Thank you very much for your help Michael. I will try the proposed solution and compare performance with running everything sequentially, which we might end up doing then. When support for parallelism was included I suppose that was done in way to minimize clashes for processors and memory, so my final question is if it will reappear in a later version or service pack?

Thanks,
Lars|||3) You don't have any control over the degree of parallelism. If you have 100 files, but only want to process three at a time for example.|||

lasa wrote:

Thank you very much for your help Michael. I will try the proposed solution and compare performance with running everything sequentially, which we might end up doing then. When support for parallelism was included I suppose that was done in way to minimize clashes for processors and memory, so my final question is if it will reappear in a later version or service pack?

Thanks,
Lars

The smart money says this will appear in a later version. Alot of people are asking for it.

-Jamie|||

Wouldn't it be possible to achieve control over the degree of parallelism using a Dummy package and the /MaxConcurrent flag of dtexec? Say I start four "real" packages in parallel using cmd.exe and use /MaxConcurrent 4 as an option to dtexec, then I start one dummy package using dtexec directly with the option /MaxConcurrent 1. The way I have understood it, the dummy package will now be queued for execution and will start only when the number of parallell processes goes below 1, i e when all four "real" packages are finished?
I am going to try this out and will report back.
Regards,
Lars

|||Since the experiment above didn't work out the way I thought (the dummy package started regardless of the fact that four other packages were running) I am guessing that I have misunderstood the /MaxConcurrent option. Taken from BOL:

Specifies the number of executable files that the package can run concurrently. The value specified must be a non-negative integer, or -1. A value of -1 means that SSIS will allow a maximum number of concurrently running executables that is equal to the total number of processors on the computer executing the package, plus two.

What kind of executable files is the text referring to? Those that are called using the "Execute Process Task" within a package? It made more sense that the SSIS engine would only allow a certain number of concurrently running packages.

Regards,
Lars|||Executables are tasks in a single package. The SSIS runtime nor DTExec do not do any interprocess communication to limit the number of packages or tasks running across process boundaries.

Matt

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 Issue

Here is what I am attempting to get accomplished. I have an SSIS package that contains a Foreach loop container. This container executes a number of SQL tasks in order: SQL Task 1, SQL Task 2, SQL Task 3.

if the SQL task 1 succeeds it should flow on to SQL Task 2 and 3. This works fine when the SQL tasks do not fail...

In the event of any SQL Task failing control should flow to a send mail task to alert about the failure. Next the Foreach loop container should go to the next enumeration in the Foreach loop container and start the next new SQL task 1. So far I have been able to get the control to flow to the send mail task when a SQL Task fails. What does not work is when one SQL Task fails the entire Foreach loop fails and does not move to the next enumeration. It should only fail the package and move on.

Any help would be appreciated....

Please check the FailPackageOnFailure and FailParentOnFailure properties of ForeachLoop Container as well as Execute SQL Task Object. if any of them is defined as true then set it to false.

If this will not help you let me know.

|||

I have checked these properties and i have both of them set to 'False'... Still Fails...

Any other suggestons?

|||I suppose you could ForceExectionResult = Success|||

Hi Steve,

Here is the Solution:

1. For Foreach Loop container set "ForceExecutionResult" to "Success" so that this container never failes on execution.
2. For precedence constraint of all your tasks in Foreach loop container set the "Value" property as "Completion" so that next SQL task get executed only on COMPLETION of previous SQL task and not SUCCESS of previous one.
3. Set the "FailParentOnFailure", "FailPackageOnFailure" property to "False" for all SQL Task in container. Set "ForceExecutionResult" property to "None" for all SQL Task in container.
4. I am sure you are using "Failure" precedence constraint to send mail task from SQL Tasks.

I created a test package to try this scenario and it works :)

Thanks
Mohit

Forcing leading zero

How can I force a number to have leading zeros ?

select '123456' from dual;

I have try using TO_NUMBER :

select TO_NUMBER('123456','00999999') from dual;

But it doesn't seems to work. It will conserve the leading zeros but I want to add some.Hehe.. sorry I get it, I just have to use TO_CHAR instead of TO_NUMBER|||Thats the way it is :)

Greetz

Manfred Peter
(Alligator Company)
http://www.alligatorsql.com

Forcing a set number of result rows in a query

I'm trying to select 5 rows of data from a query. Sometimes there is less than 5 rows of data in the result set.

Is there a way to FORCE a return of 5 rows - even if they don't exist? For example, returning some text such as "No Data" or NULL in the result set?

What I'm doing to return 5 rows of data:

Select top 5 *

From MyTable

I need help modifying this query to make sure I always get 5 rows of data.

Thanks!

There is no pre-defined settings available but you do something below,

Code Snippet

Create table #Data(

Id int,

Name varchar(100)

)

Insert Into #Data Values(1,100)

Insert Into #Data Values(2,100)

Insert Into #Data Values(3,100)

Select Top 5 * From

(

Select Id, Name from #Data

Union ALL

Select NULL, NULL

Union ALL

Select NULL, NULL

Union ALL

Select NULL, NULL

Union ALL

Select NULL, NULL

Union ALL

Select NULL, NULL

)

as Data

Order By Case When Id is NULL Then 1 Else 0 End , ID

|||

Code Snippet

CREATE TABLE #temp (test int)

INSERT INTO #temp SELECT 1

INSERT INTO #temp SELECT 2

INSERT INTO #temp SELECT 3

DECLARE @.counter as int

set @.counter = (SELECT COUNT(*) from #temp)

SELECT * FROM #temp

WHILE @.counter < 5

BEGIN

INSERT INTO #temp SELECT NULL

SET @.counter = @.counter + 1

END

SELECT * FROM #temp

DROP TABLE #temp

Adamus

|||Thanks for the prompt replies - both of these replies were helpful and answered my question!

Forcing '0' to set number of decimal places

Ok heres my problem.
Ive got a field that is rounded off to a set number of decimal places that
is defind in another field. Heres the code...
=ROUND (Fields!Transaction_Quan_.Value, Fields!Decimal_Places.Value)
my problem is that when the value is zero, it returns just 0, not 0.00 for
example. This is a requirement of the report.
Ive tried using the format feature, and dont have access to the SQL at the
moment to so some sort of CAST, it all needs to be done in RS for now.
Any ideas?
Cheers
Rob.Quick work around:
=IIF(Fields!Transaction_Quan_.Value >0, ROUND
(Fields!Transaction_Quan_.Value, Fields!Decimal_Places.Value), "0.00")
There's probably a more scientific way to do it, but that should make 0
appear as 0.00. ;)
Kaisa M. Lindahl Lervik
"MACNR" <MACNR@.discussions.microsoft.com> wrote in message
news:97D2A9F3-F623-4D3C-81B3-A06ECB41E8CB@.microsoft.com...
> Ok heres my problem.
> Ive got a field that is rounded off to a set number of decimal places that
> is defind in another field. Heres the code...
> =ROUND (Fields!Transaction_Quan_.Value, Fields!Decimal_Places.Value)
> my problem is that when the value is zero, it returns just 0, not 0.00 for
> example. This is a requirement of the report.
> Ive tried using the format feature, and dont have access to the SQL at the
> moment to so some sort of CAST, it all needs to be done in RS for now.
> Any ideas?
> Cheers
> Rob.
>|||Thanks for the reply kaisa, but the main problem is that 'the man' wants 0's
to be rounded to the correct number of decimal places aswell. so if the
decimal places amount is 4, then it needs to be 0.0000 etc. Irritating i know.
Cheers anyway though ;)
"Kaisa M. Lindahl Lervik" wrote:
> Quick work around:
> =IIF(Fields!Transaction_Quan_.Value >0, ROUND
> (Fields!Transaction_Quan_.Value, Fields!Decimal_Places.Value), "0.00")
> There's probably a more scientific way to do it, but that should make 0
> appear as 0.00. ;)
> Kaisa M. Lindahl Lervik
> "MACNR" <MACNR@.discussions.microsoft.com> wrote in message
> news:97D2A9F3-F623-4D3C-81B3-A06ECB41E8CB@.microsoft.com...
> > Ok heres my problem.
> >
> > Ive got a field that is rounded off to a set number of decimal places that
> > is defind in another field. Heres the code...
> >
> > =ROUND (Fields!Transaction_Quan_.Value, Fields!Decimal_Places.Value)
> >
> > my problem is that when the value is zero, it returns just 0, not 0.00 for
> > example. This is a requirement of the report.
> >
> > Ive tried using the format feature, and dont have access to the SQL at the
> > moment to so some sort of CAST, it all needs to be done in RS for now.
> >
> > Any ideas?
> >
> > Cheers
> >
> > Rob.
> >
> >
>
>

For implementing wrap around for a table

Hi,
Iam looking a good solution for wrapping around records in the table. The
requirement is to keep only last n (eg. 20000) number of records in the
table.
After inserting the n+1 record the first record need to be removed from the
table. so on...
Is it better to place the logic in the application side or in the database
server side (using stored procedure /triggeres')?
HariUsually something like this is done in a scheduled job. I can't believe
the number has to be exactly 20000 so a job that runs every so often can
trim the old rows. You typically don't want to burden the app or the
transaction with cleanup type work.
Andrew J. Kelly SQL MVP
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:AA2E781B-31E9-4754-843C-7F1C1A939846@.microsoft.com...
> Hi,
> Iam looking a good solution for wrapping around records in the table. The
> requirement is to keep only last n (eg. 20000) number of records in the
> table.
> After inserting the n+1 record the first record need to be removed from
> the
> table. so on...
> Is it better to place the logic in the application side or in the database
> server side (using stored procedure /triggeres')?
> Hari
>|||What do you want to do with record #1 when record #20001 is added to the
table? Delete it, move it somewhere else? Is there a possibility this
could muck up relationships to other tables when these records are removed?
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:AA2E781B-31E9-4754-843C-7F1C1A939846@.microsoft.com...
> Hi,
> Iam looking a good solution for wrapping around records in the table. The
> requirement is to keep only last n (eg. 20000) number of records in the
> table.
> After inserting the n+1 record the first record need to be removed from
> the
> table. so on...
> Is it better to place the logic in the application side or in the database
> server side (using stored procedure /triggeres')?
> Hari
>|||Rows and records are totally different concepts; you need to learn the
difference if you are going to write SQL. Please post DDL, so that
people do not have to guess what the keys, constraints, Declarative
Referential Integrity, datatypes, etc. in your schema are. Sample data
is also a good idea, along with clear specifications.
Having said that, I would go with a trigger on the database side.
Ugly, but safer than depending on application to enforce the rules.

Footer Report Version number

How do I display the SQL embedded major version number of my rdl report.

Thanks,

Ken

Do you mean that you want to put a 'version' number (that you type in) into the footer or the SQL Server version number in the footer?

|||SQL server has a major and minor imbedded version number.|||

why not create a dataset with the following

Code Snippet

select version = @.@.version

|||

Thats SQL servers version. SQL report manager keeps an embedded version number for the rdl files. When I redeploy the report the version is stamped somewhere.

|||

I don't think you can get at this.

|||As a small slightly offtopic addendum to this it would be nice if there was a more robust versioning system for reports built into SSRS.

Footer Report Version number

How do I display the SQL embedded major version number of my rdl report.

Thanks,

Ken

Do you mean that you want to put a 'version' number (that you type in) into the footer or the SQL Server version number in the footer?

|||SQL server has a major and minor imbedded version number.|||

why not create a dataset with the following

Code Snippet

select version = @.@.version

|||

Thats SQL servers version. SQL report manager keeps an embedded version number for the rdl files. When I redeploy the report the version is stamped somewhere.

|||

I don't think you can get at this.

|||As a small slightly offtopic addendum to this it would be nice if there was a more robust versioning system for reports built into SSRS.