SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Data Mining Part 29: Programming with C# and AMO

By Daniel Calbimonte,

Introduction

In this new chapter of our Data Mining series, we will work with C# instead of using VB as we did in the Part 28 of the Data Mining series. This chapter will also teach how to use the debugging in C#. Finally, we will process a Data Mining structure if it was not processed in the last five days.

Requirements

The requirements are the same as were shown in Part 28 of thise series.

Getting Started

1. I am assuming that you already used SSIS in part 28. Drag and drop the script task to the graph pane in a SSIS project.

2. Make sure that the script language is Microsoft C# (this is the language by default).

3. You will also need to add the Analysis Management Objects, as you did in part 28.

4. Add the "Using Microsoft.AnalysisServices" to the top of your code.

5. Now add the following code:

public void Main()
{
// TODO: Add your code here
        //Add the server
        Server DM_Server= new Server();
        //Add the database
        Database AS_Database=new Database();
        //Connect to the Data Mining 
        DM_Server.Connect("Data Source=infra4;Initial Catalog=AdventureWorksDW2014Multidimensional-EE");
        //Get the AS Database
        AS_Database = DM_Server.Databases["AdventureWorksDW2014Multidimensional-EE"];
        //Show the database name
        MessageBox.Show("Database: " + AS_Database.Name);

Dts.TaskResult = (int)ScriptResults.Success;
}

The code shows a message with the database name that contains the mining structure. This example is the same that the basic sample in Visual Basic done in part 28 of this series. If you run the example, you will obtain the following message:

Debugging Example

Sometimes, there are strange messages that are difficult to fix and to find out the reason. For this purpose, we will teach how to debug the SSIS scripts. This topic is very easy for people with programming skills and we tried to write this example as simple as possible.

In order to force an error, do the following steps:

1. Change this section of the code:

DM_Server.Connect("Data Source=infra4;Initial Catalog=AdventureWorksDW2014Multidimensional-EE");

With this code:

DM_Server.Connect("Data Source=local:2382;Initial Catalog=AdventureWorksDW2014Multidimensional-EE");

2. The new code will generate an error. Run the script. You will receive an error message similar to this one:

3. In order to find out the problem we will debug the program. In order to debug, click on the gray line at the left of the script on the line of code where you want to start debugging. A red circle will appear. The debugging will allow seeing line by line the progress of the execution of the script. Save the script and run the SSIS package.

4. In the Debug menu, there are several options like the Step Into, Step Over and Step Out. The Step Into, execute the code one statement at the time. The Step Over, is similar than the step into, but this one is used when the statement has a call procedure. Finally the step Out executes a procedure as a single unit and goes to the next statement of the next procedure.

5. In order to watch the variables in the debugging process, go to DEBUG>Windows>Watch>Watch 1

6. You will have the following Window. Add the DM_Server and the AS_Database.variables included in the script code.

7. Press F11 to step into the code. To a new line.

8. You will be able to see the Variable values of the DM_Server

9. Press F11 again to step into the code.

10. Now, you will be able to see the AS_Database variable properties.

11. Press F11 again and you will be able to see the error message.

12. As you can see, if you debug you can see which line of code is causing the problem. It is very useful and it safes a lot of time. You can also watch the variables and how the values change while the code runs step by step. Now let's add some code to handle the errors.

Error handling

As a best practice, it is good to use the try and catch sentences in order to handle the errors produced by the code. The syntax is the following:

  try
  {
   Code to execute
  }
  catch (Exception ex)
  {
   what to do if the error is produced
  }

The try includes the code to execute and the catch handles the response to the error. For more information about try and catch, see the try catch reference.

1. In our example, the code would be like this.

 try
            {
                // TODO: Add your code here
                //Add the server
                Server DM_Server = new Server();

                //Add the database
                Database AS_Database = new Database();

                //Connect to the Data Mining 
                DM_Server.Connect("Data Source=local:2382;Initial Catalog=AdventureWorksDW2014Multidimensional-EE");

                //Get the AS Database
                AS_Database = DM_Server.Databases["AdventureWorksDW2014Multidimensional-EE"];

                //Show the database name
                MessageBox.Show("Database: " + AS_Database.Name);

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
}

2. If you run the script with the try and catch code, you will have the following error message.

3. As you can see, the error message is now clear and easier to understand.

A Sample to Run Mining Structures That Are Out of Date

The last example verifies the last process date of a mining structure and compares with the current date. If the number of days that the Mining Structure was not processed is higher than 5, the Mining Structure is processed. 

1. First, copy this code to the task script.

            try
            {
                string lastProcessDate;
                // TODO: Add your code here

                //Add the server
                Server DM_Server = new Server();
                //Add the database
                Database AS_Database = new Database();

                //Connect to the Data Mining 
                DM_Server.Connect("Data Source=infra4;Initial Catalog=AdventureWorksDW2014Multidimensional-EE");

                //Get the AS Database
                AS_Database = DM_Server.Databases["AdventureWorksDW2014Multidimensional-EE"];

                //Show the database name
                lastProcessDate = AS_Database.MiningStructures[0].LastProcessed.ToString();
   
                DateTime pd = Convert.ToDateTime(lastProcessDate);
                DateTime Now = DateTime.Now;

                //Get the total days that the Mining structure was not processed
                int diff = Convert.ToInt32((Now - pd).TotalDays);

                //If the number of days of last process is higher than 5, then process the structure
                if (diff > 5)
                {
                    AS_Database.MiningStructures[0].Process();
                }
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

In the code, the lastProcessDate store the date of the last time that the Mining Structure was processed.

lastProcessDate = AS_Database.MiningStructures[0].LastProcessed.ToString();

Using the SSMS, you can watch the Last Processed date:

You then convert the string to date.

DateTime pd = Convert.ToDateTime(lastProcessDate);

The diff stores the number of days that the Mining structure was not stored.

int diff = Convert.ToInt32((Now - pd).TotalDays);

2. Now, run the script.

3. If you check in the SSMS, the Mining Structure last processed property was updated.

Conclusion

In this chapter, we saw how to debug the SSIS Script task, how to watch the variables and how to handle errors. Finally, we saw how to detect the Mining Structures out of date and how to process it again.

References

https://msdn.microsoft.com/es-es/library/0yd65esw.aspx

https://msdn.microsoft.com/en-us/library/ms136033.aspx

 

This article is part of the series A Data Mining Introduction:

Total article views: 2413 | Views in the last 30 days: 2
 
Related Articles
FORUM

Cube Process Error -- Urgent

Error while processing cube

FORUM

SSAS CUBE PROCESSING ERROR

SSAS Cube processing error

ARTICLE

Streamlining the Database Server Recovery Process

Are you tired of manually restoring each database on a new server when the original server has a mel...

FORUM

SSIS debugging

SSIS: Execute process task using GPG works in Debug, not in an Agent Job.

ARTICLE

Debugging SQL Server

This week we have a more technical poll as Steve Jones wonders how you best debug your T-SQL code.

Tags
amo    
c#    
data mining    
 
Contribute