High CPU Usage SSIS Package

Anyone aware of anything I can look at to improve performance of my SSIS packages? i basically have two master packages that call about 15 child package tasks each. All the package tasks are called sequentially and all they do is just load flat files into temporary tables and execute a simple stored proc. Each package tasks check to see if a file is there and if so it processes it else it just ends. The packages are scheduled to run every minute to check for files. If I watch the CPU during execution i can see the two instances of DTSEXEC.exe running and consuming about 25-30% of the CPU. The box that this runs on is a Quad processor with 4 GB of ram.

Any ideas on how to improve the performance of this? I have tried playing with the transaction level, but that didn't seem to do much performance wise.

Thanks for the help!

I thoroughly recommend you digest this SSSI Performance Whitepaper: http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx. Its a really interesting read and has some great tips on performance tuning.

Understanding how SSIS processes data is half the battle and this paper really helps towards this.

What performance problems are you having? As ever, performance tuning is all about identifying where your bottlenecks are.

-Jamie

|||

Yeah I browsed through that whitepaper and didn't see anything that triggered me for this situation.

Basically my setup is the following...

There is a directory that contains data that an AS/400 sends over via FTP. So for a customer change there is a file called customer.txt. Then there is an SSIS package that checks if that file exists, if so it processes it, if not it just ends (used a precendence constraint). There are probably 30 different files that get put into the FTP directory. So there are two master packages and each one calls about 15 child packages that do just as the package I described above. Most of the time there aren't very many files there so most of the packages fire up, see there isnt' a file there and end. Seems like the bottleneck is in the packages spinning up as once it gets going it seems like it goes pretty fast. I just don't understand how checking for a file can take up that much CPU. Are there other strategies for implementing the kind of architecture we have?

Thanks!

|||

OK, well you're right that there is an overhead to executing packages. Witness: http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1100.aspx

