Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 13, 2006

Process Analysis Services Objects with Integration Services - Page 4

By William Pearson

Add an Analysis Services Connection Manager to the Integration Services Package

An Analysis Services Connection Manager enables our package to connect to the server that runs the Analysis Services database upon which (or upon whose member objects) we wish to use our Analysis Services Processing task. (We can also use the Connection Manager to connect to an Analysis Services project that provides "BI-development-Studio-only" access to cube and dimension data, to establish connections between the package and the server / database to which we later deploy the package).

At run time, the Connection Manager is resolved as an Analysis Services connection. As part of the creation process, Integration Services sets the properties (one of which, ConnectionManagerType, is set to MSOLAP90), adding it to the package's Connections collection. We will add and configure the Analysis Services Connection Manager by taking the following steps.

1.  Right-click a point within the Connection Managers pane, located below the Control Flow tab in the SSIS Designer.

2. Select New Analysis Services Connection ... from the context menu that appears, as shown in Illustration 12.

Click for larger image

Illustration 12: Initiating the Creation of a New Analysis Services Connection in the Package

3.  Right-click a point within the Connection Managers pane, located below the Control Flow tab in the SSIS Designer.

4.  Click the Edit button on the Add Analysis Services Connection Manager dialog that immediately appears, as depicted in Illustration 13.

Click for larger image

Illustration 13: Click Edit on the Analysis Services Connection Manager Dialog

The Connection Manager dialog appears next.

5.  In the Server or file name box, enter the server (or server / instance) appropriate to your environment.

6.  Select the appropriate radio button in the Log on to the server section of the Connection Manager dialog.

7.  In the Initial catalog selector, select Adventure Works DW.

8.  Click the Test Connection button to ascertain connectivity.

A Connection Manager message box appears, indicating that the test connection has succeeded, as shown in Illustration 14.


Illustration 14: Testing Positive for Connectivity to the Analysis Services Database

9.  Click OK to dismiss the message box, and to return to the Connection Manager dialog.

The Connection Manager dialog appears, with our input, similar to that depicted in Illustration 15.


Illustration 15: The Connection Manager Dialog ...

10.  Click OK to accept settings and close the dialog.

We return to the Add Analysis Services Connection Manager dialog, which appears similar to that shown in Illustration 16.


Illustration 16: The Add Analysis Services Connection Manager Dialog ...

11.  Click OK to accept and dismiss the Add Analysis Services Connection Manager dialog.

We arrive once more at the Integration Services Designer, where we see the new Connection Manager within the Connection Managers pane as depicted in Illustration 17.


Illustration 17: The New Connection Manager Appears ...

12.  Select File --> Save All from the main menu, as depicted in Illustration 18, to save our work to this point.


Illustration 18: Saving Our Work to this Point ...

We have now established and tested the Connection Manager we have added to the Analysis Services package. We will begin configuration of the Analysis Services Processing task we have added to the package in the next section.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM