SCRUBS for SQL Server 2008 - CE only

Coordinator
Nov 15, 2009 at 7:10 PM

Post your Community Edition questions here.

Premium Edition users should connect with us directly via our support site.

Jul 20, 2011 at 4:38 PM

Just installed the CE version. When I open the Execution Log report it has "Unknown" as the only value for the Sites parameter. Shouldn't it reflect the name of the SP web site or the report site? The reports comes up empty (I tried opening it after executing a request in another report deployed on my web site), so wondering if I'm missing a configuration setting.

 

Thanks for the help,

Dhruv

Coordinator
Jul 21, 2011 at 7:16 PM

Have you successfully run the ETL to import data from SSRS to SCRUBS? Is there data in the SCRUBS db?

Jul 21, 2011 at 9:09 PM
Is there any documentation on this process you can share with me? I've run through all the steps in the installation guide.
Thanks,
Dhruv

On Jul 21, 2011, at 3:17 PM, "summitcloud" <notifications@codeplex.com> wrote:

From: summitcloud

Have you successfully run the ETL to import data from SSRS to SCRUBS? Is there data in the SCRUBS db?

Coordinator
Jul 22, 2011 at 2:32 AM

If you have run through the steps here: http://support.summitcloud.com/KB/a6/scrubs-for-sql-server-2008-ce-installation-step-by-step.aspx

Then can you confirm that the first job has successully run?

Can you confirm that subsequent jobs have run?

Can you confirm if data exists in your Scrubs DB after the run?

Jul 28, 2011 at 6:54 PM
Hi, sorry for the delay response.
When I run the SQL Server Agent job ("SSRS Log Feeder"), it fails to execute and points me to the history log. Step 1 ("Start Job") worked fine after I started the SQL Server Agent service.
Attached is the error log file from SQL for this, can you let me know what I'm missing? Is this the correct log file to look at?
Thanks,
Dhruv

From: summitcloud [notifications@codeplex.com]
Sent: Thursday, July 21, 2011 9:32 PM
To: Dhruv Raheja
Subject: Re: SCRUBS for SQL Server 2008 - CE only [scrubs:75197]

From: summitcloud

If you have run through the steps here: http://support.summitcloud.com/KB/a6/scrubs-for-sql-server-2008-ce-installation-step-by-step.aspx

Then can you confirm that the first job has successully run?

Can you confirm that subsequent jobs have run?

Can you confirm if data exists in your Scrubs DB after the run?

Sep 2, 2011 at 6:50 PM

Hello,

Unfortunately the “SSRS Log Feeder” step still isn’t quite working for me. Following is the error trace if it helps. Any advice would be much appreciated!

Date 9/2/2011 1:45:23 PM

Log Job History (SummitCloud SCRUBS - SSRS Log Feeder)

Step ID 1

Server WIN-SUVU5OLCM7F

Job Name SummitCloud SCRUBS - SSRS Log Feeder

Step Name SSRS Log Update

Duration 00:00:02

Sql Severity 0

Sql Message ID 0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted 0

Message

Executed as user: CORP\Administrator. Microsoft (R) SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 1:45:23 PM Error: 2011-09-02 13:45:25.09 Code: 0xC0202009 Source: Reports Write new Reports [135] Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The statement has been terminated.". An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80004005 Description: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Reports__ReportT__2D27B809". The conflict occurred in database "SummitCloud_SCRUBS", table "dbo.ReportTypes", column 'ReportType'.". End Error Error: 2011-09-02 13:45:25.09 Code: 0xC0047022 Source: Reports SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Write new Reports" (135) failed with error code 0xC0202009 while processing input "OLE DB Destination Input" (146). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:45:23 PM Finished: 1:45:25 PM Elapsed: 1.437 seconds. The package execution failed. The step failed.

From: Dhruv Raheja
Sent: Thursday, July 28, 2011 1:54 PM
To: scrubs@discussions.codeplex.com
Subject: RE: SCRUBS for SQL Server 2008 - CE only [scrubs:75197]

Hi, sorry for the delay response.

When I run the SQL Server Agent job ("SSRS Log Feeder"), it fails to execute and points me to the history log. Step 1 ("Start Job") worked fine after I started the SQL Server Agent service.

Attached is the error log file from SQL for this, can you let me know what I'm missing? Is this the correct log file to look at?

Thanks,

Dhruv


