run times of SSIS package not making sense....HELP!

We have an SSIS package that was created to migrate data in from a few production databases. The steps for the package are as follows...

backup databases on server 1 (prod database server) restore database to SSIS server (server 2) . truncate worker tables in SSIS server's (server 2) Main DB database. copy data from restored db tables to working db tables ( database to database) Start Multiple threads (15 ) and run steps from here in parrallel Combination of Data flow tasks and SQL scripts and Stored procedures used to flatten data out and combine data for reporting purposes.

The average run time is 8 hours.

the issue we are seeing is this, the package will fluctuate in run times from 4 hours to over 11 with no change in the data or the underlying SSIS package. We have looked for any changes or things that would effect this but have not found anything that changed...

Also, certain steps are running shorter while others double in time. there doesnt seem to be any rhyme or reason to this behaviour. The server is x64 12GB of RAM 2 dual core 3.2Ghz.

Please let me know if you need any more information or specifics...

the only thing I have seen so far that looks out of place is Tempdb has one of its files that is 20+GB.

Thanks,

Chris

Are you using "fast load" in your OLE DB Destination connections?|||

yes we are using Fast load...

we have narrowed the actual longest running spot right now... it has been running for over 10 hours....

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

|||Try asking this question in the Transact-SQL forum as it seems that's where your issue is. It doesn't look like this is an SSIS issue anymore.

http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1|||

ok I posted the question there as well..