Excel connections in SSIS 64-bit

This is a new one on me…when running SSIS in a 64-bit environment, Excel connections (and Access as well I presume) do not work, as the OLE DB Provider for Jet is not supported.

At a current client, they are moving the server platform from Windows 2003 Server to Windows 2008 Server 64-bit.  I had developed some ETL packages for them, pulling data from Excel and CSV sources.  The packages get executed when the files have been unzipped and downloaded to a specified folder.  This is done via simple batch files, which are along the lines of the following:

dtexec /f "F:SSIS PackagesReportingImportClientCSV.dtsx"

When I moved all the packages to Windows 2008, the CSV packages ran no problem.  But when I tried to execute one that imported from Excel, I received the following error:

Code: 0xC00F9304
   Source: ImportClientXLS Connection manager "Excel Connection Manager"

   Description: SSIS Error Code DTS_E_OLEDB_EXCEL_NOT_SUPPORTED: The Excel Connection Manager is not supported in the 64-bit version of SSIS, as no OLE DB provider is available.

In order to use the OLE DB for Jet providers, the packages must be run in 32-bit mode.  This is done by using the x86 version of dtexec and specifying the "/X86" switch.  I amended the batch file in question to look similar to this:

"C:Program Files (x86)Microsoft SQL Server100DTSBinndtexec.exe" /f "F:SSIS PackagesReportingImportClientXLS.dtsx" /X86

Voila!  The package ran successfully.

This link from Microsoft details the limitations when running SSIS in 64-bit environments: 64-bit Considerations for Integration Services

SSRS 2008: Using the TOP filter in a chart

I found an interesting forum post over at SQLTeam this morning, and decided to have a go at coming up with a solution.

Filters are quite commonly used in Tablix reports to show only the top n rows from a dataset.  This is easily done by using the Top N operator in the Filters tab of the Tablix, and choosing the column in the Expression drop down.

In the forum post however, the original poster was wanting to show the top n values in a chart.  The chart would by default sum up the values in the data field.  The trick would be to get the top n aggregated values, as opposed to taking the top n original values and then doing the aggregation.  To start with, I created the following very simple table and data.

create table EmployeeSales
EmployeeName varchar(20),
SalesAmount money

insert into EmployeeSales
(EmployeeName, SalesAmount)
(‘Jerry’, 9.99),
(‘Jerry’, 30.00),
(‘Jerry’, 55.70),
(‘Jerry’, 4.99),
(‘George’, 3.99),
(‘George’, 1.99),
(‘George’, 1.99),
(‘George’, 1.99),
(‘George’, 15.00),
(‘Kramer’, 17.00),
(‘Kramer’, 152.00),
(‘Kramer’, 99.99),
(‘Kramer’, 75.00),
(‘Elaine’, 9.99),
(‘Elaine’, 15.99),
(‘Elaine’, 3.99),
(‘Newman’, 120.00),
(‘Newman’, 5.99),
(‘Newman’, 5.99),
(‘Newman’, 0.99)

I then created a report in SSRS 2008 with a DataSet called Sales, which contained the following SQL query.

select EmployeeName, SalesAmount
from EmployeeSales

I dropped a Tablix onto the report, and set the source to be the Sales dataset, with two columns for the fields in the dataset.  At this point I also created two Parameters, @TopSalesN and @TopTotalSalesN, both integers with a default value of 5.  I then went to the Filters tab in the Tablix Properties and added a filter with the following values (see picture)…

Expression: [SalesAmount]
Operator: Top N
Value: [@TopSalesN]


Then in the Sorting tab, I sorted the data by [SalesAmount], order Z to A.

Running a preview confirmed this worked as expected and I could change the parameter to show as many values as I needed.

Then I added a standard column chart next to the tablix.  I dropped the SalesAmount column on the data fields section, and EmployeeName onto the category fields section.  There is no filter option on the Series properties for the data, so I tried to add a filter on the Chart Properties.  I found two issues with this.  Firstly, if you try to use an aggregate value in the filter Expression then the report fails to process, as this is not allowed.  If you filter on the raw values, then obviously all you do is remove rows from the raw data and the results are not what you expect.

To produce the correct results, the filtering (and sorting, if required) needs to be added to the Category Group Properties on the Category Field.  I set it up as follows…

Expression: [Sum(SalesAmount)]
Operator: Top N
Value: [@TopTotalSalesN]

This gave me exactly what I was looking for.  The image shows the preview of the report, with the top 10 rows from the raw dataset in the tablix, and the top 3 Sales by Employee in the chart.

