Интеграция CRM с внешними источниками с помощью SQL Server Integration Services (SSIS)

Как следует из названия сегодня рассмотрим возможности службы SQL Server Integration Services для интерграции CRM с другими системами (в этом примере будем загружать Котнтакты в CRM из различных источников, а потом эти же Контакты выгружать в аналогичные источники данных).

Но, прежде чем мы начнем, вот список требуемых компонентов:

• SQL Server 2008 Standard/Enterprise Edition с установленным SQL Integration Service;
• Microsoft Dynamics CRM 4.0;
• Visual Studio 2008 Professional Edition SP 1 с установленными Business Intelligence Tools (поставляются вместе с SQL Server’ом);
• MS CRM SDK.

Подготовка тестовых данных

Для того примера нам понадобятся:

• Два CSV файла:
  • CSVtoCRM.csv, таким набором данных:
    

    Имя	Фамилия	Телефон	E-mail
    CSVимя1	CSVфамилия1	111-11-22	email1
    CSVимя2	CSVфамилия2	111-11-33	email2

    И сохраните его в формате UTF-8;

  • CRMtoCSV.csv, только со строкой заголовка:
    

    Имя

    Фамилия

    Телефон

    E-mail

• Два Excel файла:
  • EXCELtoCRM.xlsx, таким набором данных:
    

    Имя	Фамилия	Телефон	E-mail
    Excelимя3	Excelфамилия3	222-11-22	email3@mail.ru
    Excelимя4	Excelфамилия4	222-11-33	email4@mail.ru

  • CRMtoEXCEL.xlsx, только со строкой заголовка:
    

    Имя

    Фамилия

    Телефон

    E-mail

• Две таблички в какой-нибудь базе данных (в этом примере БД IntegrationSample):
  • toCRM, таким набором данных:
    

    Имя	Фамилия	Телефон	E-mail
    Tableимя5	Tableфамилия5	222-11-55	email5@mail.ru
    Tableимя6	Tableфамилия6	222-11-66	email6@mail.ru

  • fromCRM, только со строкой заголовка:
    

    Имя

    Фамилия

    Телефон

    E-mail

  Для создания табличек и заполнения их данными можете воспользоваться следующим скриптом:

  CREATE TABLE [dbo].[toCRM](
  	[Имя] [varchar](50) NULL,
  	[Фамилия] [varchar](50) NULL,
  	[Телефон] [varchar](50) NULL,
  	[E-mail] [varchar](50) NULL
  )
  GO
    
  INSERT INTO toCRM (Имя, Фамилия, Телефон, [E-mail])
  VALUES ('Tableимя5','Tableфамилия5','222-11-55','email5@mail.ru')
  INSERT INTO toCRM (Имя, Фамилия, Телефон, [E-mail])
  VALUES ('Tableимя6','Tableфамилия6','222-11-66','email6@mail.ru')
  GO
  

Итак, можем приступать… к интеграции…

Из внешних источников в CRM

CSV -> CRM

Этот шаг мы рассмотрим подробно, а остальные «по диагонали», т.к. в целом они очень похожи:

• Запустите Visual Studio 2008 и создайте новый проект Integration Services Project;
• Переименуйте Package.dtsx в CRMintegration.dtsx;
• Перетащите элемент Data Flow Task из панели Toolbox в область Control Flow;

• Дважды щелкните по Data Flow Task, который Вы только что добавили — Вы переместитесь к панели Data Flow Design, в которой необходимо определить источники исходных данных, а также способы их «переброски в пункт назначения» (в данном подпункте – написать скрипт, который будет передавать данные в CRM);
• Перетащить Flat File Source из панели Toolbox в область Data Flow;
• Дважды щелкните по нему, чтобы открыть Flat File Source Editor;
• Нажмите кнопку New, чтобы открыть Flat File Connection Manager, в котором укажите:
  • Имя нового соединения;
  • Путь к CSV файлу CSVtoCRM.csv;
  • Укажите способ разделения столбцов заголовка CSV файла;
  • Поставьте галку Column names in the first data row (что означает – Первая строка содержит заголовки столбцов);
  • Перейдите на страницу Columns и укажите способ разделения столбцов данных.
