Need TSQL HELP! long running TSQL task in SSIS.....

I was referred to this forum as this SSIS task is TSQL in nature

We have an SSIS package that seems to run long and then short then long and is random in nature in these times....8 hours is the average but it has been known to run for 4 hours and it currently running 10+ hours...

the data size hasnt changed much other than a few 1000 records more than the previous iteration.

here is what we are running right now as a step in the package and it has been running for over 10 hours so far.

I had to change some of the names of Tables etc... but here is the rought outline of what the SQL task does:

truncate table [Reporting].[dbo].[ReportTable]
INSERT INTO [Reporting].[dbo].[ReportTable] (pid)
SELECT DISTINCT [MainAPPDB].[dbo].[ReportTableHistory].[Client_ID]
FROM [MainAPPDB].[dbo].[ReportTableHistory]
LEFT OUTER JOIN [MainAPPDB].[dbo].[ReportTableStatus] ON [MainAPPDB].[dbo].[ReportTableStatus].[ReportTableStatusID] = [MainAPPDB].[dbo].[ReportTableHistory].[ReportTableStatusID]
LEFT OUTER JOIN [SecondAPPdb].[dbo].[tblBrokerContact] ON [SecondAPPdb].[dbo].[tblBrokerContact].[BrokerContact_ID] = [MainAPPDB].[dbo].[ReportTableHistory].[LastUpdatedBy]

UPDATE [Reporting].[dbo].[ReportTable] SET rdata = X.rAsXML
FROM
(
SELECT
C.pid as 'primid',
(SELECT [MainAPPDB].[dbo].[ReportTableStatus].[Description] --varchar(25)
,CONVERT(CHAR(30),[MainAPPDB].[dbo].[ReportTableHistory].[LastUpdatedOn],100) AS [LastUpdatedOn] --varchar(30)
,[SecondAPPdb].[dbo].[tblContact].[Contact_FName] + ' ' + [SecondAPPdb].[dbo].[tblContact].[Contact_LName] AS [LastUpdatedBy] --varchar(50)
,[MainAPPDB].[dbo].[ReportTableHistory].[Reason] --varchar(300)
FROM [MainAPPDB].[dbo].[ReportTableHistory]
LEFT OUTER JOIN [MainAPPDB].[dbo].[ReportTableStatus] ON [MainAPPDB].[dbo].[ReportTableStatus].[ReportTableStatusID] = [MainAPPDB].[dbo].[ReportTableHistory].[ReportTableStatusID]
LEFT OUTER JOIN [SecondAPPdb].[dbo].[tblContact] ON [SecondAPPdb].[dbo].[tblContact].[Contact_ID] = [MainAPPDB].[dbo].[ReportTableHistory].[LastUpdatedBy]
WHERE [C_ID] = C.pid FOR XML RAW ('ReportTableHistory'), ROOT('ReportTableStatusHistories'), ELEMENTS XSINIL) as rAsXML
FROM [Reporting].[dbo].[ReportTable] C) X
WHERE X.primid = pid

DECLARE @.inxml XML
DECLARE @.res XML
DECLARE @.pid INT

DECLARE cur CURSOR fast_forward FOR
SELECT pid,rdata FROM [Reporting].[dbo].[ReportTable]
OPEN cur
FETCH next FROM cur INTO @.pid, @.inxml
WHILE @.@.fetch_status = 0
BEGIN
EXEC ExternFunctions_FormatReportTableHistory @.inxml, @.res OUT
UPDATE [Reporting].[dbo].[Client] SET ReportTableHistory = @.res
WHERE [Reporting].[dbo].[Client].[Client_ID] = @.pid
FETCH next FROM cur INTO @.pid,@.inxml
END
CLOSE cur
DEALLOCATE cur

It may help to put in some timestamp statements that you can study by letting the SQL output to "Results to Text."

declare @.MyMin varchar(3)

declare @.MySec varchar(3)

declare @.MyMS varchar(4)

set @.MyMin = right(('00' + cast(datepart(mi, getdate()) as varchar(3))), 2)

set @.MySec = right(('00' + cast(datepart(ss, getdate()) as varchar(3))), 2)

set @.MyMS = right(('000' + cast(datepart(ms, getdate()) as varchar(4))), 3)

print 'Beginning step xxxx' + ' ' + @.MyMin + ':' + @.MySec + '.' + @.MyMS

How many rows are in [Reporting].[dbo].[ReportTable]?

How time-consuming is the EXEC inside your CURSOR loop:

EXEC ExternFunctions_FormatReportTableHistory @.inxml, @.res OUT

?

(Maybe you want some timing statements within that loop.)