Using Excel 2007 ( xlsx) in SSIS or exporting more than 65536 row in ssis

Very common scenario when there is a need to export a table having more than 65536 rows to EXCEL.  Best solution to this is use Excel 2007 (.xlsx). But the problem SSIS Excel Source or Destination does not provide support for it.

We can still connect to Excel 2007 using SSIS and export/import data.

HOW..lets see in an example

I have have table having 70,000 rows and we will pump this to an Excel file

1. The table

2. The Excel work:

Create an OleDb connection to Excel in Connection Manager

a. Use Microsoft Office 12.0 Access Database Provider from Provider list

b. Give Path to the xlsx file in Server or File name

c. Go to the ALL tab and give Excel 12.0 as Extended Property

3. Now use OleDB Destination (careful we are using Excel Destination here) and give Excel Connection Mgr, select the Sheet name, Map the columns and there it is.

4. Run

SSAS: Memory error

Memory error: The operation cannot be completed because the memory quota estimate (XXXXMB) exceeds the available system memory (XXXXMB).

SSAS throws this error while processing one of the dimension in Cube when memory need for the operation exceeds memory available for SSAS, primarily due to size of dimension.


To solve this error there are three options

A)  Increase size of RAM. (I know, not easy to implement in most organizations, so we go to next option)

B)  Divide you Dimension table so as they are small enough to complete processing in given memory.
for example if Dimension is holding Customer Names then divide DimCustomer into two table, one with Customer names starting with A-M and another with N-Z. It will require updation of fact table with new Customer Primary key.

(Again will require time and design changes may not be possible straight away, so we go to next option)

C)  BufferMemoryLimit: There is always an easy fix but not permanent so as our next option.

For Processing requirement by default SSAS provides 60% of memory available to it. For a fix we can increase Buffer Memoryt Limit  percentage so as to accommodate the processing requirement.

It is specified in msmdsrv.ini file in Program Files/SQL Sever/.../SSAS/Config/ folder at

 
   


You can increase this BufferMemoryLimit to you suitability.
a) Between 0-100, it is in Percentage and allocated % of Memory to the operations
b) Above 100, it is in MB and allocates specified Memory to the operation

As I said it is a quick fix and should be treated as quick fix only and you may need to test if it is affecting other operations performance.


--Happy Processing!!