A/Synchronous execution of SSIS ETL packages

Hi

I'd like to know if there's a way to control the execution of ETL packages, such that:
Different packages, or at least packages that don't access the same table or database run asynchronously with respect to each other; e.g., two different packages run at the same time
and
If a package is called for execution more than once by different requests, force them to run synchronously, or one after the other.If this is possible, what resources would it require? Is this possible under, say, a dual or quad processor machine?

Thanks.

Well, there is nothing to stop you running 2 packages concurrently (i.e. at the same time). They run as seperate processes so cannot interfere with each other in the process space. Of course, if they are competing for the same external connections then you may run into problems.

Not so sure about ensuring that executions of a package are queued up tho as you require. You may have to implement a process on top of your packages to check to see if a package is already executing before attempting to execute it.

I don't think the spec of the machine is the issue here but it may help to know what resources a package utilises: http://blogs.conchango.com/jamiethomson/archive/2005/05/29/1486.aspx

-Jamie

|||

I'm not sure about the big picture, and how these package are run, but here are two ideas to consider:

1) using master package with execute package tasks and appropriate precedence constraints

2) creating Agent Job for each package - the Agent runs maximum one instance of a job at a time, thus if you always go via Agent, you'll have at most one instance of same package running.

|||

You would need to maintain some kind of shared state in order for packages in seperate processes to 'know' if a singleton object was being consumed by eachother and prevent contention. You can typically use a semaphore to stagger execution of any Windows dependant processes; in the SSIS context you could use a semaphore from within a script task. Have a look in the .Net framework for synchronization primitives support.

|||MSMQ task does this smashingly. :)

A, B and C are the same but at least D or E are different (was "noob question")

hi,

just wanted to ask the most efficient way to query for the following:
say i have a table with the following columns A, B, C, D, E.
i want to find the records whose A, B and C are the same but at least D or E are different and then display all the columns (A to E) for these records.
thank you!

g11DBhi,

just wanted to ask the most efficient way to query for the following:
say i have a table with the following columns A, B, C, D, E.
i want to find the records whose A, B and C are the same but at least D or E are different and then display all the columns (A to E) for these records.
thank you!

g11DB

is this wat u wanted ?
Select A,B,C,D,E From Table1 Where Exists (Select 1 from Table1 Where (A=B and B=C) and (( A <>E) or (A <>D)))

Gurus, please comment|||I'm afraid your query won't work :o

Firstly, Exists isn't necessary. Secondly, if the condition is true for any of the rows it will display all the rows (the exists statement is not corrolated with the main query). Minor but SELECT * is recommended within an exists statement as the optimser will then chose the best index for the job (doubt it would make much difference here but I like to use it by habit).

Nearly there - a slight reworking:
Select A,B,C,D,E
From Table1
Where A=B and B=C and (A <> E or A <> D)|||Select A,B,C,D,E From Test
Where A=B
and A=C
and ((A=D or A=E)
or D<>E)

hope this works for u.|||Thanks for the suggessions|||Here's some NZDF code that will do what you've asked for:CREATE TABLE g11DB (
A INT
, B INT
, C INT
, D INT
, E INT
)

INSERT INTO g11DB (
A, B, C, D, E)
SELECT 1, 2, 3, 4, 5 UNION ALL
SELECT 1, 1, 1, 4, 5 UNION ALL
SELECT 1, 1, 1, 1, 5 UNION ALL
SELECT 1, 1, 1, 4, 1 UNION ALL
SELECT 1, 2, 1, 4, 1

SELECT *
FROM g11DB
WHERE 0 = A - B
AND A = C
AND A != D
UNION SELECT *
FROM g11DB
WHERE A = B
AND -A = -C
AND 0 != (A - E)-PatP|||Always the skeptic, Pat.|||Thanks guys but it seems i didn't properly state what I wanted... The code I was looking for is something like this (although i'm still wondering if there's some more efficient version...
SELECT IPA.*
FROM dbo.IRS_PAYMENT IPA INNER JOIN
dbo.IRS_PAYMENT IPB ON IPA.ip_employee_id = IPB.ip_employee_id
AND IPA.ip_paydate = IPB.ip_paydate
AND IPA.ip_pay_indicator = IPB.ip_pay_indicator
AND (NOT (IPA.ip_total_tax = IPB.ip_total_tax)
OR NOT (IPA.ip_net_pay = IPA.ip_net_pay))
ORDER BY IPA.ip_employee_id