(But caveat those observations with what I've said here: http://blogs.conchango.com/jamiethomson/archive/2005/03/02/1107.aspx)

Basically, if you can reduce the number of packages that have to be executed that would be very beneficial. That would be my strategy anyway. Is there a reason that everything can't happen within 1 package?

Interested to know how you progress with this.

-Jamie

|||

Those are some interesting findings that you have there.

I could implement most of these in one package and skip the child packages. The reason for having them is for versioning and concurrent development. Now that we are pretty much done with developing these I could probably transform this into one package that doesn't use any child packages.

So any thoughts on the best way to implement this into one package then? Wondering if I can have a config file that has all the file names to check for and based on that perform the appropriate transformation which is nothing more than trimming off the spaces, loading it into a temp table and executing a stored proc that update the master table.

Seems kind of ridiculous that loading these child packages takes that much overhead, but I guess I can see that.

|||

Yeah, the overhead of spinning up the packages is only really a problem if its proportion of total package execution time is large. In other words, if you aint doing much, don't try and put everything into seperate packages.

I would recommend using sequence containers to logically seperate your processing in the control-flow. So, where you had a child package before, you now have a sequence container. These can of course run in parallel. And also, only execute those "stuff" for which a file exists - I'm sure you've already gone down that road already anyway.

-Jamie

|||Maybe what I shoudl try first is to put the check if the file exists in the master package and not in the child package. That way it will have to spin up a lot fewer packages.|||

Stark77 wrote:

Maybe what I shoudl try first is to put the check if the file exists in the master package and not in the child package. That way it will have to spin up a lot fewer packages.

Yep, another good idea. Although if you do that your child packages will be (correct me if I'm wrong) only loading up a file and executing a sproc on the data. Such a small amount of work doesn't really justify moving it out into another package.

-Jamie

|||

Ha...finally getting around to doing this. You have any best practices on doing something like this?

Basically here is what I am doing. We get about 30-40 different files in an ftp directory and when they arrive we need to process them. Each file has a different layout. Basically we grab the file, load it to a temp table that matches the file layout and then fire a stored procedure to process the records in the temp table. At the end we delete the file if it was processed successfully. We process the files in a specific order (orderheader, then orderdetail, shipmentheader, shipmentdetail, etc). The only other task we do is to trim all the string variables.

Basically I am wondering the best way to lay this out or if there is a best practice for processing something like this. I thought about looping through all the .txt files in the directory, but that won't process them in the order I want.

Thanks for the help!

|||

I use a table in a management db that contains the files. This way you have utmost control. In addition you can use it to log failures against the files, processing history etc.

You can improve the overhead time a package has by delaying validation on tasks/components. This way the validation only occurs when needed.

|||

Any chance you can script me out your table you use so I can brain storm off of it? Not really following what you are suggesting. How does the package flow out then? Do you have a for each that reads that table? Would be cool to send me over that table and a screenshot of the package or the package itself.

Thanks for the input. I am definitely going to throw an article together once I get this one done.

High CPU Usage SSIS Package

Anyone aware of anything I can look at to improve performance of my SSIS packages? i basically have two master packages that call about 15 child package tasks each. All the package tasks are called sequentially and all they do is just load flat files into temporary tables and execute a simple stored proc. Each package tasks check to see if a file is there and if so it processes it else it just ends. The packages are scheduled to run every minute to check for files. If I watch the CPU during execution i can see the two instances of DTSEXEC.exe running and consuming about 25-30% of the CPU. The box that this runs on is a Quad processor with 4 GB of ram.

Any ideas on how to improve the performance of this? I have tried playing with the transaction level, but that didn't seem to do much performance wise.

Thanks for the help!

I thoroughly recommend you digest this SSSI Performance Whitepaper: http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx. Its a really interesting read and has some great tips on performance tuning.

Understanding how SSIS processes data is half the battle and this paper really helps towards this.

What performance problems are you having? As ever, performance tuning is all about identifying where your bottlenecks are.

-Jamie

|||

Yeah I browsed through that whitepaper and didn't see anything that triggered me for this situation.

Basically my setup is the following...

There is a directory that contains data that an AS/400 sends over via FTP. So for a customer change there is a file called customer.txt. Then there is an SSIS package that checks if that file exists, if so it processes it, if not it just ends (used a precendence constraint). There are probably 30 different files that get put into the FTP directory. So there are two master packages and each one calls about 15 child packages that do just as the package I described above. Most of the time there aren't very many files there so most of the packages fire up, see there isnt' a file there and end. Seems like the bottleneck is in the packages spinning up as once it gets going it seems like it goes pretty fast. I just don't understand how checking for a file can take up that much CPU. Are there other strategies for implementing the kind of architecture we have?

Thanks!

|||

OK, well you're right that there is an overhead to executing packages. Witness: http://blogs.conchango.com/jamiethomson/archive/2005/03/01/1100.aspx

(But caveat those observations with what I've said here: http://blogs.conchango.com/jamiethomson/archive/2005/03/02/1107.aspx)

Basically, if you can reduce the number of packages that have to be executed that would be very beneficial. That would be my strategy anyway. Is there a reason that everything can't happen within 1 package?

Interested to know how you progress with this.

-Jamie

|||

Those are some interesting findings that you have there.

I could implement most of these in one package and skip the child packages. The reason for having them is for versioning and concurrent development. Now that we are pretty much done with developing these I could probably transform this into one package that doesn't use any child packages.

So any thoughts on the best way to implement this into one package then? Wondering if I can have a config file that has all the file names to check for and based on that perform the appropriate transformation which is nothing more than trimming off the spaces, loading it into a temp table and executing a stored proc that update the master table.

Seems kind of ridiculous that loading these child packages takes that much overhead, but I guess I can see that.

|||

Yeah, the overhead of spinning up the packages is only really a problem if its proportion of total package execution time is large. In other words, if you aint doing much, don't try and put everything into seperate packages.

I would recommend using sequence containers to logically seperate your processing in the control-flow. So, where you had a child package before, you now have a sequence container. These can of course run in parallel. And also, only execute those "stuff" for which a file exists - I'm sure you've already gone down that road already anyway.

-Jamie

|||Maybe what I shoudl try first is to put the check if the file exists in the master package and not in the child package. That way it will have to spin up a lot fewer packages.|||

Stark77 wrote:

Maybe what I shoudl try first is to put the check if the file exists in the master package and not in the child package. That way it will have to spin up a lot fewer packages.

Yep, another good idea. Although if you do that your child packages will be (correct me if I'm wrong) only loading up a file and executing a sproc on the data. Such a small amount of work doesn't really justify moving it out into another package.

-Jamie

|||

Ha...finally getting around to doing this. You have any best practices on doing something like this?

Basically here is what I am doing. We get about 30-40 different files in an ftp directory and when they arrive we need to process them. Each file has a different layout. Basically we grab the file, load it to a temp table that matches the file layout and then fire a stored procedure to process the records in the temp table. At the end we delete the file if it was processed successfully. We process the files in a specific order (orderheader, then orderdetail, shipmentheader, shipmentdetail, etc). The only other task we do is to trim all the string variables.

Basically I am wondering the best way to lay this out or if there is a best practice for processing something like this. I thought about looping through all the .txt files in the directory, but that won't process them in the order I want.

Thanks for the help!

|||

I use a table in a management db that contains the files. This way you have utmost control. In addition you can use it to log failures against the files, processing history etc.

You can improve the overhead time a package has by delaying validation on tasks/components. This way the validation only occurs when needed.

|||

Any chance you can script me out your table you use so I can brain storm off of it? Not really following what you are suggesting. How does the package flow out then? Do you have a for each that reads that table? Would be cool to send me over that table and a screenshot of the package or the package itself.

Thanks for the input. I am definitely going to throw an article together once I get this one done.

Hierarchically arranged attributes...mission impossible?

Struggling over the design of a database. Basically, we have a long
list of attributes. We've decided to arrange them hierarchically so
users can find them more easily. The problem is, this doesn't seem very
easy to implement! Let me try to explain what I'm talking about a
little more specifically.
I have a list of attributes organized hierarchically. For example:
CarAttributes
--Configuration
--Doors
--EngineLocation
--DriveWheels
--Engine
--Type
--Cylinders
--Arrangement
--Specs
--Size
--Power
--Torque
--Transmission
--Type
--Gears
I also have a lot of attribute data (for leaf attributes only). For
example:
CarID = 1, Doors = 4
CarID = 1, EngineLocation = 'Front'
CarID = 1, DriveWheels = 'Front'
CarID = 1, Cylinders = 6
CarID = 1, Arrangement = 'Inline'
CarID = 1, Size = '3.0'
...
CarID = 2, Doors = 2
CarID = 2, EngineLocation = 'Mid'
Etc.
QUESTION: How do I set up a proper relational database to support this
scheme?
I was thinking I could set up an adjacency table for the attributes.
The conundrum is how to store the attribute data.
Couple possibilities:
===+===+===+===+===+===+===+===+===
Idea #1 - I could set up my CarData table like this:
Table: CarData
CarID int
AttributeID int
AttributeValue nvarchar(100)
But then my attribute values, whether they are numeric, category, or
string, are all getting stored as a string. That seems like a big
divantage.
===+===+===+===+===+===+===+===+===
Idea #2 - Alternatively, I could set up my CarData table like this:
Table: CarData
CarID int
Doors int
EngineLocation EngineLocationID (references EngineLocation table)
DriveWheels DriveWheelsID
Cylinders int
EngineArrangement EngineArrangementID (references EngineArrangement
table)
EngineSize float
EnginePower int
EngineTorque int
TransmissionType TransmissionTypeID (reference TransmissionType
table)
TransmissionGears int
Everything is now explicitly typed but there's no apparent way to set
up a link between an attribute in the CarAttributes tree and its
corresponding column in the CarData table. For example, say the user is
looking at data on CarID = 1 and clicks on the "Engine/Type/Cylinders"
leaf. Now I need a select statement that looks something like this...
SELECT Cylinders FROM CarData WHERE CarID = 1
...but the only way I can think to get the word "Cylinders" into that
query would be through dynamic code or some hideously ugly CASE
statment, which also seems like a big divantage.
===+===+===+===+===+===+===+===+===
So, is there a "right" way to do this so I can have both properly typed
attribute values *and* be able to reference attribute values without
resorting to dynamic queries?
Thanks!
-DanHave you gotten a copy of TREES & HIERARCHIES IN SQL for various
approaches to this kind of problem?|||LOL, there is a book about his problem. Daniel, you are lucky!
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141348474.658630.45940@.v46g2000cwv.googlegroups.com...
> Have you gotten a copy of TREES & HIERARCHIES IN SQL for various
> approaches to this kind of problem?
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141348474.658630.45940@.v46g2000cwv.googlegroups.com...
> Have you gotten a copy of TREES & HIERARCHIES IN SQL for various
> approaches to this kind of problem?
>
Look at the problem: it's not a hierarchy or tree problem:
CarAttributes
--Configuration
--Doors
--EngineLocation
--DriveWheels
--Engine
--Type
--Cylinders
--Arrangement
--Specs
--Size
--Power
--Torque
--Transmission
--Type
--Gears
These are all attributes of a single entity: the car.
Perhaps an Engine is shared among multiple Cars and should be factored into
a seperate table. But other than that this is just a simple table:
create table CAR
(
NAME VARCHAR(50) PRMARY KEY,
DOORS INT CHECK (DOORS < 6),
ENGINE_LOCATION CHAR(1) CHECK (ENGINE_LOCATION IN ('F','B')),
DRIVE_WHEELS CHAR(1) CHECK (ENGINE_LOCATION IN ('F','B')),
ENGINE_CYLINDERS INT CHECK (ENGINE_CYNINDERS < 12),
...
dAVID|||Actually, Joe, I haven't, but I did read this...
http://blogs.msdn.com/anthonybloesc...r />
2005.aspx
...which seems to suggest that a good ol' adjacency table is just fine.
Besides being simple, it apparently runs a lot faster than the "Nested
Sets" technique.
My question, however, is not how to implement/traverse a hierarchy.
It's more about how to store the actual attribute data. If I get to a
particular leaf of the hierarchy, how do I query that particular column
in the table?
Be great to get your take on this...I hope you'll give my post a read
sometime.
Thanks,
-Dan|||Yes, David, they are all attributes of a car, but the attributes
themselves are arranged in a hierarchy. The table you are proposing
(which is basically like my Idea #2) cannot be linked to the hierarchy.
At least, not as far as I can tell.
See what I'm getting at?
Thanks,
-Dan|||"Daniel Manes" <danthman@.cox.net> wrote in message
news:1141367733.531758.185340@.e56g2000cwe.googlegroups.com...
> Yes, David, they are all attributes of a car, but the attributes
> themselves are arranged in a hierarchy. The table you are proposing
> (which is basically like my Idea #2) cannot be linked to the hierarchy.
> At least, not as far as I can tell.
> See what I'm getting at?
>
I think so. If so the relational model just doesn't quite get you there.
The relational model doesn't have support for the notion of attribute
groupings on an entity. You can get a bit of that by factoring the entity
into smaller, related entities, but that's just a hack.
You will have to supply and store that additional metadata yourself.
I would probably use a single flat table, and also provide an XSD schema
which mapped to the table and provided the hierarchicial relationships among
the attributes.
Or adopt a naming convention for your columns which embeds the attribute
relationships
eg
SELECT ENGINE_TYPE_CYLINDERS FROM CarData WHERE CarID = 1
David|||Hi David,
Knowing that I'm not missing some really obvious relational concept is
actually a relief. I think I'm going to go this route:
1. Put the attribute data in one flat table (e.g., Car).
2. Put the attribute names (which will correspond to the column names
in the Car table) and hierarchy information in an adjacency table
(e.g., CarAttribute).
If my app needs data on a particular car, I'll just write a query to
grab the whole row (all the attributes) and another query to grab the
particular column names that should be displayed. Then I'll write code
in my .NET app to handle the rest.
Thanks for the help,
-Dan|||>> Look at the problem: it's not a hierarchy or tree problem: <<
Yes, it is. It is called a parts explosion or Bill of Materials. Get
the book and use teh code for this. I think that you might be thinking
in OO terms and not RDBMS.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1141531629.790826.299470@.p10g2000cwp.googlegroups.com...
> Yes, it is. It is called a parts explosion or Bill of Materials. Get
> the book and use teh code for this. I think that you might be thinking
> in OO terms and not RDBMS.
>
The example chosen (Car) might be mistaken for a Bill of Materials problem.
But I think it's a typical entity model, but with the the entity attributes
"grouped" for the UI.
In a Bill of Materials problem an entity is composed of other entities, each
of which is composed of other entities, etc. Moreover all the entities
belong to a single domain (eg Parts). Here there's only one entity and a
fixed list of attributes. The problem concerns how to group related
attributes.
<quote>
Basically, we have a long list of attributes. We've decided to arrange them
hierarchically so
users can find them more easily.
</quote>
Some entities have a lot of attributes. Sometimes there are logical
groupings of attributes. For instance a Person may have Height, Weight,
Age, Income, NumberOfPets. Height, Weight and Age are all demographic
atributes, and it would sometimes be convenient to group them together.
They shouldn't be extracted into a seperate PersonDemographicData table, but
short of that the relational model doesn't help.
David

hiding parameters

I'm using the included ReportViewer sample (basically it just just loads the
reporting services report page in an ifram), and I'd like to set report
parameters for the report by my ASP.NET application which houses the report.
Is there any way to have my app set the parameter, without prompting the
user?
--JasonI added a helper method to the ReportViewer:
public void SetQueryParameter(
string ParameterName,
string ParameterValue)
{
SetParameter(ParameterName,ParameterValue);
}
To set a parameter value then is as easy as:
rv.SetQueryParameter("SalesOrderNumber", "SO6003");
HTH,
--
Scott
http://www.OdeToCode.com/blogs/scott/
On Thu, 21 Oct 2004 10:55:24 -0400, "Jason W. Nadal"
<jnadal@.comcast.net> wrote:
>I'm using the included ReportViewer sample (basically it just just loads the
>reporting services report page in an ifram), and I'd like to set report
>parameters for the report by my ASP.NET application which houses the report.
>Is there any way to have my app set the parameter, without prompting the
>user?
>--Jason
>|||Scott,
Thanks a ton -- I wasn't sure if that method would just be used for the
parameters for the reportservices UI or for the reports. That helped a
great deal!
--Jason
"Scott Allen" <bitmask@.[nospam].fred.net> wrote in message
news:qmlfn050dor01d5ailc98qvlejmn2tl0rh@.4ax.com...
> I added a helper method to the ReportViewer:
> public void SetQueryParameter(
> string ParameterName,
> string ParameterValue)
> {
> SetParameter(ParameterName,ParameterValue);
> }
>
> To set a parameter value then is as easy as:
> rv.SetQueryParameter("SalesOrderNumber", "SO6003");
> HTH,
> --
> Scott
> http://www.OdeToCode.com/blogs/scott/
> On Thu, 21 Oct 2004 10:55:24 -0400, "Jason W. Nadal"
> <jnadal@.comcast.net> wrote:
> >I'm using the included ReportViewer sample (basically it just just loads
the
> >reporting services report page in an ifram), and I'd like to set report
> >parameters for the report by my ASP.NET application which houses the
report.
> >Is there any way to have my app set the parameter, without prompting the
> >user?
> >
> >--Jason
> >
>|||What name space is the "SetParameter" method a member of?
I get a compile error when I tried to copy and paste as is.
Thanks!!
Spo
"Scott Allen" wrote:
> I added a helper method to the ReportViewer:
> public void SetQueryParameter(
> string ParameterName,
> string ParameterValue)
> {
> SetParameter(ParameterName,ParameterValue);
> }
>
> To set a parameter value then is as easy as:
> rv.SetQueryParameter("SalesOrderNumber", "SO6003");
> HTH,
> --
> Scott
> http://www.OdeToCode.com/blogs/scott/
> On Thu, 21 Oct 2004 10:55:24 -0400, "Jason W. Nadal"
> <jnadal@.comcast.net> wrote:
> >I'm using the included ReportViewer sample (basically it just just loads the
> >reporting services report page in an ifram), and I'd like to set report
> >parameters for the report by my ASP.NET application which houses the report.
> >Is there any way to have my app set the parameter, without prompting the
> >user?
> >
> >--Jason
> >
>|||NEVER MIND.
I read this too quick the 1st time, i now understand what your doing.
Sorry and Thanks!
"iamspo" wrote:
> What name space is the "SetParameter" method a member of?
> I get a compile error when I tried to copy and paste as is.
> Thanks!!
> Spo
> "Scott Allen" wrote:
> > I added a helper method to the ReportViewer:
> >
> > public void SetQueryParameter(
> > string ParameterName,
> > string ParameterValue)
> > {
> > SetParameter(ParameterName,ParameterValue);
> > }
> >
> >
> > To set a parameter value then is as easy as:
> >
> > rv.SetQueryParameter("SalesOrderNumber", "SO6003");
> >
> > HTH,
> >
> > --
> > Scott
> > http://www.OdeToCode.com/blogs/scott/
> >
> > On Thu, 21 Oct 2004 10:55:24 -0400, "Jason W. Nadal"
> > <jnadal@.comcast.net> wrote:
> >
> > >I'm using the included ReportViewer sample (basically it just just loads the
> > >reporting services report page in an ifram), and I'd like to set report
> > >parameters for the report by my ASP.NET application which houses the report.
> > >Is there any way to have my app set the parameter, without prompting the
> > >user?
> > >
> > >--Jason
> > >
> >
> >