Many of us use the Execute SQL task in SSIS to creating warm cache for the MDX queries. If you do the same and find that some of your MDX queries are timing out after 30 seconds. You may want to go back to check what "connection type" are used in the tasks. If you are using ADO connection type like the screen shot below, change it to OLE DB connection. ADO has a command timeout default to 30 seconds. Changing the connection type to OLE DB instead of ADO can avoid this 30 seconds timeout issue.

SQL Server 2005 SSIS and BIDS

Few days ago, one of my clients contacted me that he ran into the following error when he attempted to run his “Slowly Changing Dimension” (SCD) task in SSIS package. He was curious whether or not the error was SCD related.

Error: 0xC0202009 at LoadChangingDim, Connection manager "Excel Connection Manager": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154.

An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered".

Error: 0xC020801C at Loading SCD, Data without keys [163]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error: 0xC0047017 at Loading SCD, DTS.Pipeline: component "Data without keys" (163) failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Loading SCD, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Loading SCD: There were errors during task validation.

SSIS package "LoadChangingDim.dtsx" finished: Failure.

This error had nothing to do with SSIS’s SCD implementation. The failing step (color red in the screen shot above) actually was outside the SCD task. The user was testing his package on a 64 bit machine. He was attempting to direct incorrect data to an Excel file as part of the package workflow. However, Office Excel (including most of the office products) does not have 64bit provider and hence the error above on Excel connection.

To correct the issue, once he changed the setting “Run64bitRuntime” from True to False in the project properties dialog page, the error was gone.

For more about this common issue: see http://technet.microsoft.com/en-us/library/ms141766.aspx

Version: SQL Server 2005 Analysis Services

One user has a business requirement to create a parent-child dimension with composite key but he could not find any example to show him how. He has a schema similar to the one below (we modified the Foodmart 2000 database to show a “how to” example). Any attempt to build a composite key parent-child dimension via the wizard, the following warning is seen:

• Warning: The dimension Wizard does not support the creation of composite key parent-child dimensions. To set the parent-child relation on this table, use Dimension Editor

To build one to fit the user’s requirement, here are the steps we used:

1. Make sure the relationship between the fact table and the dimension table is properly set.

2. First build a regular dimension using the Dimension Wizard. Select all the composite key columns. In this example, the columns we selected are region_id and employee_id.

3. Follow the wizard to the end. (You will find that the option to make this a parent-child dimension is greyed out). A warning is seen: The dimension Wizard does not support the creation of composite key parent-child dimensions. To set the parent-child relation on this table, use Dimension Editor

4. Inside Dimension Editor, make sure that the key columns for the key attribute (in here is Employee attribute) contains 2 columns just like the picture below:

5. Create a new attribute from the key employee_id again:

6. Name this new attribute as “Employees”. Toggle the Usage property of this attribute to “Parent” (default: Regular). Modify the key columns to include the key columns (child columns) join back to their parent columns. Make sure the order of the key columns here are matching with the order of the key columns in key attribute Employee such as region_id --> report_to_region_id, and employee_id --> supervisor_id.

7. Save the dimension. Done.