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.

async_network_IO

I have a process that runs that keeps getting a wait type of async_network_IO
and I was hoping somebody on the board might be able to help out.
I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
data partition striped across 8 high-speed drives and 2 disks mirrored for
the system and logs (logs are on a separate logical partition)
I have a job that calls a local executable that essentially summarizes from
one table and inserts/updates another. This is the one that starts a process
that gets the async_network_IO wait type error. I changed the connection
string for the executable to use “Localhost” rather than the DNS name
(thinking that it might have to ask the DNS server what it’s name is) but the
process will still get the same error.
I’m confused why it would be getting this error when nothing should be
traversing the network? Has anybody seen something similar and if so how
were you able to solve the issue?
Thanks,
-Eric
The fact you see some wait type doesn't mean that you necessarily have a
problem with the resource of that wait type. You may want to take a closer
look at two things. First, find whether the wait for that resource type is
significant in terms of absolute numbers and in terms of percentage among all
the resource wait types. Second, try to correlate the waitstats with perfmon
counter values. It's possible that you may have some waits, but things are
not being piled up on disk I/Os, network I/Os, cpu, or memory, etc. Then, you
are probably okay.
Linchi
"ep" wrote:

> I have a process that runs that keeps getting a wait type of async_network_IO
> and I was hoping somebody on the board might be able to help out.
> I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
> data partition striped across 8 high-speed drives and 2 disks mirrored for
> the system and logs (logs are on a separate logical partition)
> I have a job that calls a local executable that essentially summarizes from
> one table and inserts/updates another. This is the one that starts a process
> that gets the async_network_IO wait type error. I changed the connection
> string for the executable to use “Localhost” rather than the DNS name
> (thinking that it might have to ask the DNS server what it’s name is) but the
> process will still get the same error.
> I’m confused why it would be getting this error when nothing should be
> traversing the network? Has anybody seen something similar and if so how
> were you able to solve the issue?
> Thanks,
> -Eric
>

async_network_IO

