Last week we created a cube using SSAS. Now I am going to show you how your end users will connect to it using Excel and how they can use the data in a meaningful way while using their favorite tool (Excel).
Here is an issue you may receive if you try to import data from an Excel spreadsheet into a Sql Server database table:
Error at Data Flow Task [OLE DB Destination ]: Columns “firstname” and “FirstName” cannot convert between unicode and non-unicode string data types.
If you have ever tried to run or create a SSIS extract package that exports to Excel, you may run into the error below:
[SSIS.Pipeline] Error: Excel Destination failed validation and returned error code 0xC020801C.
Sql Server Management Studio is very robust and can do many things, but like any other product cannot do everything. Luckily there are add-ins available that fill in some of the gaps. My favorite and one I use every single day is SSMSBoost, (I no longer use it due to the change in the community license and the useful functions being taken away.)