Tuesday, 22 April 2014

SSIS integration with Dynamics CRM using ExecuteMultipleRequest for bulk operations

There are several tutorials on the Web explaining how to integrate SSIS with Dynamics CRM using the script component. All of them however show you only the basic setup, where records from a data source are processed 1 by 1 when executing CRM commands (e.g. creating CRM records). In this post I would like to show you have to leverage the ExecuteMultipleRequest class from CRM SDK to create bulk operations for records from the SSIS data source.

Tutorial scenario

  1. At first we will create a simple database with 1 table that stores user names
  2. Then we will create an SSIS project
  3. Next, we will add our db table as data source, so SSIS can read information about users
  4. Then, we will add a script component that creates contacts in CRM for each user from the table
  5. Finally, we will modify the script to import CRM contacts in batches
  6. At the end we will compare execution time of both scripts

Basic setup

Database
Let's create a basic db table with only 2 columns:
CREATE TABLE Users (
 FirstName VARCHAR(100) NOT NULL,
 LastName VARCHAR(100) NOT NULL
 )
Now populate your table with some dummy data, in my case I've added 1000 records.

SSIS project
  1. Open "Sql Server Data Tools" (based on Visual Studio 2010)
  2. Got to File -> New -> Project...
  3. Select "Integration Services Project", provide project name and click OK
  4. When the project is created add a Data Flow task to your main package:
Data Source
  1. Double click your Dat Flow task to open it
  2. Double click "Source Assitance" from the toolbox
  3. On the first screen of the wizard select "SQL Server" as source type and select "New..."
  4. On second screen provide you SQL server name and authentication details and select your database
  5. A new block will be added to you Data Flow, representing your DB table. It has an error icon on, cause we haven't selected the table yet. Also, you will see a new connection manager representing you DB connection:
  6. Double click the new block, from the dropdown select the Contacts table we created and hit OK. The error icon should disappear
Script component
  1. Drag and drop the Script Component from the toolbox to you Data Flow area
  2. Create a connection (arrow) from your data source to your script:
  3. Double click your script componet to open it
  4. Go to "Input Columns" tab and select all columns
  5. Go to "Inputs and Outputs" tab and rename "Input 0" to "ContactInput"

1-by-1 import

Now that we have basic components setup let's write some code! In this step we will create a basic code for importing Contacts into CRM. I'm assuming you have basic knowledge of CRM SDK, therefore the CRM specific code will not be explained in details.

Open the script component created in the previous steps and click "Edit Script...". A new instance of Visual Studio will open with a new, auto-generated script project. By default the main.cs file will be opened - this is the only file you need to modify. However, before modyfing the code you need to add references to following libraries:

  • Microsoft.Sdk.Crm.Proxy
  • Microsoft.Xrm.Client
  • Microsoft.Xrm.Sdk
  • Microsoft.Runtime.Serialization
Now we are ready to write the code. Let's start by creating a connection to you CRM organization. This will be created in the existing PreExecute() method like this:
OrganizationService _service;

public override void PreExecute()
{
    base.PreExecute();
        
    var crmConnection = CrmConnection.Parse(@"Url=https://******.crm4.dynamics.com; Username=******.onmicrosoft.com; Password=*********;");
    _service = new OrganizationService(crmConnection);
}
Now that we have the connection created let's write code, that actually imports our contacts to CRM. This can be done be modyfing the existing method ContactInput_ProcessInputRow:
public override void ContactInput_ProcessInputRow(ContactInputBuffer Row)
{
    var contact = new Entity("contact");
    contact["firstname"] = Row.FirstName;
    contact["lastname"] = Row.LastName;
    _service.Create(contact);
}
Obviously the code above requires some null-checks, error handling etc but in general that's all you need to do in order to import your contacts into CRM. If you close the VS instance with the script project it will be automatically saved and built.

You can now hit F5 in the original VS window to perform the actual migration.

Bulk import

In the basic setup described above there is 1 CRM call for each record passed to the script component. Calling web services over the network may be a very time consuming operation. CRM team is aware of that and that is why they introduced the ExecuteMultipleRequest class, which basically allows you to create a set of CRM requests on the client side and send them all at once in a single web service call. In response you will receive an instance of the RetrieveMultipleResponse class, allowing you to process response for each single request.