The original poster also wanted the chart data to be sorted.  This is easily done by adding a sort on the Category Group Properties, sorting by [Sum(SalesAmount)] Z to A.  The final image shows how this looks.

I can see quite a few situations where using this method of having one “raw” dataset and more than one report item consuming the data in different ways would be very beneficial.  Particularly in cases where you have a data report and you need to show a summary chart relating to the data on the same report.

Another method you can use for “Top N” reports is to pass a TopN parameter into a stored procedure, and use that value in the SQL statement.  For example…

create procedure [dbo].[TopSales]
@TopN tinyint = 20

set nocount on;

select top (@TopN)
from EmployeeSales
order by SalesAmount desc

exec dbo.TopSales 10

Quick tip: Search for a term in a Stored Procedure

Need to find out if a term is referenced in any stored procedures in your database?  Use the following T-SQL statement…

select name, definition
from sys.procedures p
    inner join sys.sql_modules m
        on p.object_id = m.object_id
    m.definition like ‘%search term%’;

This will work in SQL Server 2005 and 2008.

Running scripts on multiple servers in SSMS 2008

SQL Server Management Studio 2008 has a new feature, the ability to run a script on multiple servers at once.  To set this up, the servers need to be registered to a Server Group in the Registered Servers window (see image).  Right-clicking on the Server Group and selecting New Query creates a query window which is connected to the servers in the group (as long as the connection information is correct and the passwords, if using SQL Server Authentication, are saved).  Running a simple query on the master database, in this case:

select top 5 * from sys.columns

returns the top 5 rows from that table from each server.  See the results in the attached image.

The option to Merge Results is True by default.  In a case where the schemas are not identical, this would need to be set to False otherwise an error will be thrown.

Column headings do not repeat in SSRS 2008

I’ve just stumbled upon an odd issue with the Tablix control in SSRS 2008.  I deployed a report on a server a few weeks ago and it is now reporting on enough data to make it span multiple pages.  I wanted the column headings to appear on all pages, not just the first.  However, when I set the RepeatColumnHeaders property on the Tablix to be true, the headings still did not appear on the next page.  After a bit of playing around I enabled Advanced Mode in the Grouping Pane, and had a look at the properties for the top (Static) group in Row Groups.  Under Other, there is a property called RepeatOnNewPage.  Setting this to True made the column headers appear as required.  OK, so that’s fine, but I can’t help but think that this a a bug in SSRS 2008, and should be fixed by MS.  Looking through the RDL file itself, I can’t find anything that would cause the RepeatColumnHeaders property not to behave as expected.

The “Header should remain visible while scrolling” option does not appear to work either; I haven’t yet found a workaround for this.

Formatting Dates in SSRS

In Reporting Services, formatting dates can become a headache for the developer, often due to differing regional settings on servers, development boxes etc.  Therefore I’ve found it much more useful to force the report to use a particular format, rather than allow the server settings to decide for me.  Previously I would have used FormatDateTime in the expression, as shown here:

=FormatDateTime(Fields!DateStamp.Value, DateFormat.ShortDate)

Depending on the regional settings, this could return either 12/3/2008 or 3/12/2008.  When designing the report, I want to know exactly what the date will look like, no matter where it is deployed.  So I instead use the Format function to allow me to choose the format I require.  Some examples:

=Format(Fields!DateStamp.Value, "dd-MMM-yy")
returns 03-Dec-08

=Format(Fields!DateStamp.Value, "dd-MMM-yyyy")
returns 03-Dec-2008

=Format(Fields!DateStamp.Value, "dd-MMMM-yy")
returns 03-December-08

=Format(Fields!DateStamp.Value, "dd-MMM-yyyy H:mm")
returns 03-Dec-2008 16:44

This method removes a lot of the issues regarding date formatting, and means there are no shocks or questions when it comes to deployment time!

Edit: Setting the Language property of the report to your locale will also ensure that dates in your report parameters will have the correct format.  This is something that is often overlooked.

New SQL Server blog

I’ll be posting any SQL Server related stuff in this new blog.  I may even try to make it vaguely interesting!  And I promise not to post my daily “I hate SSIS!”/”I love SSIS!” thoughts here.  🙂

Don’t expect any content in the next two weeks, as I’ll not be working and I hope to have a wee break from databases for the duration.

But let’s make the intro post useful; go get the new SQL Server Cribsheet from Red Gate.  It’s a free e-book, well worth the download.  Link below…

SQL Server Cribsheet