I have a process that runs that keeps getting a wait type of async_network_IO
and I was hoping somebody on the board might be able to help out.
I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
data partition striped across 8 high-speed drives and 2 disks mirrored for
the system and logs (logs are on a separate logical partition)
I have a job that calls a local executable that essentially summarizes from
one table and inserts/updates another. This is the one that starts a process
that gets the async_network_IO wait type error. I changed the connection
string for the executable to use â'Localhostâ' rather than the DNS name
(thinking that it might have to ask the DNS server what itâ's name is) but the
process will still get the same error.
Iâ'm confused why it would be getting this error when nothing should be
traversing the network? Has anybody seen something similar and if so how
were you able to solve the issue?
Thanks,
-EricThe fact you see some wait type doesn't mean that you necessarily have a
problem with the resource of that wait type. You may want to take a closer
look at two things. First, find whether the wait for that resource type is
significant in terms of absolute numbers and in terms of percentage among all
the resource wait types. Second, try to correlate the waitstats with perfmon
counter values. It's possible that you may have some waits, but things are
not being piled up on disk I/Os, network I/Os, cpu, or memory, etc. Then, you
are probably okay.
Linchi
"ep" wrote:
> I have a process that runs that keeps getting a wait type of async_network_IO
> and I was hoping somebody on the board might be able to help out.
> I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
> data partition striped across 8 high-speed drives and 2 disks mirrored for
> the system and logs (logs are on a separate logical partition)
> I have a job that calls a local executable that essentially summarizes from
> one table and inserts/updates another. This is the one that starts a process
> that gets the async_network_IO wait type error. I changed the connection
> string for the executable to use â'Localhostâ' rather than the DNS name
> (thinking that it might have to ask the DNS server what itâ's name is) but the
> process will still get the same error.
> Iâ'm confused why it would be getting this error when nothing should be
> traversing the network? Has anybody seen something similar and if so how
> were you able to solve the issue?
> Thanks,
> -Eric
>sql

async_network_IO

I have a process that runs that keeps getting a wait type of async_network_I
O
and I was hoping somebody on the board might be able to help out.
I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID 5
data partition striped across 8 high-speed drives and 2 disks mirrored for
the system and logs (logs are on a separate logical partition)
I have a job that calls a local executable that essentially summarizes from
one table and inserts/updates another. This is the one that starts a proces
s
that gets the async_network_IO wait type error. I changed the connection
string for the executable to use “Localhost” rather than the DNS name
(thinking that it might have to ask the DNS server what it’s name is) but
the
process will still get the same error.
I’m confused why it would be getting this error when nothing should be
traversing the network? Has anybody seen something similar and if so how
were you able to solve the issue?
Thanks,
-EricThe fact you see some wait type doesn't mean that you necessarily have a
problem with the resource of that wait type. You may want to take a closer
look at two things. First, find whether the wait for that resource type is
significant in terms of absolute numbers and in terms of percentage among al
l
the resource wait types. Second, try to correlate the waitstats with perfmon
counter values. It's possible that you may have some waits, but things are
not being piled up on disk I/Os, network I/Os, cpu, or memory, etc. Then, yo
u
are probably okay.
Linchi
"ep" wrote:

> I have a process that runs that keeps getting a wait type of async_network
_IO
> and I was hoping somebody on the board might be able to help out.
> I am running SQL2005 on a box with 4 dual core procs, 16GB or RAM, a RAID
5
> data partition striped across 8 high-speed drives and 2 disks mirrored for
> the system and logs (logs are on a separate logical partition)
> I have a job that calls a local executable that essentially summarizes fr
om
> one table and inserts/updates another. This is the one that starts a proc
ess
> that gets the async_network_IO wait type error. I changed the connection
> string for the executable to use “Localhost” rather than the DNS name
> (thinking that it might have to ask the DNS server what it’s name is) bu
t the
> process will still get the same error.
> I’m confused why it would be getting this error when nothing should be
> traversing the network? Has anybody seen something similar and if so how
> were you able to solve the issue?
> Thanks,
> -Eric
>

ASTrace Utility on x64 server

Hi,

I’m testing the new ASTrace service and when I’m running it on my own personal notebook it runs just

fine.

The problem starts when I try to run the service in a x64 environment. The service installs properly,

but when I try to start it, it fails.

Looking at the log file the service writes to, I noticed that the service looks for files on the path:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\

However, being an x64 server, all those files are located on:

C:\Program Files(x64)\Microsoft SQL Server\90\Tools\Binn\

Is there any way I can point the service to look for the files in the x64 folder?

Thanks,

Yaniv

I would report this issue here as they seem to be pretty responsive:

http://www.codeplex.com/MSFTASProdSamples/WorkItem/List.aspx

Note that Codeplex is having some downtime currently, but I'm sure it will be back up in the next few days.

|||

Which file(s) is it looking for?

If it is only the Microsoft.SqlServer.ConnectionInfo.dll file, then you could try copying this file from the (x64) folder into the same folder where you installed ASTrace. I'm not sure if this works for a service, but it often does for a win forms app (it depends a bit on how the reference has been made)

I would still ask on the connect site, but this might get you up and runinng in the short term.

|||

Thanks Darren,

The file is PFCLNT90.dll, which I have tried to copy, but it still complained about this file and “other dependency” files missing.

I figured I don’t want to start copying the entire directory...

Appreciate your help!

Assigning properties to variables

I have a package level Event which runs a stored procedure, to log the error. What I want to do is assign some of the properties of the erroring task to package level variables so that I can use them as parameters.

I can see how to assign properties from variables, but not the other way round.
Can anyone explain to me how to do this?

Many thanks

RudyHi,

i've done something like this with a Script Task in SSIS.

1. Create some Variables you need in the Variables Window
2. Create a Script Task and put you Readonly and Readwrite Varibales at the
Properties of the Script Task Editor.
3. Use the Design Scritp Button at this Task to define a Script.
4. My Script was designed to create dynamic SQL Statements:

Dim CreateTable, SelectTable, CreateIndex, CreateConstraint, DropConstraint As String

Dim Table As String = CStr(Dts.Variables("TSDES").Value)

Dim SB As New System.Text.StringBuilder(1024)

'Create Table

SB.Append("IF NOT EXISTS (SELECT * FROM sys.tables WHERE type ='U' and name = 'Dim" & CStr(Dts.Variables("TSDES").Value).Trim & "')" & vbNewLine)

SB.Append("BEGIN" & vbNewLine)

SB.Append(" BEGIN Transaction" & vbNewLine)

SB.Append(" CREATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value).Trim & vbNewLine)

SB.Append(" (" & vbNewLine)

SB.Append(" TDTYP numeric(38, 0) NOT NULL," & vbNewLine)

SB.Append(" TDIMX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TDESC nvarchar(30) NOT NULL," & vbNewLine)

SB.Append(" TPDIX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TEMNO numeric(38,0) NOT NULL" & vbNewLine)

SB.Append(" ) ON [PRIMARY]" & vbNewLine)

SB.Append(" COMMIT" & vbNewLine)

SB.Append("End" & vbNewLine)

SB.Append("ELSE" & vbNewLine)

SB.Append(" TRUNCATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value) & vbNewLine)

Dts.Variables("CREATETABLE").Value = SB.ToString

SB.Remove(0, SB.Length)
...

That's it.
I hope this could be helpfull for you.

Kind Regards
Andy L?wen

|||I am doing exactly this with a script task.

When you setup the script task, make sure you set the 'Read/Write Variables' property in the properties dialog, or the script code will fail.

Here is a sample of the script which should give you and idea how to set the value of a package level variable.

Public Sub Main()

Dim VarName As String = ""

Try

Dim varCurrent As Microsoft.SqlServer.Dts.Runtime.Variable

VarName = "User::ObjectName"

varCurrent = Dts.Variables.Item(VarName)

' Set the current value of the Variable
varCurrent.Value = "VTDW_PROD_CMS_AccountInstance"

VarName = "Completed"

Catch Ex As Exception

Dts.TaskResult = Dts.Results.Failure

Dts.Events.FireError(1, "Validate Variables", String.Format("Missing one of the following variables [User::Phase, User::ObjectName, User::ObjectType, User::StepName]. These Variables must be defined. Current Variable ='{0}'", VarName),Nothing, 0)

Return

End Try

' Found all variables. Let the Phase run

Dts.Events.FireInformation(0, "Valor DW DTS", String.Format("All step variables for Phase '{0}' and Step '{1}' have been set.", Phase, StepName), Nothing, 0, Nothing)

Dts.TaskResult = Dts.Results.Success

|||Andy

Can you explain me what your script task exactly does

I'm searching for a script that changes the sql-commands
Thx

Assigning properties to variables

I have a package level Event which runs a stored procedure, to log the error. What I want to do is assign some of the properties of the erroring task to package level variables so that I can use them as parameters.

I can see how to assign properties from variables, but not the other way round.
Can anyone explain to me how to do this?

Many thanks

RudyHi,

i've done something like this with a Script Task in SSIS.

1. Create some Variables you need in the Variables Window
2. Create a Script Task and put you Readonly and Readwrite Varibales at the
Properties of the Script Task Editor.
3. Use the Design Scritp Button at this Task to define a Script.
4. My Script was designed to create dynamic SQL Statements:

Dim CreateTable, SelectTable, CreateIndex, CreateConstraint, DropConstraint As String

Dim Table As String = CStr(Dts.Variables("TSDES").Value)

Dim SB As New System.Text.StringBuilder(1024)

'Create Table

SB.Append("IF NOT EXISTS (SELECT * FROM sys.tables WHERE type ='U' and name = 'Dim" & CStr(Dts.Variables("TSDES").Value).Trim & "')" & vbNewLine)

SB.Append("BEGIN" & vbNewLine)

SB.Append(" BEGIN Transaction" & vbNewLine)

SB.Append(" CREATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value).Trim & vbNewLine)

SB.Append(" (" & vbNewLine)

SB.Append(" TDTYP numeric(38, 0) NOT NULL," & vbNewLine)

SB.Append(" TDIMX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TDESC nvarchar(30) NOT NULL," & vbNewLine)

SB.Append(" TPDIX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TEMNO numeric(38,0) NOT NULL" & vbNewLine)

SB.Append(" ) ON [PRIMARY]" & vbNewLine)

SB.Append(" COMMIT" & vbNewLine)

SB.Append("End" & vbNewLine)

SB.Append("ELSE" & vbNewLine)

SB.Append(" TRUNCATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value) & vbNewLine)

Dts.Variables("CREATETABLE").Value = SB.ToString

SB.Remove(0, SB.Length)
...

That's it.
I hope this could be helpfull for you.

Kind Regards
Andy L?wen

|||I am doing exactly this with a script task.

When you setup the script task, make sure you set the 'Read/Write Variables' property in the properties dialog, or the script code will fail.

Here is a sample of the script which should give you and idea how to set the value of a package level variable.

Public Sub Main()

Dim VarName As String = ""

Try

Dim varCurrent As Microsoft.SqlServer.Dts.Runtime.Variable

VarName = "User::ObjectName"

varCurrent = Dts.Variables.Item(VarName)

' Set the current value of the Variable
varCurrent.Value = "VTDW_PROD_CMS_AccountInstance"

VarName = "Completed"

Catch Ex As Exception

Dts.TaskResult = Dts.Results.Failure

Dts.Events.FireError(1, "Validate Variables", String.Format("Missing one of the following variables [User::Phase, User::ObjectName, User::ObjectType, User::StepName]. These Variables must be defined. Current Variable ='{0}'", VarName),Nothing, 0)

Return

End Try

' Found all variables. Let the Phase run

Dts.Events.FireInformation(0, "Valor DW DTS", String.Format("All step variables for Phase '{0}' and Step '{1}' have been set.", Phase, StepName), Nothing, 0, Nothing)

Dts.TaskResult = Dts.Results.Success

|||Andy

Can you explain me what your script task exactly does

I'm searching for a script that changes the sql-commands
Thx

aspnet user can not access sql server

We have an ASP.net application that currently sits on a server that runs IIS and sql server. We are spliting IIS and SQL server into 2 seperate machines. I believe I have the connection string okay as I can see the entries in the security log on the sql server machine however it keeps saying aspnet user invalid user or invalid password. What gives with this. We put .net on the sql server however this just added the aspnet user to the machine. Do I need to create an aspnet user inside the sql server and give it control of the dbases as well? I am running this using the personal web server not IIS on a server. I have a project on my desktop and am using the "local" IIS or personal web server that comes with visual studio out of the box. What kills me is that when I put the project on a real IIS server that has sql server on the same machine I have no issues. However when I split the dbase and the IIS apart onto 2 servers I get this aspnet invalid user or invalid password.
HELPtry giving database permission to the aspnet user on the other machine (IIS). ex. IISMachine\ASPNET
cheers
Shane Sukul
|Bsc|Mcsd.Net|Mcsd|Mcad|
|||

In SQL Server you can use Windows Authentication or SQL Server Authentication. If in connection string you don't spec. which user and password to use it tries to use Windows Authentication and the user which ASP.NET process runs should have permissions in the SQL Machine.
I normally use SQL Server authentication, put in my web.config (with only permission to the ASP.NET process) the ConnectionString with user and password.
HH
NeuralC

|||

neuralc wrote:

In SQL Server you can use Windows Authentication or SQL Server Authentication. If in connection string you don't spec. which user and password to use it tries to use Windows Authentication and the user which ASP.NET process runs should have permissions in the SQL Machine.
I normally use SQL Server authentication, put in my web.config (with only permission to the ASP.NET process) the ConnectionString with user and password.
HH
NeuralC

Of course you wouldn't actually put that in clear text!
|||

None of this works. The application currently sits on my desktop which is running xp professional. The web server is local to my machine whatever ships with visual studio. The dbase is on a server that has the .net environment running on it. Why can I not see the dbase information when I have the correct connection string.

If I take the same application on my machine and go and hit a server that has IIS adn sql server installed on the same machine it works fine
What gives

|||Also have a look at your firewall settings on the server that hosts your database.

asp.net worker process runs out of memory when using a large dataset

Hi,

I'm running an application on a server which grabs data from a database table on another server using SqlConnection, SqlDataAdapter and DataSet.

The application then updates every row in that DataSet's DataTable and the updates are saved back using DataAdapter. The code is pretty much straightforward code that you would find on MSDN documentation for using DataSets. The table contains a little over a million rows.

When I run the application, I get an error saying the Server Application is not available. Upon looking into the application event log, I get this message.

aspnet_wp.exe was recycled because memory consumption exceeded the
306 MB (60 percent of available RAM)

How do I get round this? I thought DataSets were supposed to handle large datatables comfortably without having memory issues.

-Thanks

They can, just increase your recycle point. Although with a million rows, it's likely you grind that little box into the ground. Buy more RAM, or expect to wait forever for a response. Using a DataReader instead of a dataset is what will allow your little box to access large tables without consuming a ton of memory.

ASP.NET Membership tables dissappear when db is attached to a different sql server instanc

A SQL Server 2005 (developer edition) database contains both application
data tables and ASP.NET membership tables and stored procedures and runs
without error on computer A. When the database is detached, copied to
computer B, and re-attached, the ASP.NET membership tables and stored
procedures are gone. If you know the cause/cure for this issue, I would
appreciate your help.
Thanks,
KeithAre they gone, or just not visible? It could be a permissions issue
where you're connecting with elevated permissions on A and not on B.
--Mary
On Sat, 24 Feb 2007 22:22:06 -0800, "keith" <kbrickey@.dslextreme.com>
wrote:
>A SQL Server 2005 (developer edition) database contains both application
>data tables and ASP.NET membership tables and stored procedures and runs
>without error on computer A. When the database is detached, copied to
>computer B, and re-attached, the ASP.NET membership tables and stored
>procedures are gone. If you know the cause/cure for this issue, I would
>appreciate your help.
>Thanks,
>Keith
>

ASP.NET connection to SQL Server

I have a page that runs a stored procedure in SQL server. It works fine when I run the app through Visual Studio but when I build the site and try to run it from the page, it will not work. Any ideas?What's the meaning of "it will not work"? Is it a connection failure? Or incorrect result? Or Command Timeout, ect.?

asp.net 2.0 / RS update

Is there a reporting services for asp.net 2.0?
We recently converted our web app to .NET 2.0 and the reportserver runs as a
subweb.
Can the 2 reportserver subwebs run as 2.0 or is there an upgrade available
from Microsoft?
Thanks.RS 2000 is a 1.1 app. You can run both 1.1 and 2.0 on the same web server.
However, if you want everything using the 2.0 framework then upgrade to RS
2005 (requires the SQL Server 2005 license). When upgrading you can leave
the database as 2000 and just upgrade Reporting Services if you want to
(still need the license though). I found noticeable performance improvement
(probably because of being based on the 2.0 framework) when I upgraded.
Definitely snappier.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris" <cmill575@.hotmail.com> wrote in message
news:ehH1QuxFGHA.724@.TK2MSFTNGP10.phx.gbl...
> Is there a reporting services for asp.net 2.0?
> We recently converted our web app to .NET 2.0 and the reportserver runs as
> a subweb.
> Can the 2 reportserver subwebs run as 2.0 or is there an upgrade available
> from Microsoft?
> Thanks.
>|||Thanks for your response.
I'll probably just view reports within my webapp using the VS 2005 tools to
open the .rdl directly instead of using the reportserver/reportmanager
subwebs.
I never liked the subweb approach anyway.
Thanks again.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:OMnUl1yFGHA.2084@.TK2MSFTNGP09.phx.gbl...
> RS 2000 is a 1.1 app. You can run both 1.1 and 2.0 on the same web server.
> However, if you want everything using the 2.0 framework then upgrade to RS
> 2005 (requires the SQL Server 2005 license). When upgrading you can leave
> the database as 2000 and just upgrade Reporting Services if you want to
> (still need the license though). I found noticeable performance
> improvement (probably because of being based on the 2.0 framework) when I
> upgraded. Definitely snappier.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Chris" <cmill575@.hotmail.com> wrote in message
> news:ehH1QuxFGHA.724@.TK2MSFTNGP10.phx.gbl...
>> Is there a reporting services for asp.net 2.0?
>> We recently converted our web app to .NET 2.0 and the reportserver runs
>> as a subweb.
>> Can the 2 reportserver subwebs run as 2.0 or is there an upgrade
>> available from Microsoft?
>> Thanks.
>>
>|||Before you decide to abandon the server based product, do a test. It is not
as simple as you might think. Subreports get more complicated, jump to
report, etc. There is a lot you have to handle yourself.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chris" <cmill575@.hotmail.com> wrote in message
news:%23p3INy2FGHA.2084@.TK2MSFTNGP09.phx.gbl...
> Thanks for your response.
> I'll probably just view reports within my webapp using the VS 2005 tools
> to open the .rdl directly instead of using the reportserver/reportmanager
> subwebs.
> I never liked the subweb approach anyway.
> Thanks again.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:OMnUl1yFGHA.2084@.TK2MSFTNGP09.phx.gbl...
>> RS 2000 is a 1.1 app. You can run both 1.1 and 2.0 on the same web
>> server. However, if you want everything using the 2.0 framework then
>> upgrade to RS 2005 (requires the SQL Server 2005 license). When upgrading
>> you can leave the database as 2000 and just upgrade Reporting Services if
>> you want to (still need the license though). I found noticeable
>> performance improvement (probably because of being based on the 2.0
>> framework) when I upgraded. Definitely snappier.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Chris" <cmill575@.hotmail.com> wrote in message
>> news:ehH1QuxFGHA.724@.TK2MSFTNGP10.phx.gbl...
>> Is there a reporting services for asp.net 2.0?
>> We recently converted our web app to .NET 2.0 and the reportserver runs
>> as a subweb.
>> Can the 2 reportserver subwebs run as 2.0 or is there an upgrade
>> available from Microsoft?
>> Thanks.
>>
>>
>