March 20, 2013 at 7:48 am
We have a SQL Job that calls an SSIS package. This package used to take about 10 minutes to run. A while back it started to take 1.5 hours and still does. We are able to narrow it down to which task in the control flow is taking the longest.
I'm very new to SSIS, but I have been asked to look in to it. So I guess I am wondering if anyone knows where I would start? I would like to some how monitor what goes on but the job runs at 3:30AM so I never get to see it till it's done running. Can I create an audit to record events for me to look at later? Is there a way to tell if tables are blocking/locking one another after the job has already ran? I can run the step again but it runs very quickly by itself so it's not that helpful.
Below is the step that takes a long time, it's just a execute sql task with a query.
--CREATE TABLE #FullList(
DECLARE @FullList table (
StoreProductId INT,
StoreId INT,
ProductId INT,
NewTaxRate NUMERIC (7,6),
BottleDeposit MONEY)
--Insert values into #fullList
INSERT INTO @FullList(StoreProductId, StoreId, ProductId , NewTaxRate , BottleDeposit)
SELECT
tblTaxLoad.StoreProductId,
tblTaxLoad.fkStoreId,
tblTaxLoad.fkProductId,
tblTaxLoad.NewTaxRate,
tblTaxLoad.BottleDeposit
FROM dbo.tblTaxLoad WITH (NOLOCK)
ORDER BY tblTaxLoad.StoreProductId
-----------------------------------------------------------------------------------------
--Update Taxes / Deposits 100 at a time
declare @myIteration int
declare @myCounter int
set @myIteration = 0
set @myCounter = 0
---------------------------------------------------------
-- Take 100 items at a time & put them in temp table #SubList
--
WHILE ( SELECT COUNT(*) FROM @FullList ) > 0
BEGIN
SET @myCounter = ( SELECT COUNT(*) FROM @FullList )
---------------------------------------
-- Get next 100 items
--
SELECT TOP 100 StoreProductId, StoreId, ProductId , NewTaxRate , BottleDeposit
INTO #SubList
FROM @FullList
ORDER BY StoreProductId
---------------------------------------
-- Update these items
--
-- begin tran
UPDATE tblStore_Product
SET InStoreSalesTaxRate = NewTaxRate,
Deposit = BottleDeposit
--select *
FROM #SubList SubList
INNER JOIN dbo.tblStore_Product WITH (NOLOCK)
ON SubList.StoreProductId = tblStore_Product.[Id]
--commit
-- rollback
---------------------------------------
-- Report to screen
--
set @myIteration = @myIteration + 1
print 'Iteration ' + cast(@myIteration as varchar) + ': ' + cast(@myCounter as varchar) + ' left'
---------------------------------------
-- Remove updated from #FullList table & LoadTable
--
DELETE FullList FROM @FullList FullList
INNER JOIN #SubList
ON FullList.StoreProductId = #SubList.StoreProductId
DELETE tblTaxLoad FROM dbo.tblTaxLoad
INNER JOIN #SubList
ON tblTaxLoad.StoreProductId = #SubList.StoreProductId
---------------------------------------
-- Drop temp table (to be remade again)
--
DROP TABLE #SubList
WAITFOR DELAY '00:00:00.200'-- .2 second between each iteration
END
---------------------------------------
-- Drop temp table -- @FullList will simply go out of scope
--DROP TABLE #FullList
March 20, 2013 at 3:52 pm
Which version of SSIS are you working with?
The first step is to enable logging and the appropriate events to see what is going on when the package runs.
Integration Services (SSIS) Logging
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 28, 2013 at 8:39 am
Bids 2008 is the version. I am looking at that link now, this information is all new to me so it will certainly be challenging to implement.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply