Разработка
22
Апр
17

Интеграция 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 🙂 и идите смотреть на Ваши мигрированные данные 🙂




Комментарии (17)
  • Oleg 22.04.2010

    Большое спасибо за статью. Давно искал нечто подобное по описанию взаимодействия SSIS+CRM

  • Армен 22.04.2010

    Благодарю вас за инструкцию, очень полезно для начинающих, а может и для опытных тоже.
    Не могли бы Вы посоветовать, как реализовать такую же схему по синхронизации 1с с CRM? Хотелось бы понять хотя бы базовые важные моменты на которые стоит обратить внимание при синхронизации

  • slivka_83 22.04.2010

    C точки зрения CRM тока 1 важный механизм — тока через SDK 🙂 а по поводу 1С не могу ничем помочь — я в нем не разбираюсь 🙂

  • Pony77 22.04.2010

    Задача выгрузки данных из CRM решается гораздо проще с использованием отфильтрованных представлений. И не надо городить Script Component…
    Скорость работы без использования web-сервиса повысится в сотни и тысячи раз (сам мерял)… Собственно для выгрузки данных он и не нужен…

  • slivka_83 22.04.2010

    Согласен 🙂 Сами часто в целях повышения производительности напрямую ломимся в БД если требуется только чтение 🙂 Но это просто пример плюшевый пример 🙂 так что не рассматривайте злого умысла 🙂

  • scint 22.04.2010

    у меня VS 2005 и на пункте

    Сначала необходимо добавить в Script Component Web Reference на веб-службу CRM Service: щелкните правой кнопкой мыши по Reference на панели Project Explorer – Add Web Reference – добавьте ссылку на службу http:///mscrmservices/2007/crmservice.asmx с именем CrmSdk

    загвоздка выходит. У меня нет пункта Add web reference, а только Add reference. Не подскажите в чем может быть проблема?

  • slivka_83 22.04.2010

    Проблема в том, что у Вас 2005 скуль 🙂 а с ним интеграция немножко сложнее 🙂
    http://blogs.msdn.com/b/crm/archive/2008/05/07/integrating-crm-using-sql-integration-services-ssis.aspx

  • scint 22.04.2010

    Спасибо. помогло.

  • Игорь 22.04.2010

    Отличный инструмент, спасибо за детальное изложение.
    Могли бы Вы подсказать, или дать ссылки на похожие решения, как вытащить данные из типа поля lookup в этом коде?

  • slivka_83 22.04.2010

    Ну, так же как и другие поля. Надо перечислить его среди других полей:

    ColumnSet cols = new ColumnSet();  
    cols.Attributes = new string[] { "firstname", "lastname", "telephone1", "emailaddress1" };
    
  • 1. Хотелось бы еще пример загрузки связанных данных. Например организация + её адреса.

    2. А также самое интересное, как при помощи такого механизма производить обновление данных?

    3. Это все работает с CRM 2011 ?

  • Смирнов Денис 22.04.2010

    был бы благодарен за пример с обновлением уже существующих данных в CRM

  • slivka_83 22.04.2010

    Ну если пример и будет то не скоро. И «постоновка» задачи слишком расплывчита 🙂

  • Игорь 22.04.2010

    Если нужно обновить данные, можно перед скрипт компонентом постаить компонент lookup — вытащить id записей, а затем в скрпт компоненте сделать Update:
    {
    contact cont = new contact();
    if (!Row.contactid_IsNull) {cont.contactid = Row.contactid; }
    if (!Row.Имя_IsNull) { cont.firstname = Row.Имя; }

    myservice.Update(cont);
    }

  • skils 22.04.2010

    А каким способом можно узнать ссылку на службу для crm 2011?
    Пробовал вставлять ссылку , которая выдается в разделе Ресурсы для разработчиков, не подошла(
    в Add Web Reference выходит ошибка 404

  • Stanislav 22.04.2010

    А как узнать ссылку на службу? Пробовал через раздел ресурсы для разработчиков использовать , не помогло… в Add Web Reference не обнаружилась , ошибка 404

  • slivka_83 22.04.2010

    Она всегда имеет один вид:

    http://{server}/{OrgName}/XRMServices/2011/Organization.svc

*

code