Quantcast

Microsoft CRM Integration: Siebel Email Attachments Import – C# and MS Transact SQL example

Get the WebProNews Newsletter:
[ Business]

Microsoft CRM – CRM’s answer from Microsoft Business Solutions has an aggressive pricing, going down and making it affordable for small companies.

We see cases when Microsoft CRM replaces such traditional CRM systems as Siebel. It is not necessary, that clients decided to replace it themselves – they may be victims of their systems – the example: Great Plains had alliance with Siebel several years ago and we saw multiple clients with Great Plains – Siebel tandem. Now Great Plains integrates with MS CRM.

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

Today’s topic is Siebel emails, stored in the files, import into MS CRM database. Each message exists in the form of separate file in the import directory. We will use 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] (
&nbsp&nbsp&nbsp&nbsp [Id] uniqueidentifier ROWGUIDCOL NOT NULL ,
&nbsp&nbsp&nbsp&nbsp [CrmActivityGuid] [varchar] (50) NOT NULL ,
&nbsp&nbsp&nbsp&nbsp [MsgFileName] [varchar] (2000) NOT NULL
) ON [PRIMARY]
GO

Comments to this table – its goal is storing MS CRM activity GUID relationship to file name with email attachment (email message), which needs to be attached to the activity. We will store activity GUID in the field CrmActivityGuid and file name in the import directory of the attachment in the MsgFileName field.
Configuration file for our utility will be the following:

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

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

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

Let’s look at the method of potential attachments catalog scanning:

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

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

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp foreach (FileInfo fileInfo in files) {
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp log.Debug("Analizing file: " + fileInfo.Name);
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp Guid activityId = GetActivityIdByFileName(fileInfo.Name);

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

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

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

Central place in this method is checking on the relationship existence in the import table for CRM Activity GUID and file name in the import directory:

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

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp conn.Open();

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

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

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

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

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

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

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp reader.Close();
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp conn.Close();

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

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp return new Guid();
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp }
&nbsp&nbsp&nbsp&nbsp }

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

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

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp 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);

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

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

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

&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp attCounter++;
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp }
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp }

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

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

http://www.albaspectrum.com/Customizations_Whitepapers/Dexterity_SQL_VBA_Crystal/MSCRMCUstomizationEmailAttachment.htm

http://www.albaspectrum.com/Customizations_Whitepapers/Dexterity_SQL_VBA_Crystal/MSCRMCUstomizationClosedActivity.htm

Boris Makushkin is senior software developer in Alba Spectrum Technologies US nation-wide Great Plains, Microsoft CRM customization company, based in Chicago and having locations in multiple states and internationally (www.albaspectrum.com ), he is Unix, SQL, C#.Net, Crystal Reports, Microsoft CRM SDK and Exchange Server SDK developer. You can reach Boris: borism@albaspectrum.com

Microsoft CRM Integration: Siebel Email Attachments Import – C# and MS Transact SQL example
Comments Off
About Boris Makushkin
Boris Makushkin is senior software developer in Alba Spectrum Technologies US nation-wide Great Plains, Microsoft CRM customization company, based in Chicago and having locations in multiple states and internationally (www.albaspectrum.com ), he is Unix, SQL, C#.Net, Crystal Reports, Microsoft CRM SDK and Exchange Server SDK developer. You can reach Boris: borism@albaspectrum.com WebProNews Writer
Top Rated White Papers and Resources

Comments are closed.