• Жмите два раза OK;

• Перетащите компонент Script Component в область Data Flow;
• В появившемся диалоговом окне выберите Transformation и OK;
• Соедините эти два объекта, перетащив зеленую стрелку из Flat File Source в Script Component;
• Дважды щелкните по компоненту Script Component, чтобы открыть Script Transformation Editor;
• На странице Input Columns выберите столбцы, которые Вы хотели бы отправить в CRM из CSV файла;
• А на странице Inputs and Outputs удалите свойство Output, так как мы не собираемся ничего выводить в этом примере;
• Вернитесь на страницу Script и нажмите Edit Script — откроется новое окно Visual Studio, в котором необходимо написать кое-какой код;

• Сначала необходимо добавить в Script Component Web Reference на веб-службу CRM Service: щелкните правой кнопкой мыши по Reference на панели Project Explorer — Add Web Reference — добавьте ссылку на службу http://<servername:port>/mscrmservices/2007/crmservice.asmx с именем CrmSdk;
• Чтобы использовать Web Reference на CRM Service в коде, необходимо включить пространство имен CrmSdk в Script Component. Для этого нам сначала необходимо узнать пространство имен Script Component’а: щелкните правой кнопкой мыши на проекте и выбирает Properties — пространство имен находится в поле Default namespace. В этом примере пространство имен Script Component’а равно SC_49f592c073c749b9bd82c131e1a5fe4c.csproj. Соответственно чтобы задействовать CrmSdk в коде необходимо вверху прописать такую строчку:

  using SC_ad0e4b91cb7e48cdb8fa2d240e3e5c30.csproj.CrmSdk;