Let's modify the script code to leverage the power of the ExecuteMultipleRequest class. To do that overwrite the ContactInput_ProcessInput method. The default method implementation can be found in the ComponentWrapper.cs file and it as simple as this:

 public virtual void ContactInput_ProcessInput(ContactInputBuffer Buffer)
{
     while (Buffer.NextRow())
     {
        ContactInput_ProcessInputRow(Buffer);
     }
}
As you can see by default it calls the ContactInput_ProcessInputRow method that we implemented in the previous step for each record from the source. We need to modify it, so it creates a batch of CRM requests and then send it to CRM at once:
List<Entity> _contacts = new List<Entity>();

public override void ContactInput_ProcessInput(ContactInputBuffer Buffer)
{
    int index = 0;
    while (Buffer.NextRow())
    {
        _contacts.Add(GetContactFromBuffer(Buffer));
        index++;

        // Let's use buffer size 500. CRM allows up to 1000 requests per single call
        if (index == 500)
        {
            ImportBatch();
            index = 0;
        }
    }
    ImportBatch();
}

private void ImportBatch()
{
    if (_contacts.Count > 0)
    {
        // Create and configure multiple requests operation
        var multipleRequest = new ExecuteMultipleRequest()
        {
            Settings = new ExecuteMultipleSettings()
            {
                ContinueOnError = true, // Continue, if processing of a single request fails
                ReturnResponses = true // Return responses so you can get processing results
            },
            Requests = new OrganizationRequestCollection()
        };

        // Build a CreateRequest for each record
        foreach (var contact in _contacts)
        {
            CreateRequest reqCreate = new CreateRequest();
            reqCreate.Target = contact;
            reqCreate.Parameters.Add("SuppressDuplicateDetection", false); // Enable duplicate detection 
            multipleRequest.Requests.Add(reqCreate);
        }

        ExecuteMultipleResponse multipleResponses = (ExecuteMultipleResponse)_service.Execute(multipleRequest);            

        // TODO: process responses for each record if required e.g. to save record id

        _contacts.Clear();
    }
}

private Entity GetContactFromBuffer(ContactInputBuffer Row)
{
    Entity contact = new Entity("contact");
    contact["firstname"] = Row.FirstName;
    contact["lastname"] = Row.LastName;
    return contact;
}

Execution time comparison

As you can see the code for sending requests in batches is a bit longer (but still quite simple I believe) so you may be tempted to go with the simpler version. If you don't care about performance too much (little data, no time limitations) then it might be the way to go for you. However, it's always better to know your options and take a conscious decision. SSIS packages usually process large amount of data, which often takes a lot of time. If you add additional step performing CRM operations via CRM SDK (i.e. via CRM web services) you may be sure this will affect significantly the execution time.

I've measured the execution time for both methods. Importing 1000 contacts into CRM took:

  • 1-by-1 - 2:22s
  • Bulk import - 0:44s
In my simple scenario bulk import was 3x faster than 1-by-1. The more data you send to CRM the bigger the difference may be.

Thursday, 17 April 2014

C#: Retrieve user data from Active Directory

The code snippet below shows how to retrieve user information from ActiveDirectory using the PrincipalSearcher class:
var context = new PrincipalContext(ContextType.Domain, "yourdomain.com");
var user = new UserPrincipal(context);

// search by alias
user.SamAccountName = "useralias";

// You can also search by other properties e.g. Display Name
//user.DisplayName = "John Doe";

// perform the search 
var search = new PrincipalSearcher(user);
user = (UserPrincipal)search.FindOne();
search.Dispose();

if (user != null) {
   Console.WriteLine(user.DistinguishedName);
} else {
   Console.WriteLine("No user found");
}

Searching across multiple domains

The code above will search for users in the specified domain only. However, you will often want to search across multiple domains. In that case you will need to provide the parent domain name together with appropriate port. Let's say you have a hierarchy like this:
corp.xxx.com
  - domain1.corp.xxx.com
  - domain2.corp.xxx.com
  - ...
To search across all children of the corp.xxx.com domain construct your PrincipalContext like this:
var context = new PrincipalContext(
                      ContextType.Domain,
                      "corp.xxx.com:3268",
                      "DC=corp,DC=xxx,DC=com");

Tuesday, 24 December 2013

WPF DataGrid - Custom template for generic columns

Recently I had to bind a WPF DataGrid to a System.Data.DataSet. This is quite straightforward and there are many tutorials on how to achieve this.

By default all table columns are auto-generated using 4 predefined templates (Text, Hyperlink, CheckBox, and ComboBox) that support read-only and edit modes. If you wish to customize the way some columns are rendered you can also define a custom template and assign it to some columns by hooking into the AutoGeneratingColumns event of the DataGrid as described here.

Problem with generic columns

