Интеграция 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
- CSVtoCRM.csv, таким набором данных:
- Два 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
- EXCELtoCRM.xlsx, таким набором данных:
- Две таблички в какой-нибудь базе данных (в этом примере БД 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
- toCRM, таким набором данных:
Итак, можем приступать… к интеграции…
Из внешних источников в 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 🙂 и идите смотреть на Ваши мигрированные данные 🙂
Большое спасибо за статью. Давно искал нечто подобное по описанию взаимодействия SSIS+CRM
Благодарю вас за инструкцию, очень полезно для начинающих, а может и для опытных тоже.
Не могли бы Вы посоветовать, как реализовать такую же схему по синхронизации 1с с CRM? Хотелось бы понять хотя бы базовые важные моменты на которые стоит обратить внимание при синхронизации
C точки зрения CRM тока 1 важный механизм — тока через SDK 🙂 а по поводу 1С не могу ничем помочь — я в нем не разбираюсь 🙂
Задача выгрузки данных из CRM решается гораздо проще с использованием отфильтрованных представлений. И не надо городить Script Component…
Скорость работы без использования web-сервиса повысится в сотни и тысячи раз (сам мерял)… Собственно для выгрузки данных он и не нужен…
Согласен 🙂 Сами часто в целях повышения производительности напрямую ломимся в БД если требуется только чтение 🙂 Но это просто пример плюшевый пример 🙂 так что не рассматривайте злого умысла 🙂
у меня 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. Не подскажите в чем может быть проблема?
Проблема в том, что у Вас 2005 скуль 🙂 а с ним интеграция немножко сложнее 🙂
http://blogs.msdn.com/b/crm/archive/2008/05/07/integrating-crm-using-sql-integration-services-ssis.aspx
Спасибо. помогло.
Отличный инструмент, спасибо за детальное изложение.
Могли бы Вы подсказать, или дать ссылки на похожие решения, как вытащить данные из типа поля lookup в этом коде?
Ну, так же как и другие поля. Надо перечислить его среди других полей:
1. Хотелось бы еще пример загрузки связанных данных. Например организация + её адреса.
2. А также самое интересное, как при помощи такого механизма производить обновление данных?
3. Это все работает с CRM 2011 ?
был бы благодарен за пример с обновлением уже существующих данных в CRM
Ну если пример и будет то не скоро. И «постоновка» задачи слишком расплывчита 🙂
Если нужно обновить данные, можно перед скрипт компонентом постаить компонент lookup — вытащить id записей, а затем в скрпт компоненте сделать Update:
{
contact cont = new contact();
if (!Row.contactid_IsNull) {cont.contactid = Row.contactid; }
if (!Row.Имя_IsNull) { cont.firstname = Row.Имя; }
myservice.Update(cont);
}
А каким способом можно узнать ссылку на службу для crm 2011?
Пробовал вставлять ссылку , которая выдается в разделе Ресурсы для разработчиков, не подошла(
в Add Web Reference выходит ошибка 404
А как узнать ссылку на службу? Пробовал через раздел ресурсы для разработчиков использовать , не помогло… в Add Web Reference не обнаружилась , ошибка 404
Она всегда имеет один вид: