I worked on a SSIS package a few months back to retrieve data from a SSAS cube with MDX queries. A co-worker recently is developing a SSIS package to perform a similar task, but couldn’t get the SSIS package to work. The data flow was lost somewhere and the package could never finish the data loading.

This is what it will look like in the Data Flow task. The OLE DB Source will stay yellow forever.

I couldn’t remember right away how my SSIS package was configured to make the data flow work. I had to find my old SSIS package and open it to refresh my memory.

Need to set Format=Tabular in the Data Link Extended Properties

After choosing the OLE DB Provider for Analysis Services 10.0 as the provider for the connection manager, we need to go to the All tab to set Format=Tabular in the Data Link Extended Properties.

There are many blogs on this little “secret” developers discovered. Some claimed that this is a fix for a bug in SSIS 2005 SP1. I had to do this in both SSIS 2005 and 2008 to make it work. So I am not sure if it is caused by a bug or a designed “feature” in SSIS.

Neither can I find more information about what this property value means. You are welcome to share if you have more insight on this.

The happy green color is what you will see once you set Format=Tabular in the Data Link Extended Properties.

A couple of notes:

1. About the data type. Whenever you try to access the column information, you will get a “nice” warning from SSIS that data type DT_WSTR (nvarchar) will be used instead. Again if you have more insight on this, you are welcome to share.

2. I used a Data Conversion transformation to convert the DT_WSTR to DT_STR or DT_NUMERIC accordingly.