Introduction

This article covers how to implement the SQL Server 2008 Execution Log Sample Reports database and SSIS package on SQL Server 2008 R2. It does not cover how to deploy the Execution Log Sample Reports to Reporting Services.

We have recently started to migrate our reporting server from SQL Server 2005 to SQL Server 2008 R2. One of the tasks required is to migrate the Execution Log Reports. The SQL Server 2008 R2 product samples do not contain the Execution Log Sample Reports. I downloaded the SQL Server 2008 product samples package from Codeplex and found the code and packages I required under Samples\Reporting Services\Report Samples\Server Management Sample Reports\Execution Log Sample Reports.

I followed the instructions in the readme_ServerManagementReports.htm file (under Samples\Reporting Services\Report Samples\Server Management Sample Reports\) for creating the database for report execution data (RSExecutionLog) and the tables therein. I created the scheduled job for the RSExecutionLog_Update SSIS package. The job failed.

After doing some Google spadework, it was apparent that I was not the only one having problems getting this to work under SQL Server 2008. I didn't find a solution though. So I worked on my own solution. For those who aren't interested in the process I went through and know their way around BIDS & SSMS, the quick notes are at the bottom of this article. These quick notes cover the steps required to implement the database and SSIS package used.

Creating The Database And Tables

This was the easiest part of the whole process. I scripted out our existing [SQL 2005] RSExecutionLogs database and made an alteration to the CREATE DATABASE statement to change where the data files were being created.

I then ran the Createtables.sql script from the Product Samples, which creates the tables and default values used by the Execution Log reports.

No problems were encountered.

SSIS Package And Scheduled Task

Once the RSExecutionLog database and tables have been created, an SSIS package (included in the Product Samples) needs to be scheduled to run to populate the RSExecutionLog database from data held in the ReportServer database.

I imported the RSExecutionLogUpdate.dtsx package into SQL Server Integration Services, storing it in the File System.

I created a new job to run the package, although not exactly as per the instructions. I set the Package source to SSIS Package Store, set the server name and selected the package I had just imported to the File System (see Figure 1).

Figure 1

I did not use the configuration file, but did set the Destination and Source Connection Manager strings on the Data sources tab, as the SQL Server 2008 R2 installation is a named instance (see Figure 2). This is what we have configured for SQL Server 2005 and it works perfectly.

Figure 2

I manually ran the job. I received an error, the pertinent part being this:

Source: Dim_Status Read Status Codes [9] Description: There was an error with output column "Status" (51) on output "OLE DB Source Output" (17). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". End Error Error: 2010-10-07 10:54:56.75 Code: 0xC020902A Source: Dim_Status Read Status Codes [9] Description: The "output column "Status" (51)" failed because truncation occurred, and the truncation row disposition on "output column "Status" (51)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

In particular, the text 'The "output column "Status" (51)" failed because truncation occurred...' told me what was failing. That meant there must have been a difference in length in a data type definition, likely to be a varchar or nvarchar, between the source database and the destination database.

Oh dear.

Editing The Package

It seemed in order to get this to work, I would need to make some changes to the SSIS package.

I opened SQL Server 2008 R2 Business Intelligence Development Studio, created a new Integration Services project and imported the RSExecutionLog_Update.dtsx package. I opened the package and started looking around.

The first thing I did was change the Server Name used by the Connection Managers to that of the server I was running the package on. There was a warning showing on the Dim_Status data flow task (yellow caution symbol). Opening the Dim_Status data flow task, I saw the warning was from the Read Status Codes item. The tooltip indicates this was the source of the truncation error (see Figure 3).

Figure 3

Double-clicking on the Read Status Codes item, gave another message (see Figure 4).

Figure 4

I clicked on Yes. This opened the source editor. Seeing nothing else amiss, I clicked OK. This caused warning to appear on the Write new Status Codes item. The tooltip indicates another truncation error (see Figure 5).

Figure 5

The table in question was dbo.StatusCodes in the RSExecutionLog database. Looking at the table, I saw the Status column was defined as nvarchar(32). So what was the source defined as? I checked the dbo.ExecutionLogStorage table in the ReportServer database. The Status column was defined as nvarchar(40).

I altered the RSExecutionLog StatusCodes table to define the Status column as nvarchar(40).

ALTER TABLE dbo.StatusCodes
ALTER COLUMN [Status] nvarchar(40) NOT NULL;
GO

I closed and reopened the package in BIDS, double-clicked on the Write new Status Codes item, clicked OK. The warning disappeared. Progress!

There was another warning to deal with on the Update Execution Logs task. Opening this item, I saw a truncation warning on the Read Logs item (see Figure 6).

Figure 6

Opening the Read Logs item, I got another message regarding metadata. I clicked Yes then OK when the source editor came up. The warning disappeared, but now the Union All 1 item was showing an error. This error was a metadata mismatch (see Figure 7).

Figure 7

Unfortunately, double-clicking the Union All 1 item just brought up a list of column matches between input and output columns. No metadata there. After some more poking around, wailing & gnashing of teeth, I found what I was looking for by double-clicking the data flow links (see Figures 8 and 9).

Figure 8 (link from Lookup Report to Union All 1)

Figure 9 (link from Union All 1 to Lookup User)

Aha! So something in the Union All 1 item was changing the data type, or so I thought. I could not find any way to edit the metadata, either in the data flow links or in the Union All item. I also checked the ExecutionLogStorage table in the ReportServer database on both SQL Server 2008 R2 and SQL Server 2005 (it is ExecutionLog in SQL Server 2005). The RequestType column in SQL Server 2005 is defined as bit but in SQL Server 2008 R2 it is defined as tinyint.

I finally found what I was looking for at http://www.sqldev.org/sql-server-integration-services/update-metadata-on-unionall-component-13840.shtml. Basically, the metadata doesn't automatically refresh - you have to force the issue. I opened the Union All 1 item, right-clicked the RequestType row and clicked Delete (see Figure 10). I did the same for the Status row.

Figure 10

I went to the bottom [blank] row, selected RequestType from the drop-down box in the Union All Input 1 column and the same for the Union All Input 3 column (see Figure 11). This auto-populates the Output Column Name. I repeated the process for the Status column.

Figure 11

The error on Union All 1 disappeared. An error appeared on Union All 2. Opening the Union All 2 item, a dialogue box came up advising there were invalid column references. In other words, Union All 2 was expecting particular columns to be sent to it and could no longer find them (see Figure 12).

Figure 12

Even though the column names were the same, SSIS must use some sort of internal identifier, which no longer matched up since I deleted and recreated the RequestType and Status metadata. I clicked OK to let SSIS automatically match up the invalid columns. That still left an error, but that was the same metadata mismatch error I had just dealt with for Union All 1. I followed the same steps as for Union All 1. I removed & recreated the column mappings. This process was also done for Union All 3 and Union All 4.

After all that, an error appeared on the Lookup and Set Request Type items. Double-clicking on the Lookup item showed the same Invalid Column Reference error I had with the Union All items. I clicked OK to automatically fix it up.

I double-clicked on the Set Request Type item. This problem was a bit trickier (see Figure 13). The error was telling me that an expression was expecting DT_BOOL (bit data type) data but received DT_U1 (tinyint data type) data.

Figure 13

So a tinyint value had been passed all the way through the chain only to be unusable at this point because a function being used is dependent on a Boolean data type being used.

How was the original source information being retrieved? I went back to the top of the data flow and opened the Read Logs item to see where the source data was coming from (see Figure 14).

Figure 14

The source data was coming from a query that was stored in a variable called sSQL_Log. I figured what I needed to do was rewrite the query to CAST the RequestType value to bit from tinyint.

I clicked on the Package Explorer tab, expanded the Variables tree item and looked for the sSQL_Log variable (see Figure 15).

Figure 15