As you can see creating custom templates for columns is pretty straightforward as long as column names are fixed. If your WPF app uses a table that doesn't change dynamically you are all good. The problem starts when you use your datagrid to display tables, whose columns` names change e.g. tables loaded from a file at runtime. This is because you can't use the column name in your custom template.

Solution 1 - Create template programmatically

In this solution you build your custom template in code and assign it to the chosen column at runtime, in the AutoGeneratingColoumn event handler.
private void DataGrid_AutoGeneratingColumn(object sender, DataGridAutoGeneratingColumnEventArgs e)
{
    // First get the corresponding DataColumn
    var colName = e.PropertyName;
    var table this.DataContext as DataTable;
    var tableColumn = table.Columns[colName];

    // choose columns to customize e.g. by type
    if (YOUR CONDITION E.G. COLUMN TYPE)
    {
       var templateColumn = new DataGridTemplateColumn();
       templateColumn.Header = colName;
       templateColumn.CellTemplate = this.BuildCustomCellTemplate(colName);
       templateColumn.SortMemberPath = colName;
       e.Column = templateColumn;
    }
}

// builds custom template
private DataTemplate BuildCustomCellTemplate(string columnName)
{
    var template = new DataTemplate();

    var button = new FrameworkElementFactory(typeof (Button));
    template.VisualTree = button;

    var binding = new Binding();
    binding.Path = new PropertyPath(columnName);
     
    button.SetValue(ContentProperty, binding);

    return template;
}
The code above would create the following template for selected columns:

    
Obviously this is just an example - in real life you would need more than just a button that does nothing. In your code you can define full templates, use binding converters, assign commands etc. However, the code gets pretty complex. Therefore this solution is suitable for simple templates.

Solution 2 - Create template skeleton

Alternatively, you can create the template skeleton in XAML and replace all bindings in your event handler:


    
And the event handler:
private void DataGrid_AutoGeneratingColumn(object sender, DataGridAutoGeneratingColumnEventArgs e)
{
    (...)

    if (YOUR CONDITION E.G. COLUMN TYPE)
    {
       // Create wrapping template in code and populate all bindings accordingly 
       string xaml = @"";
       var template = (DataTemplate)XamlReader.Load(string.Format(xaml, "{Binding " + colName + "}", "{StaticResource customCellTemplate}"));
       templateColumn.CellTemplate = template;
       
       (...)
    }
}
The advantage of this approach is that you can create more complex templates in XAML and in your event handler code only populate all required bindings. The limitation of this method is that the custom template needs to be defined at the application level. I found this solution here.

Monday, 2 December 2013

Claims based authorization in MVC4

Recently I worked on a sample MVC4 application that was using Claims based authentication. I used the Identity and Access Visual Studio extension to help me configuring Windows Identity Foundation (WIF) in my app. In short, the tool updates your web.config by adding sections system.identityModel and system.identityModel.services to enable WIF. In result, my application is redirecting all unauthenticated users to my Identity Provider, which then generates a security token that is returned back to my app.

Once I had the authentication part done I started working on the authorization. I wanted it to be role-based i.e. very similar to what you use by default in the default MVC model:

