Hitachi Consulting BI Blog

MDX cell set into SSIS data stream [Reed + Pablo]

I've seen two techniques described for getting the results of an MDX query into an SSIS data stream. 1 - Use the OLE DB Source Adapter and ignore the whiny warning. This seems to work when you Preview, but crashes SSIS (even SP2) when you run the job. 2 - Use a linked server and an OpenQuery command. This works but has a lot of overhead. My colleague Pablo Guzman and I came up with a pretty slick way to do it. - Create a new ADO.Net Connection and from the .Net Providers\SQLClient Data Providers list choose the OLE DB Provider for AS9.0. - Create a DataReader Source adapter connected to the new AS connection manager. - Put the MDX into the SQLCommand property--be sure to map the external and output columns. This obviously flattens the cell set, but it doesn't limit what you put on the axes--probably a good idea to have the columns end up static. The DataReader Source adapter turns all the columns into DT_NTEXT, which you then have to convert to DT_WSTR to be able to convert to anything else, but that's standard SSIS data type manipulation. Seems a lot slicker than creating a linked server. Cheers, Reed