what's NSDF stand for btw? :)|||How about:SELECT A.*
FROM dbo.IRS_PAYMENT AS A
INNER JOIN dbo.IRS_PAYMENT AS B
ON (B.ip_employee_id = A.ip_employee_id
AND B.ip_paydate = A.ip_paydate
AND B.ip_pay_indicator = A.ip_pay_indicator)
WHERE B.ip_total_tax != A.ip_total_tax
OR B.ip_net_pay != A.ip_net_pay
ORDER BY A.ip_employee_id, A.ip_paydate, A.ip_pay_indicator-PatP

a humdinger of a performance problem

Hi all,
Generally i'm helping to answer questions here, but i have strange problem
that I haven't seen before, or at least such a difference in performance of
a query.
Scenario:
I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
SP4.
I have a datawarehouse that currently contains tiny amounts of data, like
3000 rows in 1 table.
All 3 tables in use are 'perfectly' indexed and I have a simple view sitting
on top of them.
If i run a select query against the DB it returns me 3000 rows in under a
second... Now.. here's the killer:
I have EXACTLY the same DB installed on the DWH server which boasts 4GB RAM,
4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
The server is again 'perfectly' tuned with the exact same database ready for
rocking and rolling!
Now - the same query runs in 3 minutes!!!!!!!
If a perform execution plans and traces to my hearts content they ALWAYS
return the same output - but simply a difference in the time taken to return
the data.
I haven't come across a difference like this in 5 years of DBA'ing and am
grateful for any pointers.
A stupid question, would Dev version of SQL perform better indexing of views
(even though no indexed views are in play) than Standard edition which
obviously doesnt support Ind.Views!!'
Baffled! and TIA
ImmyImmy
Check out all SQL Server's settings on both machines
Add "noexpand" hint to the indexed view like
select sum(col) from IndexView with (noexpand)
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance
> of a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view
> sitting on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB
> RAM, 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready
> for rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>|||Why don't you run a perfmon on the server and keep a check on the buffer
cache, I/O, processesor and memory when the query is run and see which is
being used more? that info will help in address the issue faster.
"Immy" wrote:

> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance o
f
> a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view sitti
ng
> on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB RA
M,
> 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready f
or
> rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to retu
rn
> the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of vie
ws
> (even though no indexed views are in play) than Standard edition which
> obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>
>|||Hi Uri,
I mentioned that both my machines are setup exactly the same way. I
installed them both personally.
I don't have indexed views setup as my live server uses Standard Edition of
SQL Server.
Immy
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OsqQcelfGHA.4864@.TK2MSFTNGP05.phx.gbl...
> Immy
> Check out all SQL Server's settings on both machines
> Add "noexpand" hint to the indexed view like
> select sum(col) from IndexView with (noexpand)
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
>|||> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
I would expect different execution plans to explain such a big difference.
Take a close look at each plan node to ensure each is the same in both
plans.
Is there blocking or other processes running on the DWH server that might
explain the difference?

> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
Developer Edition has the same features as Enterprise Edition.
Consequently, DE will automatically consider view indexes. However, you
would see different execution plans if indexed views was the reason for the
difference in performance. DE/EE also has a merry-go-round scan feature
that can improve concurrent scan performance but that shouldn't be an issue
here.
Hope this helps.
Dan Guzman
SQL Server MVP
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance
> of a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view
> sitting on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB
> RAM, 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready
> for rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>|||Immy
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
Sorry I was referencing to the above statement somehow
Run DBCC FREEPROCCACHE on both machines and see what is going on
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%23c130jlfGHA.3996@.TK2MSFTNGP04.phx.gbl...
> Hi Uri,
> I mentioned that both my machines are setup exactly the same way. I
> installed them both personally.
> I don't have indexed views setup as my live server uses Standard Edition
> of SQL Server.
> Immy
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OsqQcelfGHA.4864@.TK2MSFTNGP05.phx.gbl...
>|||thanks for the responses guys and i've either already tried most of your
suggestions or tried the rest offered but getting nowehere very fast.
I'm currently on Japanese time zone rolling out a live system so there comes
a time where i need to re-group and take a look after some sleep!
I'm beginning to think my conclusion as well as Dans comments re: auto
indexing is the key here as i've swapped db's around from server to server
and always see the issue on the Standard version of sql.
I'll have to just try alternate methods of providing the data to the users;
however, feel free to post more suggestions that i can try in a few hours
time.
thanks guys
Immy
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
> Hi all,
> Generally i'm helping to answer questions here, but i have strange problem
> that I haven't seen before, or at least such a difference in performance
> of a query.
> Scenario:
> I have my laptop (terrible spec) that has WinXP SP2, SQL2000 DEV EDITION +
> SP4.
> I have a datawarehouse that currently contains tiny amounts of data, like
> 3000 rows in 1 table.
> All 3 tables in use are 'perfectly' indexed and I have a simple view
> sitting on top of them.
> If i run a select query against the DB it returns me 3000 rows in under a
> second... Now.. here's the killer:
> I have EXACTLY the same DB installed on the DWH server which boasts 4GB
> RAM, 4 3.08Ghz Processers and Win2003 and SQL 2000 STANDARD Edition.
> The server is again 'perfectly' tuned with the exact same database ready
> for rocking and rolling!
> Now - the same query runs in 3 minutes!!!!!!!
> If a perform execution plans and traces to my hearts content they ALWAYS
> return the same output - but simply a difference in the time taken to
> return the data.
> I haven't come across a difference like this in 5 years of DBA'ing and am
> grateful for any pointers.
> A stupid question, would Dev version of SQL perform better indexing of
> views (even though no indexed views are in play) than Standard edition
> which obviously doesnt support Ind.Views!!'
> Baffled! and TIA
> Immy
>|||How long has the database been set up on the production server?
Do any other queries take this long to run?
Is it possible that you have a bad processor / memory?
Or, maybe you have a corrupt index.
Lets figure out if one table is causing you the trouble. Take each table in
your query, and create a seperate query, joining the table to itself several
times, on the same columns used for the join in the original query. Run
each of these and see if one of them is slow. I would expect that one of
these would be very slow and the other two would be quite fast. Drop the
indexes on the one that is slow and recreate them.
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:%23Y8B0ylfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> thanks for the responses guys and i've either already tried most of your
> suggestions or tried the rest offered but getting nowehere very fast.
> I'm currently on Japanese time zone rolling out a live system so there
comes
> a time where i need to re-group and take a look after some sleep!
> I'm beginning to think my conclusion as well as Dans comments re: auto
> indexing is the key here as i've swapped db's around from server to server
> and always see the issue on the Standard version of sql.
> I'll have to just try alternate methods of providing the data to the
users;
> however, feel free to post more suggestions that i can try in a few hours
> time.
> thanks guys
> Immy
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:OWPs5alfGHA.2208@.TK2MSFTNGP05.phx.gbl...
problem
+
like
a
am
>|||Jim
the DB is new as of yesterday as is the installation and the query was
running fine during the first few hours of yesterday.
I have also broken down the queries and joined onto 1 table it's fine.
If i add 1 of any other 2 tables to the query it slows down dramatically and
i had also recreated the indexes a few times throughout the day.
Could be right about bad processor as 1 of the processors always peaks for
the duration of the run.
Once again, thanks for your responses. Good to know we're all singing from
the same hymn sheet and i'm sure i'll get to the end of it soon.
Immy
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23n4XommfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> How long has the database been set up on the production server?
> Do any other queries take this long to run?
> Is it possible that you have a bad processor / memory?
> Or, maybe you have a corrupt index.
> Lets figure out if one table is causing you the trouble. Take each table
> in
> your query, and create a seperate query, joining the table to itself
> several
> times, on the same columns used for the join in the original query. Run
> each of these and see if one of them is slow. I would expect that one of
> these would be very slow and the other two would be quite fast. Drop the
> indexes on the one that is slow and recreate them.
>
> "Immy" <therealasianbabe@.hotmail.com> wrote in message
> news:%23Y8B0ylfGHA.4304@.TK2MSFTNGP05.phx.gbl...
> comes
> users;
> problem
> +
> like
> a
> am
>|||How is the memory and IO (from perfmon) during the run? This really has my
curiosity peaked now. With only 3000 rows, I would expect very fast
response on anything better than a 386. With identical execution plans, I
am even more puzzled.
Just to confirm, all 3 tables have the same number of rows on both
databases?
Could you post the SQL query and the DDL, just to satisfy our curiosity?
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uzTPfDnfGHA.2068@.TK2MSFTNGP02.phx.gbl...
> Jim
> the DB is new as of yesterday as is the installation and the query was
> running fine during the first few hours of yesterday.
> I have also broken down the queries and joined onto 1 table it's fine.
> If i add 1 of any other 2 tables to the query it slows down dramatically
and
> i had also recreated the indexes a few times throughout the day.
> Could be right about bad processor as 1 of the processors always peaks for
> the duration of the run.
> Once again, thanks for your responses. Good to know we're all singing from
> the same hymn sheet and i'm sure i'll get to the end of it soon.
> Immy
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%23n4XommfGHA.2068@.TK2MSFTNGP02.phx.gbl...
table
of
the
your
hours
under
4GB
and
of
edition
>