I changed the Value to this (yes, there's no FROM clause - that gets added later during package execution):

SELECT NEWID() AS ExecutionLogID, l.[LogEntryId], l.[InstanceName], l.[ReportID], l.[UserName], l.[ExecutionId], CAST(l.[RequestType] AS bit) AS "RequestType", l.[Format], l.[Parameters], l.[ReportAction], l.[TimeStart], l.[TimeEnd], l.[TimeDataRetrieval], l.[TimeProcessing], l.[TimeRendering], l.[Source], l.[Status], l.[ByteCount], l.[RowCount], l.[AdditionalInfo] FROM ExecutionLogStorage l WITH (NOLOCK)

Of course, this meant the metadata had changed for the RequestType column, so I had to go through the remove/recreate process for the Union All inputs/outputs again. I double-clicked the Read Logs item and got the usual metadata mismatch error. I clicked OK to let SSIS fix it up. I double-clicked on the Lookup Report item, just in case there was an error or warning that wasn't showing up, and then clicked OK. Then I went through the process above to fix up the Union All items again.

After all that was done, I double-clicked on the Set Request Type item which was still showing an error. I got the Restore Invalid Column References Editor, clicked OK to let SSIS fix up the column mapping for RequestType.

No more errors or warnings!

Rebuild And Redeploy

Now that the package had been fixed, I needed to build it so I could deploy it to SSIS and schedule it to run daily. I built the RSExecutionLog_Update package and deployed it to SSIS, overwriting the existing item. I ran the SQL Agent job I had created earlier with fingers crossed. It worked!

Running Reports

As our ReportServer database had been migrated from SQL Server 2005 to SQL Server 2008, our Execution Log Reports were already in place. All I needed to do was edit the data source being used by the reports to reflect the change in server name. As the data source was configured to use Windows Integrated Security, no change to the credentials was required. All our Execution Log reports ran successfully with no modifications required. This includes the SQL Server 2008 Execution Log Sample Reports.

Conclusion

I don't know if the Execution Report Samples have been updated for SQL Server 2008 R2 and I just haven't found the link yet or whether it's been superseded by something else. The data type for the RequestType column was probably changed for a reason, but until that reason becomes apparent, as long as the process populating the RSExecutionLog database and the reports using the RSExecutionLog database continue to run, I'm not too concerned.

The package could also be configured to use a configuration file. I chose not to do so as I was only deploying to one server (and the same server name is used in all pre-production environments as well).

Until I find the updated samples, I have a working solution at least, as well as expanding my knowledge of SSIS. And if this helps someone else having the same or similar problems, all the better.

Quick Notes

The following is a concise guide to what was covered above, without the rambling and with the assumption that the reader can find their way around BIDS & SSMS.

1. Create RSExecutionLog database.
2. Create RSExecutionLog tables (from SQL Server 2008 Product Samples code).
3. Alter the RequestType column in the StatusCodes table in the RSExecutionLog database to be nvarchar(40).
4. Create new project in BIDS, add RSExecutionLog_Update.dtsx.
5. Change connection manager connection strings.
6. Edit s_SQL_Log variable. Set Value to: SELECT NEWID() AS ExecutionLogID, l.[LogEntryId], l.[InstanceName], l.[ReportID], l.[UserName], l.[ExecutionId], CAST(l.[RequestType] AS bit) AS "RequestType", l.[Format], l.[Parameters], l.[ReportAction], l.[TimeStart], l.[TimeEnd], l.[TimeDataRetrieval], l.[TimeProcessing], l.[TimeRendering], l.[Source], l.[Status], l.[ByteCount], l.[RowCount], l.[AdditionalInfo] FROM ExecutionLogStorage l WITH (NOLOCK)
7. Save changes, close package, reopen.
8. Open Dim_Status control flow item. Open Read Status Codes data flow item. Click Yes. Click OK. Open Write new Status Codes. Click OK.
9. Open Update Execution Logs control flow item. Open Read Logs data flow item. Click Yes. Click OK.
10. Open Union All 1 data flow item. Delete and recreate RequestType and Status column mappings. Repeat for Union All 2, Union All 3, Union All 4 (NB - 2, 3 & 4 will require remapping of columns as well, due to the mappings being dropped & recreated).
11. Open Lookup. Click OK.
12. Save package.
13. Build package.
14. Deploy package to SSIS.
15. Create SQL Agent job to run package.
16. Done!