Asynchronous data flow tasks how to run more than 4 at a time

Hi guys,

i have a for each loop and it has about 20 data flow tasks (simple data extractions). i notice when i run the package it only runs up to 4 data flow tasks at a time. others have to wait till one of the first 4 flows finishes.

i was wondering if there's a way to change the limit of how many data flow tasks can run at a time. is there a property some where ?

i know this will be stressfull to the server, but the server is well equiped with CPU power and memory, so performance will not be an issue.

any thoughts?

Package.MaxConcurrentExecutables Property

Valid values are one and higher, or -1. Other values are invalid. A value of -1 allows the maximum number of concurrently running executables to equal the number of processors plus two. Setting this property to zero or any other negative value fails with an error code that indicates an invalid argument.

This property is used when parallelism exists in the workflow. If the workflow is a series of sequential precedence constraints, then this property has no effect.

I don't know if you can get more than CPU Count + 2 by forcing the value. If this is a 32-bit server then I would be concerned about memory, as despite having 10 GB in there, a process (read SSIS Package) can only use 2GB or 3GB with the/3GB boot.ini switch, so you may want to break out into multiple packages, or just call the same package multiple times. The Execute Package Task can be used to get multiple processes with the out of processes property, but this has a higher overhead for loading and starting the packages.

Asymmetry in "Execute SQL Task"?

Anyone know why the Execute SQL Task is only available under Control flow and not Data flow? (At times it's a useful data manipuation task and I wish was available as a Data flow task as well.)

Barkingdog

It could lead to some extremely inefficient data flows.

Imagine executing a SQL statement for every record in a 1,000,000 row transformation?

Or what if you decided to execute an update against the source data in your transformation?

The posiibilities for abuse are staggering...

If you need to run a SQL task doing it before or after a transformation allows you to execute SQL on the entire set... Not just a single record.

|||ssis provides the ole db command data flow transformation component for sql-based data manipulation: http://msdn2.microsoft.com/en-us/library/ms141138.aspx

assign an output of derive column to variable

I have a derive column( sequence) transformation in data flow , i am trying to assign this column to a variable , so that i can use it in the SQLtask control flow... how can i do this? can you show me some examples?

Why are you trying to assign a value from a derived column to a variable? The derived column works for every row passing through it. When passing a variable value back to the control flow, it would only contain the last value in the data flow.

With that said, you can use a script component's PostExecute sub to assign data to a variable.|||

Actually the derived column is in For each loop ( row by row process)...

"script component's PostExecute sub to assign data to a variable"

--> is there a code example that you can show me ?

Thank you for your help

|||

safddddddddddddddddddddd wrote:

Actually the derived column is in For each loop ( row by row process)...

No it isn't. You might have a data flow in the foreach loop.

The foreach loop can assign variables for each iteration. Why doesn't that work for you?|||

you are correct I meant I have a data flow which has the derive column transformation in the foreach loop..

I am tying to tryting to pass the result of derive column ( @.[User::Assessment_Dt] > assessment_dt ? 1 : 0 ) to a variable so that i can use it in SQLTASK ..

I understand i need to use a script task to assign the output of derive column in data flow to a variable ..but i don't know how? is there any examples that i can see?

|||What is the source for the foreach loop?

What is the source in the data flow?

I don't understand what you are trying to achieve, and I'd really like to help you architect this better.|||

The source of the foreach loop is

Select columns from S_enrollment_assessment

and i am not quite sure how to create the data source for the data flow in the for each loop since it it assign to row by row ?

|||

safddddddddddddddddddddd wrote:

The source of the foreach loop is

Select columns from S_enrollment_assessment

and i am not quite sure how to create the data source for the data flow in the for each loop since it it assign to row by row ?

So you're populating a data set with an execute SQL task and then using a foreach loop against that dataset?|||

yes.. and i assigned each columns to variables ( 71 of them) and follow the complex loding process..

for example,

FINDSTRING("1,2,3,4",@.TypeofAssessment,1) > 0 in precedence editor - expression

then go the SQLTASK1 otherwise go to SQLTASK2

and there are so many different logics that i need to follow...

so how to use 71 variables as the source of data flow in for each loop?

|||

safddddddddddddddddddddd wrote:

yes.. and i assigned each columns to variables ( 71 of them) and follow the complex loding process..

for example,

FINDSTRING("1,2,3,4",@.TypeofAssessment,1) > 0 in precedence editor - expression

then go the SQLTASK1 otherwise go to SQLTASK2

and there are so many different logics that i need to follow...

so how to use 71 variables as the source of data flow in for each loop?

You don't. You can write a SQL command in a variable expression that maps other variables (from your foreach loop) and then use the SQL variable as your OLE DB Source.

For instance:
@.TypeofAssessment - Source: Foreach loop
@.SQLCommand - Source: Expression: "select * from table where assessment = " + @.TypeofAssessment

OLE DB Source - Set to use sql command from variable - choose @.SQLCommand.|||yes. that is what i was trying to do...but since there are 71 columns that i am passing as variables,it's a kind of time consuming job.. thank you for all your help.. i really appreciate it..