Archive for May, 2008

Connecting to Excel 2007 from SSIS

Create OLE DB Connection using Microsoft Office 12.0 ACE (Access Database Engine) OLEDB provider.

 

Use the following Extended properties:
Excel 12.0;HDR=YES
HDR –> 1st row is HEADER row (column Headings)



*****************without extended properties, you might get this error**********************
TITLE: Connection Manager
——————————

Test connection failed because of an error in initializing provider. Could not find file ‘C:\temp\Sample.xlsx’.

——————————

Tags: , , , ,

Wednesday, May 28th, 2008 SSIS No Comments

Hierarchical Reporting in SQL Server Reporting Services (SSRS)

This post is to remind myself about the hierarchcial grouping Report that I did last week using SQL Server Reporting Services (SSRS). I was trying to understand the business segmentation hierarchy. Among other fields, segmentation table has 2 fields, segment and parent segment, the latter being a recursive pointer to the former (The relation between them is comparable to Employee and Manager fields in the traditional EMP table).

  • Create dataset with segment and parent segment fields
  • Insert a table in the “Layout” tab
  • select the table and select “insert group” option
  • In the grouping and sorting dialog,
    • Select “Segment” field in the “Group on” expression area
    • Select “Parent Segment” in the “Parent Group” area
    • Uncheck “Include group footer” option

  • Drag “Segment” field into the newly added Group Header
  • De-select Table Header, Table Details and Table Footer sections in the Table properties, remove all un-used columns from the table
  • Make sure the Segment textbox is wide enough to show full hierarchy depth

  • Select Group Header box(Segment field) in the table, open properties dialog, goto “Padding” section, and add =Convert.ToString(2 + (Level()*50)) & “pt” as expression to the LEFT property. This is key step to get the indentation (adjust values accordingly) (if you’ve more fields on your report, you might need to add this expression to other fields as well)

  • Optional: added =IIF(Level()=0 OR Level()=1 OR Level()=2,”Bold”,”Normal”) as expression to the “FontWeight” property to make the first two levels bold
  • Report Preview

Saturday, May 24th, 2008 SSRS No Comments

What’s so special about this day at 30 ST MARY AXE (Gherkin, Swiss-Re tower)?

…in case you were wondering, nothing! just a normal working day in the city and the crowds gathered to grab a ?quick? lunch hour pint at the Sterling bar!!

Wednesday, May 7th, 2008 Uncategorized No Comments