Expert SQL Server™ 2005 Integration Services

6.2. SSAS Integration Basics Within SSIS

To make an informed decision on how you will handle your SSAS processing, it is important to understand the uses, benefits, and drawbacks of the built-in SSAS components in SSIS. This section is followed by examples of the various choices reviewed, which, in some cases, use these features.

The two built-in control flow components already mentioned that interact with SSAS are the Analysis Services Processing Task and the Analysis Services Execute DDL Task. The two data flow components that interact with SSAS are the Dimension Processing destination and the Partition Processing destination. All of these components leverage an Analysis Services connection that must be defined in the Connection Managers window pane.

To create a new connection, right-click in the Connection Managers window pane and select New Analysis Services Connection, which brings up the Add Analysis Services Connection Manager window with a connection string. When you select Edit next to the connection string, it opens up the standard Connection Manager editor, as shown in Figure 6-4.

The Server or file name entry is the name of the SSAS server, and the Log on to the server selection defines how the connection should pass its authentication to the SSAS server. SSAS only supports Windows authentication (local machine or Active Directory security), so the two choices for security both relate to Windows security.

• The Use Windows NT Integrated Security option uses the existing ...