You definitely need to use Format=Tabular in the connection string with the OLE DB source going against an SSAS source. I would highly recommend installing the latest service pack, SQL Server 2005 Service Pack 3 (SP3) – now available, also since you are only using the RTM release (SQL Server 2005 Build List[/url]).

Are you able to preview the SQL Command properly in the OLE DB source in the Data Flow Task? You could also setup a trace on the SSAS instance with Profiler to see what if anything is being passed to further troubleshoot this issue. Setup logging on the package to and see if you can get additional information in regards to the specifics of the error. This might have been resolved in a service pack too, so I would recommend getting up-to-date with that first.