• А вот и сам код:

  using System;
  using System.Data;
  using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
  using Microsoft.SqlServer.Dts.Runtime.Wrapper;
  using SC_316edbb772d0441a83a23aa17c9f1672.csproj.CrmSdk; // Замените пространство имен на свое
  
  [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
  public class ScriptMain : UserComponent
  {
      // Объяляем Crm Service
      public CrmService myservice = null;
      // Перед просмотром данных из источника
      public override void PreExecute()
      {
          base.PreExecute();
  
          // Настраиваем Crm Service
          CrmAuthenticationToken token = new CrmAuthenticationToken();
          token.AuthenticationType = 0;
          token.OrganizationName = "superfirma";
  
          myservice = new CrmService();
          myservice.Url = "http://win-n22hj23d1b1/mscrmservices/2007/crmservice.asmx";
          myservice.CrmAuthenticationTokenValue = token;
          myservice.Credentials = System.Net.CredentialCache.DefaultCredentials;
      }
      // После просмотра данных из источника
      public override void PostExecute()
      {
          base.PostExecute();
      }
      // Просматриваем каждую строку исходных данных и...
      public override void Input0_ProcessInputRow(Input0Buffer Row)
      {
          // Создаем объект Контакт
          contact cont = new contact();
          // Заполняем его полями из исходных данных
          if (!Row.Имя_IsNull) { cont.firstname = Row.Имя; }
          if (!Row.Фамилия_IsNull) { cont.lastname = Row.Фамилия; }
          if (!Row.Телефон_IsNull) { cont.telephone1 = Row.Телефон; }
          if (!Row.Email_IsNull) { cont.emailaddress1 = Row.Email; }
          // Создаем запись Контакта
          myservice.Create(cont);
      }
  }
  

  Он состоит из трех функций (по умолчанию): PreExecute, Input0_ProcessInputRow (меняется в зависимости от названия свойства входящих данных), PostExecute. Эти три функции соответственно выполняются до непосредвенной обработки входящих данных, во время нее (причем столько раз, сколько есть строк в источнике данных) и после нее. Поэтому мы прежде всего (вне этих функций) объявляем глобальную ссылку на Crm Service, затем в PreExecute настраиваем ее, а при просмотре каждой строки в источнике данных создаем в CRM Контакт (значения для полей которого берем из источника данных). Функцию PostExecute мы здесь никак не используем, так что можете ее удалить (я оставил ее для наглядности 🙂 );

После добавления кода можете сохранять и закрывать дополнительно окно Visual Studio. На данном этапе уже можно проверить работоспособность пакета SSIS, щелкнув правой кнопкой мыши на CRMintegration.dtsx и выбирав Execute Package (или по зеленой стрелке на панели инструментов). Но т.к. этот пакет будет состоять из множества элементов проверим его работоспособность после добавления их всех…

Excel -> CRM

Как уже было сказано, работа с другими источниками данных очень похожа на предыдущий подпункт (а работа со Script Component’ом и вовсе одинакова для данного примера)… работу над которым и продолжим…

• Перетащить Excel Source из панели Toolbox в область Data Flow;
• Дважды щелкните по нему, чтобы открыть Excel Source Editor;
• Нажмите кнопку New, чтобы открыть Excel Connection Manager, в котором укажите путь к файлу EXCELtoCRM.xlsx и его версию. Жмите Ок;
• В окне Excel Source Editor в ниспадающем списке Data access mode выберите Table or view и укажите лист Excel, который содержит данные;
• Перетащите еще один компонент Script Component в область Data Flow;
• В появившемся диалоговом окне выберите Transformation и OK;
• Соедините зеленой стрелкой Excel Source со вторым Script Component’ом;
• Дважды щелкните по второму Script Component, чтобы открыть его Script Transformation Editor;
• На странице Input Columns выберите столбцы, которые Вы хотели бы отправить из файла Excel в CRM;
• На странице Inputs and Outputs удалите свойство Output;
• Вернитесь на страницу Script и нажмите Edit Script — откроется окно Visual Studio, в котором необходимо вставить точно такой же код как и в предыдущем листинге. Как обычно подключите в Script Component Web Reference на CRM Service, а также измените используемое пространство имен Script Component’а. В остальном код остается точно таким же, т.к. имя входящего свойства идентично первому подпункту;

SQL Server Database -> CRM

Продолжаем работу над нашим проектом… осталось подключить последний (для данного примера) входящий источник данных – SQL Server Database:

• Перетащить ADO NET Source из панели Toolbox в область Data Flow;
• Дважды щелкните по нему, чтобы открыть ADO.NET Source Editor;
• Нажмите кнопку New, чтобы открыть ADO.NET Connection Manager, в котором также жмите New;
• В диалоге Connection Manager укажите имя Вашего SQL Server’а и имя БД из которой будете тянуть данные. Два раза Ок;
• В окне ADO.NET Source Editor в ниспадающем списке Data access mode выберите Table or view и укажите SQL таблицу, которая содержит необходимые данные;
• Перетащите еще один (предпоследний 🙂 ) компонент Script Component в область Data Flow;
• В появившемся диалоговом окне выберите Transformation и OK;
• Соедините зеленой стрелкой Excel Source с третьим Script Component’ом;
• Дважды щелкните по нему, чтобы открыть его Script Transformation Editor;
• На странице Input Columns выберите столбцы SQL таблицы, которые хотите перенести в CRM;
• На странице Inputs and Outputs удалите свойство Output;
• Вернитесь на страницу Script и нажмите Edit Script — откроется окно Visual Studio — копируйте в него код из первого листинга (т.к. и здесь он одинаков 🙂 ). Не забудьте только подключить в Script Component Web Reference на CRM Service, а также измените пространство имен Script Component’а на новое.

Ну вроде с импортом данных в CRM мы разобрались… теперь приступим к их экспорту 🙂

Из CRM во внешние источники данных

В отличие от импорта, компонентов для экспорта будет меньше за счет «унифицирования» источника данных (аналогичного результата можно было добиться и при импорте, но пока оставим все как есть – для примера)…

CRM -> CSV

• Перетащите компонент Script Component (последний) в область Data Flow;
• В появившемся диалоговом окне выберите Source и OK;
• Дважды щелкните по нему, чтобы открыть его Script Transformation Editor;
• На странице Inputs and Outputs добавьте к одному существующему выходному параметру еще два. Для каждого из трех свойств добавьте следующие поля: Имя, Фамилия, Телефон, E-mail. И для всех этих полей выберите DataType = Unicode string [DT_WSTR]. Эля этого подпункта нам не понадобятся все три свойства, но т.к. у нас будет только один Script Component для всех экспортируемых данных и в коде (который мы напишем чуть позже) мы задействуем все три свойства, то мы и добавляем их тут;
• Вернитесь на страницу Script и нажмите Edit Script — откроется окно Visual Studio — добавьте туда такой код:

  using System;
  using System.Data;
  using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
  using Microsoft.SqlServer.Dts.Runtime.Wrapper;
  using SC_3d3b044b78094ab884c3c60e9f8ebf7b.csproj.CrmSdk;
  
  [Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
  public class ScriptMain : UserComponent
  {
      public override void PreExecute()
      {
          base.PreExecute();
      }
  
      public override void PostExecute()
      {
          base.PostExecute();
      }
  
      public override void CreateNewOutputRows()
      {
          // Настраиваем CRM Service
          CrmAuthenticationToken token = new CrmAuthenticationToken();
          token.AuthenticationType = 0;
          token.OrganizationName = "superfirma";
  
          CrmService service = new CrmService();
          service.Url = "http://win-n22hj23d1b1/mscrmservices/2007/crmservice.asmx";
          service.CrmAuthenticationTokenValue = token;
          service.Credentials = System.Net.CredentialCache.DefaultCredentials;
  
          // Составляем запрос всех Контактов в CRM
          ColumnSet cols = new ColumnSet();
          cols.Attributes = new string[] { "firstname", "lastname", "telephone1", "emailaddress1" };
  
          QueryExpression query = new QueryExpression();
          query.EntityName = EntityName.contact.ToString();
          query.ColumnSet = cols;
  
          RetrieveMultipleRequest retrieve = new RetrieveMultipleRequest();
          retrieve.Query = query;
  
          // Отправляем запрос в CRM
          RetrieveMultipleResponse retrieved = (RetrieveMultipleResponse)service.Execute(retrieve);
          BusinessEntityCollection Contacts = retrieved.BusinessEntityCollection;
  
          // Просматриваем все возвращенные Контакты
          foreach (contact Contact in Contacts.BusinessEntities)
          {
              // и помещаем их поля в выходящие свойства
              Output0Buffer.AddRow();
              Output1Buffer.AddRow();
              Output2Buffer.AddRow();
              Output0Buffer.Имя = Output1Buffer.Имя = Output2Buffer.Имя = Contact.firstname;
              Output0Buffer.Фамилия = Output1Buffer.Фамилия = Output2Buffer.Фамилия = Contact.lastname;
              Output0Buffer.Телефон = Output1Buffer.Телефон = Output2Buffer.Телефон = Contact.telephone1;
              Output0Buffer.Email = Output1Buffer.Email = Output2Buffer.Email = Contact.emailaddress1;
          }
      }
  }
  

  Как обычно, добавьте Web Reference на CRM Service и замените пространство имен для текущего Script Component’а.
  Вся логика здесь помещена в функции CreateNewOutputRows – здесь мы подключаемся к CRM, запрашиваем все Контакты, и помещаем их в выходные параметры.
  Сохраните и закройте окно редактирования кода;

• Далее перетащите Flat File Destination в область Data Flow. Соедините четвертый Script Component с этим Flat File Destination зеленой стрелкой — появится диалоговое окно выбора выходного свойства — выберите первое (Output 0);
• Дважды щелкните по Flat File Destination;
• Откроется окно Flat File Destination Editor, в котором нужно настроить параметры выходного источника (CSV файла). Нажмите New, в первом далоге выберите Delimited, а во тором (Flat File Connection Manager Editor) настройте параметры подключения к файлу CRMtoCSV.csv (не забудьте на странице Columns указать способ разделения строк данных). Ок;
• В диалоговом окне Flat File Destination Editor перейдите к странице Mapping и задайте сопоставление полей из выходного свойства и полями в CSV файле. Ок.

Ну, вот… первый экспорт готов 🙂

CRM -> Excel

Продолжаем выгружать данные из CRM. Причем остальные две задачи значительно упростились – мы подготовили выходные данные в пердыдущем пункте – осталось настроить приемники этих данных 🙂

• Перетащите элемент Excel Destination из Toolbox в область Data Flow;
• Соедините его с четвертым Script Component’ом зеленой стрелкой — при этом появится окно выбора выходного параметра — укажите второй (Output 1);
• Дважды щелкните Excel Destination, чтобы открыть Excel Destination Editor;
• Жмите New и задайте в диалоге Excel Connection Manager путь к файлу CRMtoEXCEL.xlsx и его версию.
• В окне Excel Destination Editor в ниспадающем списке Data access mode выберите Table or view и укажите лист Excel, в который будем выгружать данные данные из CRM;
• Ну, и последнее… на странице Mappings задайте сопоставление полей.

Уффф… осталось настроить последний экспорт 🙂

CRM -> SQL Server Database

Выгружаем данные в таблицу SQL Server’а…

• Перетащите элемент ADO NET Destination из Toolbox в область Data Flow;
• Перетащите зеленую стрелку из четвертого Script Component’а в ADO NET Destination (автоматически подставится выбор данных из третьего свойства – Output 2);
• Дважды щелкните ADO NET Destination, чтобы открыть ADO NET Destination Editor;
• В этом диалоговом окне необходимо настроит подключение к БД и указать таблицу в кторую будем передавать данные. Т.к. параметры соединения с SQL Server’ом мы уже настроили в подпункте SQL Server Database -> CRM, то просто укажите это существующее подключение и выберите таблицу для выгрузки данных из CRM;
• Потом перейдите на страницу Mappings и настройте сопоставление данных. Ок.

Готово 🙂 самый простой экспорт из всех 🙂

Тестируем: щелкните правой кнопкой мыши по файлу CRMintegration.dtsx в Solution Explorer — Execute Package. После того как все блоки подсветятся зеленым, что означает, что все прошло успешно) Вы должны увидеть в CRM (а также в CSV, Excel’е и таблице SQL Server’а) новые записи!

Осталось все это дело более-менее автоматизировать – а то ручками каждый раз проводит миграцию как-то лень 🙂

Разворачивание SSIS-пакета

Существует несколько способ задействовать пакет SSIS. В данном примере воспользуемся самым простым – SQL Server Agent’ом!

• Откройте SQL Server Management Studio;
• Разверните узел SQL Server Agent’а и щелкните правой кнопкой мыши на папке Jobs — New Job;
• Введите имя нового задания для агента и перейдите на страницу Steps, на которой нам нужно создать шаг запуска пакета интеграции SSIS – жмите New;
• В диалоговом окне введите имя шага, в качестве типа выберите SQL Server Integration Services Package, в качестве источника File System и укажите путь к созданному в пердыдущих шагах пакету. Ок;
• Перейдите на страницу Schedules и задайте необходимое Вам расписание выполнения пакета! Все 🙂

А чтобы сразу его протестировать и не ждать пока время дойдет до установленного в расписании, можете щелкнуть правой кнопкой мыши по Job’у — Star Job at Step 🙂 и идите смотреть на Ваши мигрированные данные 🙂