Hi,
Got another question. I run my job and it runs the first two steps fine but when I hit the third step i get an error. In the third step I have an ole db connection to an AS400 db and downloading data from it. Is there any way that I can get this to read and accept the ole db connection because it seems like it doesn't recognize it.
Thanks
Brian
What is the third step? A task of some sort? What kind of task- what object did you drag onto the surface?
Donald Farmer
sqlWe have an SSIS package that was created to migrate data in from a few production databases. The steps for the package are as follows...
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:
|||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.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 = pidDECLARE @.inxml XML
DECLARE @.res XML
DECLARE @.pid INTDECLARE 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
http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1|||
ok I posted the question there as well..
I have a few reports. I want to run each report as one step of sql agent job. If the report succeeds only then the job should move to the next step ? is it possible
I am able to make separate jobs for separate reports but not as steps of one job.
Thanks in advance
When you say "run the report", what are you trying to accomplish? Pre-creating the intermediary report so subsequent requests are rendered more quickly? Or are you trying to deliver the fully rendered report to a user?In either case, there are built-in mechanisms for doing that.
Can you elaborate a bit?
Thanks!
Joe|||
I want to deliver the fully rendered report to the user. Actually I have around 50 reports in 50 different scheduled jobs. When the schedule goes as planned its ok. But when the schedule fails for some reason I have to manually run 50 reports one after the other. I want some kind of job that runs all the reports one after the other. i.e. one report in each step.
I have a few reports. I want to run each report as one step of sql agent job. If the report succeeds only then the job should move to the next step ? is it possible
I am able to make separate jobs for separate reports but not as steps of one job.
Thanks in advance
When you say "run the report", what are you trying to accomplish? Pre-creating the intermediary report so subsequent requests are rendered more quickly? Or are you trying to deliver the fully rendered report to a user?In either case, there are built-in mechanisms for doing that.
Can you elaborate a bit?
Thanks!
Joe|||
I want to deliver the fully rendered report to the user. Actually I have around 50 reports in 50 different scheduled jobs. When the schedule goes as planned its ok. But when the schedule fails for some reason I have to manually run 50 reports one after the other. I want some kind of job that runs all the reports one after the other. i.e. one report in each step.
