How to use ASPT in SSIS 2005

Problem

We look forward to automating the processing of latitude and data cubes in SQL Server Analytics services. We want to add this process to an existing SQL Server Integration Services (SSIS) package, which periodically updates the data warehouse from our OLTP system. Can you explain some details about how to use analysis Services to process tasks in an SSIS package?

Expert answers

Profiling Service processing tasks (Analysis Services Processing Task) allow you to work with latitude, measure group partitioning, and mining models in SSIS packages. When you can work with all objects at the same time, you can also choose to process a subset of those objects. For example, you can update specific latitude and fact tables in your data warehouse by running an SSIS package on a regular basis. As the final step of the SSIS package, you can only process latitude and measure the group partitions that use the Data Warehouse table as their data source. Profiling Service processing tasks (Analysis Services Processing Task) allow you to do this.

In this approach, we can follow these steps to use the Profiling Services Processing task in the SSIS package (Analysis Service Processing Task). We will create a sample package that will process latitude and metric group partitions in the Adventure Works DW Analysis Services database, along with SQL Server 2005. We assume that we update our products and exchange rate tables in the Data Warehouse every day by running an SSIS package. We want to add a step to the product and Exchange rate fact table in the SSIS package to update the information available in the SQL Server Analysis Services cube.

To create a sample SSIS package

First start business Intelligence Development Studio (BIDS) from the Microsoft SQL Server 2005 program group (Microsoft SQL Server? , and create a new integrated services project (Integration Server project). An SSIS package named PACKAGE.DTSX is automatically generated and added to the project. Rename the package (package) to SSASPROCESSINGTASK_DEMO.DTSX, and then proceed to the following steps in the SSIS package:

Step One:

Add a connection management (Connection Manager) to the SSAS server. Right-click on the connection management (Connection managers) page and select a new analysis service connection from the Drop-down menu (A/Connection). Accept the default value in the dialog box to connect to the local SSAS server (if you want to connect to an SSAS server on another machine, you can edit it as appropriate).

Figure I