As we know, DMX is not just a query language - it includes syntax for creating and processing mining structures and models as well. So you could choose build your entire data mining project using DMX, except for one thing: to train your mining structure/model using INSERT-INTO, you need a data source object and DMX doesn't give you a way to create one!

Fortunately, this is easy to fix - just write a stored procedure that creates data source objects for you, deploy it to the server and viola, you can create as many data sources as you want directly from your queries without having to write C# code each time to do so.

Deploying the Stored Procedure

You can create a C# class library project in VS and replace the contents of the Class1.cs file with the code below or you can download the project and just fix up the references.

Follow these steps to build and deploy the project:

1. Add a reference to the msmgdsrv.dll (Microsoft.AnalysisServices.AdomdServer.dll) class library from the Browse tab.
   This class library is available in the location where Analysis Services 2005 is installed By default, this location has the form:
      C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\bin.
   Please note that, depending on your installation options, MSSQL.2 might be MSSQL.1 or MSSQL.3 or so.
2. Add a reference to "Analysis Management Objects" from the .NET tab.
3. Build the project.
4. Deploy the stored procedure on the server:
1. Open SQL Server Management Studio.
2. Connect to the target Analysis Services server instance.
3. In the Object Explorer, select the 'Assemblies' node at the server level.
4. Right-click and select 'New Assembly'.
5. For the FileName, field, use the Browse (...) button to navigate to the location where the stored procedure was built. A file named CreateDataSourceSP.dll should be available at that location, under the bin\debug folder. Select that file.
6. Assembly Name should be automatically filled with CreateDataSourceSP.
7. Set Permissions  to Unrestricted.
8. Set Impersonation to "Use the credentials of the current user".
9. Click OK to deploy the stored procedure.

Using the Stored Proc from DMX

You can now call the stored procedure to create a new data source using the following syntax in DMX:

CALL CreateDataSourceSP.CreateDataSource('NewDS', 'Provider=SQLNCLI;Location=localhost','ImpersonateCurrentUser','','')

Source Code

And finally, here's the source code if you want to create your own project:

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.AnalysisServices;

using Microsoft.AnalysisServices.AdomdServer;

using System.Diagnostics;

namespace SSDM

{

    public class SSDM

    {

        [SafeToPrepare(true)]

        public void CreateDataSource(

            string DataSourceName,

            string ConnectionString,

            string sImpersonationMode,

            string Account,

            string Password)

        {

            if (Context.ExecuteForPrepare)

                return;

            // Connect to the calling session

            Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server();

            svr.Connect("*");

            // Get the current database

            Database db = svr.Databases.GetByName(Context.CurrentDatabaseName);

            // Create a new datasource and set connection string

            DataSource ds = db.DataSources.AddNew(DataSourceName, DataSourceName);

            ds.ConnectionString = ConnectionString;

            // Set the impersonation info

            bool FoundMode = false;

            foreach (ImpersonationMode mode in Enum.GetValues(typeof(ImpersonationMode)))

            {

                if (mode.ToString() == sImpersonationMode)

                {

                    FoundMode = true;

                    ds.ImpersonationInfo = new ImpersonationInfo(mode, Account, Password);

                    break;

                }

            }

            if (!FoundMode)

                throw (new SystemException("Invalid Impersonation Mode"));

            // Update the server with the new datasource

            ds.Update();

        }

    }

}