Microsoft CRM Integration: Siebel E-Mail Attachments Import - C# and MS Transact SQL Example

Microsoft CRM is the CRM answer from Microsoft Business Solutions. It has aggressive pricing and the prices are going down, making it affordable for small companies. I see cases when Microsoft CRM replaces such traditional CRM systems as Siebel. It is not necessary that clients decide to replace it themselves; they may be victims of their systems. The example: Great Plains had an alliance with Siebel several years ago and you saw multiple clients with the Great Plains-Siebel tandem. Now, Great Plains is integrated with MS CRM.

This article is for the programmer who needs data to be migrated from Siebel or other CRM to MS CRM.

Today's topic is Siebel e-mails. They are stored in the files and then imported into a MS CRM database. Each message exists in the form of a separate file in the import directory. You will use a custom SQL table, created in MS SQL Server 2000:

if exists (select * from dbo.sysobjects where id =
   object_id(N"[dbo].[CrmAttachImporter]") and
   OBJECTPROPERTY(id, N"IsUserTable") = 1)
drop table [dbo].[CrmAttachImporter]
GO

CREATE TABLE [dbo].[CrmAttachImporter] (
   [Id]  uniqueidentifier ROWGUIDCOL  NOT NULL ,
   [CrmActivityGuid] [varchar] (50) NOT NULL ,
   [MsgFileName] [varchar] (2000) NOT NULL
) ON [PRIMARY]
GO

Comments about this table: Its goal is storing the MS CRM activity GUID relationship to a file name with an e-mail attachment (e-mail message) that needs to be attached to the activity. You will store the activity GUID in the CrmActivityGuid field and file name in the import directory of the attachment in the MsgFileName field.

The configuration file for the utility will be the following:

<config>
   <connectionString>provider=SQLOLEDB;
                     Initial Catalog=Albaspectrum;
                     Data Source=MSSQL1;
                     User Id=sa;Password=sa;</connectionString>
   <msgFolder>data</msgFolder>
   <tableName>CrmAttachImporter</tableName>
   <activityGuidColumn>CrmActivityGuid</activityGuidColumn>
   <msgFileNameColumn>MsgFileName</msgFileNameColumn>
</config>

Here, you described the MS SQL Server connection string, the path to messages-files in the file system, the name of the table that stores the relations Activity GUID and file names, and table column names. These are required for the import procedure.

To control the import process, you will use a free logging library for .NET: log4net. You can get it here: http://logging.apache.org/log4net/.

Now, look at the method of potential attachments catalog scanning:

public void scanFolder() {
   log = LogManager.GetLogger(typeof(AttachImporter));
   DOMConfigurator.Configure(new FileInfo("log.config"));

   try {
      DirectoryInfo dirInfo = new DirectoryInfo(msgFolder);
      FileInfo[] files = dirInfo.GetFiles();
      Hashtable emails = new Hashtable();

      foreach (FileInfo fileInfo in files) {
         log.Debug("Analizing file: " + fileInfo.Name);

         Guid activityId = GetActivityIdByFileName(fileInfo.Name);

         if (! activityId.ToString().Equals(new Guid().ToString())) {
            emails.Add(activityId, fileInfo.DirectoryName + @"\" +
                       fileInfo.Name);

            Console.WriteLine("Marked for import: " + fileInfo.Name);
            log.Debug("Marked for import: " + fileInfo.Name);
         }
         else {
            Console.WriteLine("Not found in activity import list: " +
                              fileInfo.Name);
            log.Debug("Not found in activity import list: " +
                      fileInfo.Name);
         }
      }

   ProcessMessages(emails);
   }
   catch (Exception e) {
   Console.WriteLine(e.Message + "\r\n" + e.StackTrace);
   }
}

The central place in this method checks on the relationship existence in the import table for CRM Activity GUID and file name in the import directory:

private Guid GetActivityIdByFileName(string fileName) {
   //create the database connection
   OleDbConnection conn = new OleDbConnection(connectionString);

   conn.Open();

   //create the command object and store the sql query
   OleDbCommand command = conn.CreateCommand();

   command.CommandText = "SELECT " + activityGuidColumn + ", " +
                         msgFileNameColumn + " FROM " + tableName +
                         " WHERE UPPER(LTRIM(RTRIM(" +
                                       msgFileNameColumn + "))) =
                         UPPER(LTRIM(RTRIM(?)))";

   log.Debug("Preview checking SQL query: " + command.CommandText);
   log.Debug("Using file name: " + fileName);

   command.Parameters.Add(new OleDbParameter(msgFileNameColumn,
                                             fileName));

   //create the datareader object to connect to table
   OleDbDataReader reader = command.ExecuteReader();

   if (reader.Read()) {
      Guid activityGuid = new Guid(reader.GetString(0));

      reader.Close();
      conn.Close();

      return activityGuid;
   }
   else {
      reader.Close();
      conn.Close();

      return new Guid();
   }
}

Importing the messages cache is transferred as a parameter to the method, which does an attachment import into MS CRM:

   private void ProcessMessages(Hashtable emails)
   {
      try
      {
         log.Debug("Start importing process");

         CRMConnector crmConnector = new CRMConnector();

// Connect to CRM DB
crmConnector.SetCrmConfigPath(Environment.SystemDirectory +
                              "/Albaspectrum/MSCRMGateway.xml");
crmConnector.SetCrmContentType(Environment.SystemDirectory +
                               "/Albaspectrum/ContentType.txt");
crmConnector.Connect(log);

         if (emails != null) {
            ICollection keys = emails.Keys;
            int attCounter = 0;

            foreach (Guid o in keys) {
            string attName = (string)(emails[o]);

            crmConnector.AddAttachmentToActivity(o, attName,
                         (new FileInfo(attName)).Length,
                         attCounter);

               attCounter++;
            }
         }

         crmConnector.Close();
      }
      catch (Exception ex)
      {
         log.Debug(ex.Message + "\n" + ex.StackTrace);
      }
   }

All the required classes for working with MS CRM Activity are stored in the MSCRMGateway library and described in these articles:

Boris Makushkin is a lead software developer with Alba Spectrum Technologies, a USA nationwide MS CRM consulting company.



Comments

  • Cheap Oakley Rada Path 90% off sale

    Posted by xgbbiuqam on 07/09/2013 02:31am

    fake ray ban sunglasses ,Today, Oakley, sunglasses happen to be more than merely a power tool to block the sun's rays, but additionally plays another role - decoration. Oakley sunglasses, black frame clear lens for cheap Oakley sunglasses design in Asia greater than other ways is a good example. There is the range to consider without notice life-style completely, the only real gentleman, Oakley sunglasses five pillows polarization traditional glowing blue-black, and unisex. Replica Oakley Radar ,The short wavelength light barrier, Oakley sunglasses and also other protective effects around the lenses of sunglasses red line is a lesser amount than one other four colors. This phenomenal facility should be reduced as a stimulus, when you are in the downward direction. Custom spa facilities and also a light alloy frame, the real Oakley sunglasses, and feel super comfortable. Oakley is very perfect! However , there a wide range of artisans top brand sunglasses, Oakley accept to take their very own some time to has been added is usually strange architecture and innovative distance limitations. The outer skin and eyes at the photovoltaic ultraviolet (UV) risk. Here is the well-known experience UV sun, might cause a burning sun damage, and ultimately cancer of the skin. Fake Oakleys ,When you're in out, it is advisable to prevent harmful UV sunglasses, you really should have a very sunglasses to guard your eyes. Oakley snow goggles are never the COS of Fashion Eye safety and fashion reasons. Sales representative art of Oakley sunglasses and fashion. ray ban outlet ,Oakley shades of a good quality (specially the "surround variety), major safety and solar energy, ultraviolet within the eye to offer, they are suitable for people of all ages. Brand the cause from the story provides each Oakley's rich breath of life, its culture and outlook on life was deeply moved. longchamp sac ,Oakley hydrophobic, oleophobic technology, high-performance coatings, water slide out, which keeps how well you see clear, and overcome the stain from skin oils and lotions. In addition to these specific polarization picture bonding strategy is generally a significant lead, and employ of injection in order to position the two materials. Under normal circumstances, it reduces the anxiety with the eyes and retention of girl squinting. Only of these supplier is just not 100% satisfied, sunglasses warehouse for the pair of sunglasses or return the purchase price.

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: October 29, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT Are you interested in building a cognitive application using the power of IBM Watson? Need a platform that provides speed and ease for rapidly deploying this application? Join Chris Madison, Watson Solution Architect, as he walks through the process of building a Watson powered application on IBM Bluemix. Chris will talk about the new Watson Services just released on IBM bluemix, but more importantly he will do a step by step cognitive …

  • Live Event Date: October 29, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT It's well understood how critical version control is for code. However, its importance to DevOps isn't always recognized. The 2014 DevOps Survey of Practice shows that one of the key predictors of DevOps success is putting all production environment artifacts into version control. In this eSeminar, Gene Kim will discuss these survey findings and will share woeful tales of artifact management gone wrong! Gene will also share examples of how …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds