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.