[Authorize(Roles = "Administrator")]
public class AdminController : Controller
{
    // Controller code here
}
In theory, if your Identity Provider issues a token containing the Identity Role claim (http://schemas.microsoft.com/ws/2008/06/identity/claims/role) with the value of user's current role the above default authorization code should work. And it actually does! This is because some basic claims from the token are automatically used to populate the user's identity object, including roles. So when your app's authorization code checks user's role it will use values provided in the token (if any were provided).

Membership database issue

The above solution worked fine for me at the beginning. What I was not aware of is the fact that, by default, the Authorize attribute also connects to you Membership database, regardless the token content. By default as membership database MVC uses the local ASPNETDB.mdf file. I realized that when I moved the application to a different server, without moving the mdf file. Suddenly I started getting the following SQL exception when calling the Authorize attribute:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
I guess there is an easy way to configure ASP not to connect to the database if roles are provided in the token. However, I decide to take a different approach to have more control over the code.

Custom authorization attribute

I decided to write a custom Authorization attribute, that would search for user's role directly in claims provided in the token:
public class ClaimsAuthorizeAttribute : AuthorizeAttribute
{
    private string claimType;
    private string claimValue;

    public ClaimsAuthorizeAttribute(string type, string value)
    {
        this.claimType = type;
        this.claimValue = value;
    }
  
    public override void OnAuthorization(AuthorizationContext filterContext)
    {
        var identity = (ClaimsIdentity)Thread.CurrentPrincipal.Identity;
        var claim = identity.Claims.FirstOrDefault(c => c.Type == claimType && c.Value == claimValue);
             
        if (claim != null)
        {
            base.OnAuthorization(filterContext);
        }
        else
        {
             base.HandleUnauthorizedRequest(filterContext);
        }
    }
} 
This approach is more flexible as it allows me to use different types of claims for authorization in future, not only role. The usage of the attribute is still very simple:
[ClaimsAuthorize(ClaimTypes.Role, "Administrator")]
public class AdminController : Controller
{
    // Controller code here
}

Additional notes

When using claims based authorization it is often advised to use the existing ClaimsPrincipalPermission attribute together with the configured ClaimsAuthorizationManager. In my case this seemed like an overkill, especially that I wanted to keep the code similar to the default authorization model.

Saturday, 14 September 2013

OMPM - SQL-DMO Install Required

When trying to run the Office Migration Planning Manager for Office 2010 on a machine that has Sql Server 2008 or later installed you will often get the following error message:

SQL-DMO Install Required: This operation requires the SQL Server ODBC Driver, version 3.80 or later, which comes with SQL Server 2000, SQL Server 2005 and SQL Server Express.

This is caused by a missing dependency - SQL-DMO has been deprecated and is no longer part of the Sql Server. When you search the Web looking for solution you will be advised to install the "Backward Compatibility Components" which are part of the "Feature Pack for Microsoft SQL Server 2005". So I did. However, this caused another error:

Runtime Error!
(...)
R6034
An Application has made an attempt to load the C runtime library incorrectly.
Please contact the application's support Sql server team for more information.

Investigating this took me some time. It came out that when you search for "Feature Pack for Microsoft SQL Server 2005" in the Microsoft Download Center the first result you get is actually outdated. There is another download link at the bottom of the search results list, that points to the latest version:

Correct version: Feature Pack for Microsoft SQL Server 2005 SP4.

Installing this one fixed all issues and allowed me to run the OMPM. I had a 64x version of Sql Server 2008 R2.

Friday, 31 May 2013

How to read and write Excel cells with OpenXML and C#

Recently I needed to update an Excel spreadsheet and then retrieve some recalculated values. The tricky part was that some cells that I needed to retrieve information from were formula cells (e.g. =A1+5). I needed to update some other cells first and then get the value of recalculated formula.

Unfortunately, this is not directly possible with OpenXML. If you simply try updating some cells and then retrieving the relying ones, you will get the original values for those cells, not recalculated. . This is because formula cells don't store any values, just... formulas. You can only force recalculation by opening your document in the Excel application.

Knowing this I implemented a Refresh method, that opens the Excel app in background and then closes it immediately and saves changes. Below I present my sample code.

Prerequisites
In order to compile the following code you will need the Microsoft.OpenXML SDK 2.0 (DocumentFormat.OpenXML NuGet package) and reference to Microsoft.Office.Interop.Excel (used for opening the Excel app to recalculate formulas).

Solution
Let's start with an interface for my ExcelDocument class:

/// <summary>
/// Interface defining Excel Document methods
/// </summary>
public interface IExcelDocument
{
    /// <summary>
    /// Reads a value of a spreadsheet cell
    /// </summary>
    /// <param name="sheetName">Name of the spreadsheet</param>
    /// <param name="cellCoordinates">Cell coordinates e.g. A1</param>
    /// <returns>Value of the specified cell</returns>
    CellValue ReadCell(string sheetName, string cellCoordinates);

    /// <summary>
    /// Updates a value of a spreadsheet cell
    /// </summary>
    /// <param name="sheetName">Name of the spreadsheet</param>
    /// <param name="cellCoordinates">Cell coordinates e.g. A1</param>
    /// <param name="cellValue">New cell value</param>
    void UpdateCell(string sheetName, string cellCoordinates, object cellValue);

    /// <summary>
    /// Refreshes the workbook to recalculate all formula cell values
    /// </summary>
    void Refresh();
}
Once we have the interface we need its implementation:
public class ExcelDocument : IExcelDocument
{
    private readonly string _filePath;

    public ExcelDocument(string filePath)
    {
        _filePath = filePath;
    }

    /// <see cref="IExcelDocument.ReadCell" />
    public CellValue ReadCell(string sheetName, string cellCoordinates)
    {
        using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(_filePath, false))
        {
            Cell cell = GetCell(excelDoc, sheetName, cellCoordinates);
            return cell.CellValue;
        }
    }

    /// <see cref="IExcelDocument.UpdateCell" />
    public void UpdateCell(string sheetName, string cellCoordinates, object cellValue)
    {
        using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(_filePath, true))
        {
            // tell Excel to recalculate formulas next time it opens the doc
            excelDoc.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
            excelDoc.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
            
            WorksheetPart worksheetPart = GetWorksheetPart(excelDoc, sheetName);
            Cell cell = GetCell(worksheetPart, cellCoordinates);
            cell.CellValue = new CellValue(cellValue.ToString());
            worksheetPart.Worksheet.Save();
        }
    }

    /// <summary>Refreshes an Excel document by opening it and closing in background by the Excep Application</summary>
    /// <see cref="IExcelDocument.Refresh" />
    public void Refresh()
    {
        var excelApp = new Application();
        Workbook workbook = excelApp.Workbooks.Open(Path.GetFullPath(_filePath));
        workbook.Close(true);
        excelApp.Quit();
    }

    private WorksheetPart GetWorksheetPart(SpreadsheetDocument excelDoc, string sheetName)
    {
        Sheet sheet = excelDoc.WorkbookPart.Workbook.Descendants<Sheet>().SingleOrDefault(s => s.Name == sheetName);
        if (sheet == null)
        {
            throw new ArgumentException(
                String.Format("No sheet named {0} found in spreadsheet {1}", sheetName, _filePath), "sheetName");
        }
        return (WorksheetPart) excelDoc.WorkbookPart.GetPartById(sheet.Id);
    }

   private Cell GetCell(SpreadsheetDocument excelDoc, string sheetName, string cellCoordinates)
    {
        WorksheetPart worksheetPart = GetWorksheetPart(excelDoc, sheetName);
        return GetCell(worksheetPart, cellCoordinates);
    }

    private Cell GetCell(WorksheetPart worksheetPart, string cellCoordinates)
    {
        int rowIndex = int.Parse(cellCoordinates.Substring(1));
        Row row = GetRow(worksheetPart, rowIndex);

        Cell cell = row.Elements<Cell>().FirstOrDefault(c => cellCoordinates.Equals(c.CellReference.Value));
        if (cell == null)
        {
            throw new ArgumentException(String.Format("Cell {0} not found in spreadsheet", cellCoordinates));
        }
        return cell;
    }

    private Row GetRow(WorksheetPart worksheetPart, int rowIndex)
    {
        Row row = worksheetPart.Worksheet.GetFirstChild<SheetData>().
                                Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
        if (row == null)
        {
            throw new ArgumentException(String.Format("No row with index {0} found in spreadsheet", rowIndex));
        }
        return row;
    }
}
I hope the code is self-explanatory and doesn't require more comments. You can optimize it for you needs e.g. when updating/reading multiple cells at once you may want to open the doc only once. Currently my code opens it and closes for each read/update request.

