SQL Server Integration Services 2005 contains multiple tasks and transforms that allow integration with Analysis Services. Among them, a set of transforms and tasks dedicated for Data Mining. We will show here how an Analysis Services query statement can be used as a data source in a SSIS Data Flow.
If one browses the transforms available for the SSIS data flow, one will notice that, among Data Flow Sources, there is no Analysis Services source. The closest transform is the Data Mining Query, which can only be used to perform predictions over data coming from a different source. However, certain Data Mining statements do not use the PREDICTION JOIN operator, so they do not need data to be applied to. Such statements include drill through statements (SELECT … FROM .CASES) or forecasting statements (SELECT PredictTimeSeries(…) FROM ).
To use such a statement as a data source, start by creating a connection to the Data Mining server. Connections are usually created by using the context menu in the Connection Managers panel of the Package designer. Choose to create an "OLE DB connection", and not an "Analysis Services connection", like below:
For the newly created OLE DB connection, use the Microsoft OLE DB Provider for Analysis Services 9.0:
Then, point the connection to your Analysis Services server and to your database of choice.
Now, add a new OLE DB Source to your pipeline. For the new source, use the OLE DB connection you just created and make sure that the Data access mode is set to "SQL Command". Type in your Data Mining query and don't forget to preview the Columns
Your data source is now configured and ready to connect to other pipeline components.