| Author |
 Topic  |
|
|
sandesh.ravi
Posting Yak Master
United Kingdom
110 Posts |
 Posted - 07/18/2012 : 09:00:05
|
Hi,
I have an SSIS package that loads some tables from a source DB to destination DB. Whenever the package is executed it gets hanged and blocks by itself. After the process is killed and then it is executed again then it runs successfully.
I just wanted to know is there any configuration to be set up?
Thanks,
Sandesh |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/18/2012 : 10:02:48
|
Could be a number of things.
Maybe the connection isn't being set up properly - second time things are in memory and dependencies get completed before they are used.
Maybe the first run gets things into memory on the source system and doesn't have unhandled timeout issues the second time.
I would start by splitting the package up into one table per package and run them in sequence - and don't do anything else in the packages.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/18/2012 : 10:37:48
|
did you have a look at db to see what causes blocking? is package populating simlutaneously or is it in sequence? does it have some partiatlly/fully blocking transforms like sort etc?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sandesh.ravi
Posting Yak Master
United Kingdom
110 Posts |
Posted - 07/20/2012 : 05:12:02
|
Before loading the tables, the data older than 3 months is deleted in the destination table and then the table is loaded. The delete statement is executing for ever, and then when it is killed for the first time it takes almost an hour to rollback and then when the package is run for the second time it runs successfully for some unknown reason.
This package is run every month and always this same issue is happening. I am puzzled....!!!
Thanks,
Sandesh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/20/2012 : 10:40:36
|
quote:
Originally posted by sandesh.ravi
Before loading the tables, the data older than 3 months is deleted in the destination table and then the table is loaded. The delete statement is executing for ever, and then when it is killed for the first time it takes almost an hour to rollback and then when the package is run for the second time it runs successfully for some unknown reason.
This package is run every month and always this same issue is happening. I am puzzled....!!!
Thanks,
Sandesh
what all indexes you've on the table?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sandesh.ravi
Posting Yak Master
United Kingdom
110 Posts |
Posted - 07/23/2012 : 03:51:23
|
The table has a primary key ID (identity column) and 5 indexes. The where condition on delete statement has a non indexed column(datetime).
Thanks,
Sandesh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/23/2012 : 10:47:36
|
did you check execution plan and see costly steps?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sandesh.ravi
Posting Yak Master
United Kingdom
110 Posts |
Posted - 07/25/2012 : 08:50:22
|
Cluster Index scan (100% cost)
Thanks,
Sandesh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/25/2012 : 11:17:17
|
quote:
Originally posted by sandesh.ravi
Cluster Index scan (100% cost)
Thanks,
Sandesh
try putting a nonclustered index on date column and see if it helps. Are you capturing timepart as well along with date?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sandesh.ravi
Posting Yak Master
United Kingdom
110 Posts |
Posted - 07/27/2012 : 03:28:08
|
We are capturing timepart as well.
Thanks,
Sandesh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47069 Posts |
Posted - 07/27/2012 : 10:32:20
|
then doesnt have much help there.
can you check while package is running whether any mutual blocking happens
use sp_who2 for identifying that
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
SSIS package getting hanged when it is executed
