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.

assigning each record to one string

hello,

i would like to loop through a record set and assign each value to the
same string, (example i would like to return all of the first name in
the authors table = Authors_total.)

should i use a cursor or just a loop to do this? I have had some
trouble with the syntax in a cursor.

nicholas.gadaczDear Nicholas,

I hope following will be help full for you.
---------------
Declare @.varstr as varchar(4000) -- declreation of
set @.varstr = ''; --initializing you know the fact Null + somhting =
Null
select @.varstr = @.varstr+','+isnull(ProductName,',') from Product;
Select @.varstr;
---------------

Best of Luck :) :) :)

Saghir Taj
MCDBA
www.dbnest.com: Home of DB Professionals.

ngadacz@.ftresearch.com wrote:
> hello,
> i would like to loop through a record set and assign each value to
the
> same string, (example i would like to return all of the first name in
> the authors table = Authors_total.)
> should i use a cursor or just a loop to do this? I have had some
> trouble with the syntax in a cursor.
> nicholas.gadacz|||Why not do that client-side? SQL isn't the best place for this kind of
presentational functionality.

--
David Portas
SQL Server MVP
--|||I am still not sure how i would loop through all of the records. if a
use a cursor i get an error variable assignment is not allowed in a
cursor declaration.

The reason why I don't put this functionality is the client side is
that I have multiple client sides: asp php and soon .aspx (.net) with
changes I want to have the code centralized.

nicholas.gadacz|||(ngadacz@.ftresearch.com) writes:
> I am still not sure how i would loop through all of the records. if a
> use a cursor i get an error variable assignment is not allowed in a
> cursor declaration.

DELARE @.str varchar(8000), @.col varchar(30)

DECLARE cur INSENSTIVE CURSOR FOR
SELECT col FROM tbl ORDER BY col
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO @.col
IF @.@.fetch_status <> 0
BREAK

SELECT @.str = CASE WHEN @.str IS NULL
THEN @.col
ELSE @.str + ',' + @.col
EMD
END
DEALLOCATE cur

This is one of the few things where you must use a cursor. Another poster
showed an example with a SELECT statement. However, that is not guaranteed
to work.

> The reason why I don't put this functionality is the client side is
> that I have multiple client sides: asp php and soon .aspx (.net) with
> changes I want to have the code centralized.

Beware that the above solution has a hard limit of the output string of
8000 characters.

In SQL2005 there will actually be a way to do this in a single statement,
by some fairly funny usage of the new XML stuff.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

AssignExpression In For Loop Container

What I'm trying to do is take multiple "snapshots" of a membership for many months. I was trying to do this in a For Loop Container in which I added one to the month variable and subtracted one from an iteration variable each loop, unless the month variable = 13...then I would add one to the year variable, set the month variable to 1 and subtract one from the iteration variable. This, I was hoping, would allow me to initialize 24 to the iteration variable, 1 to the month variable and 2004 to the year variable, kick off the package, and get 2 years worth of "snapshots" in seperate files (one for each month). Here is the AssignExpression I thought would work:

@.Member_Month == 13 ? @.Member_Month = 1 && @.Member_Year= @.Member_Year + 1 && @.Member_Iteration = @.Member_Iteration - 1 : @.Member_Month = @.Member_Month + 1 && @.Member_Iteration = @.Member_Iteration - 1

The package states that the single "=" should be double, but I don't think that that is what I want to do (I tried doing this just to check, and it didn't like this either). I think that it is having problems with doing multiple things on both sides of the ":". What am I doing wrong?

Thanks,

Brian Layden

The AssignExpression supports only a single assignment. Can you put the logic of updating Month and Year inside the loop in a script, perhaps?

Assign permissions to multiple objects

Yes
1. Simply add user to a group with enough perms to do it.
or
2. use sp_MSForeachtable or a cursor and loop through the objects assigning
perms as appriopriate by generating dynamic sql.
or
3. Script out perms from prod and apply to dev for login in question.
"rsbaier@.gmail.com" wrote:

> I know I should know the answer to this question, but unfortunately I
> do not.
> We have a SQL Server 7.0 production database (will be upgraded soon).
> I am attempting to restore this database in my development environment
> which is running SQL Server 2005 developer edition. The restore
> appeared to succeed without problem, but one of the logins ended up not
> getting any of the correct permissions assigned to any objects for some
> reason. Is there any way to easily assign permissions to multiple
> objects for a single login? If I have to do it manually and
> individually it will take hours.
> Thanks in advance for any advice.
>I know I should know the answer to this question, but unfortunately I
do not.
We have a SQL Server 7.0 production database (will be upgraded soon).
I am attempting to restore this database in my development environment
which is running SQL Server 2005 developer edition. The restore
appeared to succeed without problem, but one of the logins ended up not
getting any of the correct permissions assigned to any objects for some
reason. Is there any way to easily assign permissions to multiple
objects for a single login? If I have to do it manually and
individually it will take hours.
Thanks in advance for any advice.|||Yes
1. Simply add user to a group with enough perms to do it.
or
2. use sp_MSForeachtable or a cursor and loop through the objects assigning
perms as appriopriate by generating dynamic sql.
or
3. Script out perms from prod and apply to dev for login in question.
"rsbaier@.gmail.com" wrote:

> I know I should know the answer to this question, but unfortunately I
> do not.
> We have a SQL Server 7.0 production database (will be upgraded soon).
> I am attempting to restore this database in my development environment
> which is running SQL Server 2005 developer edition. The restore
> appeared to succeed without problem, but one of the logins ended up not
> getting any of the correct permissions assigned to any objects for some
> reason. Is there any way to easily assign permissions to multiple
> objects for a single login? If I have to do it manually and
> individually it will take hours.
> Thanks in advance for any advice.
>

Assign Null value to Variable

I have a column with int data type.. i am trying to assign this column to a variable ( int) in For Each Loop..but it keeps giving me an erro

The type of the value being assigned to variable "User:ubcontractor_Key" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Am i getting this error becasue the column has NULL value?. how can I resove this probelm?

Populate your source with 0s instead of NULLs in your source query, if applicable. (Or some arbitrary number)|||

can i do thsi in Expression builder using NULL function?

if so, can you show me some examples?

|||Anywhere in an expression builder, you can do:

ISNULL(ColumnOrVariable) ? 0 : ColumnOrVariable