From: summitcloud [notifications@codeplex.com]
Sent: Thursday, July 21, 2011 9:32 PM
To: Dhruv Raheja
Subject: Re: SCRUBS for SQL Server 2008 - CE only [scrubs:75197]

From: summitcloud

If you have run through the steps here: http://support.summitcloud.com/KB/a6/scrubs-for-sql-server-2008-ce-installation-step-by-step.aspx

Then can you confirm that the first job has successully run?

Can you confirm that subsequent jobs have run?

Can you confirm if data exists in your Scrubs DB after the run?

Sep 5, 2011 at 9:19 AM

Hi, 

I will just add some additional info and way i was able to solve this problem: 

We are using SQL 2008 R2, which according to http://www.sqlservercentral.com/Forums/Topic649547-147-1.aspx#bm1100388 has some additional Report types. After adding them (8 = Shared Dataset, 9 = Report Part) to ReportTypes table above error has gone. Unfortunately there was additional error. Here is Message:

MessageExecuted as user: VIALYSIS\Administrator. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  11:08:12  Error: 2011-09-05 11:08:14.20     Code: 0xC0202009     Source: Update Execution Logs Write Paths [7858]     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80004005  Description: "Unspecified error".  End Error  Error: 2011-09-05 11:08:14.25     Code: 0xC020901C     Source: Update Execution Logs Write Paths [7858]     Description: There was an error with input column "ExecutionId" (7940) on input "OLE DB Destination Input" (7871). The column status returned was: "The value violated the integrity constraints for the column.".  End Error  Error: 2011-09-05 11:08:14.25     Code: 0xC0209029     Source: Update Execution Logs Write Paths [7858]     Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "OLE DB Destination Input" (7871)" failed because error code 0xC020907D occurred, and the error row disposition on "input "OLE DB Destination Input" (7871)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.  End Error  Error: 2011-09-05 11:08:14.25     Code: 0xC0047022     Source: Update Execution Logs SSIS.Pipeline     Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Write Paths" (7858) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (7871). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.  End Error  Error: 2011-09-05 11:08:14.34     Code: 0xC0047062     Source: Update Execution Logs Parameter Breakout [5664]     Description: System.Runtime.InteropServices.COMException (0xC0047020): Exception from HRESULT: 0xC0047020     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.HandleUserException(Exception e)     at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.ProcessInput(Int32 inputID, PipelineBuffer buffer)     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostProcessInput(IDTSManagedComponentWrapper100 wrapper, Int32 inputID, IDTSBuffer100 pDTSBuffer, IntPtr bufferWirePacket)  End Error  Error: 2011-09-05 11:08:14.34     Code: 0xC0047022     Source: Update Execution Logs SSIS.Pipeline     Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Parameter Breakout" (5664) failed with error code 0xC0047020 while processing input "Input 0" (5673). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:08:12  Finished: 11:08:14  Elapsed:  2.153 seconds.  The package execution failed.  The step failed.

 

Fast fix for that was to change ExecutionID column in ExecutionPath table to allow null values. I don't know if this is good idea, but i didn't get any null values in that column after executing timer job. 

Coordinator
Sep 6, 2011 at 4:51 PM

@silent_mark:

Admittedly, we have not updated much in the CE code base since the initial release. We have applied updates to the Premium Edition.

Adding the Report Types above is a good work around that you note above. In PE, we modified the SSIS package so any new report types are automatically created in an effort to future proof.

Not having the ExecutionID in the ExecutionPath table will prevent any relationship back...for some types of execution, this is unavailable from the SSRS logging. We've also handled that in the SSIS to deposit good records only. ExecutionPath is really just there to allow you to view a user navigation path. You have to roll your own reports in CE...and if you don't use it, this won't limit your use of this version at all. In PE, we have a clickstream style report that uses this data.

 

Aug 7, 2012 at 7:51 PM

Hi I am evaluating this CE system to build a case to buy the premium edition for 4 servers.

I have set everything up and followed your instructions to setup the SSIS to import from 2 servers. The problem is that everything seems to be importing except for the ExecutionLogs for the 2nd server. I have tried this multiple times now, dropping the Db and starting over each time.

If I import from server1 it will not import the logs from server2, but it will import the reports, machines, users etc from both servers. If I import from server2 first, it will import the logs just fine from that server but then it won't import them from server1.

Can you please help? I can't demo this to Sr Management until this issue is resolved. Thanks!