Working with Data Sources (DSO)
This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible.
Collections of data sources (that is, MDStore objects of ClassType clsDataSource) are contained in MDStore objects of ClassType clsDatabase, clsCube,and clsPartition. Each object's data source specifies an external database that will be used as the source of data.
A database can contain multiple data sources in its DataSources collection. However, each cube and partition contains only a single data source.
The two examples in this topic demonstrate how to list and add a data source to the database's DataSources collection.
List Data Sources
The easiest way to list data sources is to iterate through the DataSources collection of an MDStore database object, as shown in the following code example which lists the Name and ConnectionString properties of each data source for every database on a given Analysis server.
The following code example loops through the DataSources collection of each database on the local Analysis server, printing some of the basic properties for each data source in the Immediate window:
Private Sub ListDataSources() Dim dsoServer As New dso.Server Dim dsoDB As dso.MDStore Dim dsoDS As dso.DataSource ' Create a connection to the Analysis server. dsoServer.Connect "LocalHost" ' Step through the databases in the ' MDStores collection of the server. For Each dsoDB In dsoServer.MDStores ' Print the name & description of the database. Debug.Print "DATABASE: " & dsoDB.Name & " - " & _ dsoDB.Description ' Determine whether the database has data sources. If dsoDB.DataSources.Count = 0 Then Debug.Print " Data source: None" Else ' Iterate through and print the data source ' information. For Each dsoDS In dsoDB.DataSources Debug.Print " Data source: " & dsoDS.Name Debug.Print " Valid?:" & dsoDS.IsValid Next End If Next End Sub
Add a Data Source
The process to add a new data source is similar to the process for adding a new database. The AddNew method of the DataSources collection for a given database creates a new data source for the database.
The following code example adds a data source named FoodMart to the TestDB database object on the local Analysis server:
Private Sub AddDataSource() Dim dsoServer As New DSO.Server Dim dsoDB As DSO.MDStore Dim dsoDS As DSO.DataSource Dim strDBName As String Dim strDSName As String Dim strDSConnect As String ' Initialize variables for the database name, ' data source name, and the ConnectionString property ' for the data source. strDBName = "TestDB" strDSName = "FoodMart" strDSConnect = "Provider=SQLOLEDB.1;" & _ "Integrated Security=SSPI;" & _ "Persist Security Info=False;" & _ "Initial Catalog=FoodMart;" & _ "Data Source=FoodMartServer;" & _ "Connect Timeout=15" ' Create a connection to the Analysis server. dsoServer.Connect "LocalHost" ' Locate the database first. If dsoServer.MDStores.Find(strDBName) Then Set dsoDB = dsoServer.MDStores(strDBName) ' Check to see whether the data source already exists. If dsoDB.DataSources.Find(strDSName) Then MsgBox "Data source " & strDSName & _ " already exists for database " & strDBName Else ' Create a new data source. Set dsoDS = dsoDB.DataSources.AddNew(strDSName) ' Add the ConnectionString properties dsoDS.ConnectionString = strDSConnect ' Update the data source. dsoDS.Update ' Inform the user MsgBox "Data source " & strDSName & _ " has been added to database " & strDBName End If Else MsgBox strDBName & " is missing." End If End Sub