Wednesday, 22 May 2013

Preparation materials for MCSD Web Applications certification

I'm happy to announce that today I've become a Microsoft Certified Solutions Developer (MCSD) in Web Applications area. To achieve that I needed to pass 3 exams. Here is my short summary of each of them together with some useful preparation materials:

70-480 - Programming in HTML5 with JavaScript and CSS3

This exam begins your journey to the MCSD certificate. In general it covers exactly what is named in its title: HTML5 & CSS3. You could also use some jQuery knowledge. If you are a web developer with multiple years of experience this should be a piece of cake for you. In case you need to refresh your memory on some topics I recommend watching the free video tutorial at the Microsoft Virtual Academy:

70-486 - Developing ASP.NET MVC 4 Web Applications

This exam tests your knowledge of the Asp.Net MVC4 framework. To be honest I can't really remember if it includes WebApi questions, but it's worth to learn it anyway, as it's required for the last exam. Again, if you worked on several MVC4 projects there is nothing to be afraid of.

Before I took this exam I browsed the following book to make sure I'm not missing anything:


70-487 - Developing Windows Azure and Web Services

For me this was the hardest exam. It is because it covers a wide range of different topics. All required technologies are somehow related, but at the same time they are independent frameworks:
  • Windows Azure
  • WCF
  • MVC4 WebApi
  • Entity Framework
  • Other Data Access
Despite the exam`s title I was under impression that there were not that many questions related to Windows Azure. The basic overview of Azure features would suffice to answer most of them. There was a lot of questions related to WCF & Data Access though. Luckily there are excellent study guides available. Here are 2 that I liked most:
  • Study Guide #1
    My personal favourite; it relays strongly on pluralsight video trainings, which are usually very good.
  • Study Guide #2
    A nice alternative for those of you who don't have access to pluralsight. Most links are referencing free online materials.
If you read/watch all linked materials you will be good to go ;) Good luck future MCSDs!