Utilizar SQL Server local para integración de datos en D365 CE mediante SSIS y KingswaySoft

Al trabajar con Dynamics 365, son frecuentes las tareas de carga, transformación y limpieza de datos. Generalmente, estas transformaciones requeridas son sencillas y se pueden realizar directamente desde la plataforma, pero, para tareas más complejas, puede resultar de utilidad disponer de una base de datos local, con objeto de realizar las transformaciones de datos utilizando sintaxis SQL en los procesos ETL.

En este artículo, se indican los pasos necesarios para instalar la solución gratuita de Microsoft SQL Server en una máquina local (o una máquina virtual en la nube) y crear una conexión con la base de datos para la realización de cargas de datos en Dynamics 365 CE, mediante SQL Server Integration Services (SSIS) y los componentes de KingswaySoft.

Como punto de partida, se hace necesario instalar la solución gratuita para desarrolladores de Microsoft SQL Server, la cual se puede descargar desde el siguiente enlace:

SQL Server Downloads | Microsoft

Al ejecutar el archivo de instalación, seleccionar la opción básica, aceptar los términos y condiciones y escoger el directorio de la instalación.

Una vez concluido el proceso de instalación, se visualizan, entre otras cosas, los datos correspondientes a la cadena de conexión y el directorio desde el cual iniciar el asistente de configuración. Adicionalmente, se ofrece la posibilidad de instalar la herramienta SQL Server Management Studio (recomendado), utilizada para la conexión y ejecución de consultas sobre la base de datos, la cual se puede descargar desde el siguiente enlace:

Download SQL Server Management Studio (SSMS) – SQL Server Management Studio (SSMS) | Microsoft Learn

Llegados a este punto, la instancia de SQL Server se ha instalado y habilitado correctamente en la máquina local.

Una vez configurada la instancia, los próximos pasos consisten en la creación de las bases de datos necesarias de acuerdo con los requisitos de los procesos ETL. Para ello, se establecerá una conexión a través de la herramienta SQL Server Management Studio, utilizando el usuario local de la máquina para la autenticación. Como nombre del servidor, se establece por defecto el nombre del equipo en el que se ha instalado la instancia de SQL Server.

Tras iniciar sesión, se procede a la creación de una nueva base de datos en la instancia desde el explorador de objetos.

Se asigna el nombre correspondiente a la nueva base de datos y se guardan los cambios, las opciones y configuraciones adicionales pueden dejarse con los valores por defecto.

Si el proceso se ha completado correctamente, se visualizará la nueva base de datos desde el explorador de objetos.

Para el desarrollo de los procesos ETL, se requiere la instalación de las siguientes herramientas en la máquina desde la cual se van a ejecutar los procesos:
+ Visual Studio 2017 o 2019.
+ SQL Server Data Tools para Visual Studio (SSDT).
+ KingswaySoft SSIS Productivity Pack.
+ KingswaySoft SSIS Integration Toolkit for Microsoft Dynamics 365.

NOTA: La utilización de los componentes de KingswaySoft para la ejecución de los paquetes de integración desde Visual Studio es gratuita, para el despliegue de los paquetes en el catálogo de un servidor SQL y la automatización de los mismos se requiere licencia.

Tras la instalación de los componentes necesarios, se procede a la creación de un nuevo proyecto de tipo Integration Services desde Visual Studio, y a configurar la conexión con la base de datos local.

Para ello, desde el administrador de conexiones se crea una nueva de tipo ADO.NET y se crea una nueva conexión.

Para acceder a las bases de datos creadas en la instancia local del SQL Server, se especifica el valor ‘./’ como nombre del servidor, seleccionando la base de datos correspondiente en el desplegable inferior.

Si la conexión se ha creado correctamente, se visualizará una nueva entrada desde el administrador de conexiones, permitiendo la utilización de la misma a través de los componentes de SQL Integration Services.

Finalmente, se plantea un caso de uso aplicado a un requerimiento real específico, con objeto de mostrar la utilidad de disponer de una base de datos local para la implementación del proceso ETL.

• Requerimiento: Se necesita actualizar todas las cuentas creadas en Dynamics CRM con la fecha de finalización real de la actividad más reciente cerrada vinculada a la propia cuenta, o al contacto principal asociado a la cuenta, o bien al cliente potencial asociado a la cuenta.

• Solución: Se propone la implementación de un proceso ETL integrado por las siguientes partes:

1. Extracción de datos desde CRM: Se recuperan las actividades, cuentas, contactos y clientes potenciales desde CRM, almacenándolas en las tablas correspondientes creadas en la base de datos local.

Para ello, se crea un flujo de datos y, mediante el componente de origen de Dynamics de KingswaySoft, se obtienen los datos correspondientes a las siguientes entidades, seleccionando los campos necesarios para realizar los cruces de datos:

• Cuentas
• Contactos
• Cliente potenciales
• Actividades

Se crean las siguientes tablas auxiliares en la base de datos local, a través del componente Destino ADO NET (premium):

• D365_accounts
• D365_activities
• D365_contacts
• D365_leads

Se define el mapeo de campos entre las extracciones de datos de CRM y las tablas SQL homólogas.

2.Transformación de datos: Mediante sintaxis SQL, se cruzan las tablas alimentadas con los datos de CRM durante la fase anterior para recuperar la actividad más reciente por fecha de vencimiento vinculada a cada cuenta.

• Se cruzan las actividades cerradas (inactivas) vinculadas a cuentas, contactos o clientes potenciales activos de tipo correo electrónico, cita, llamada de teléfono y tarea, ordenándolas por fecha de vencimiento en sentido descendente, y tomando el primer resultado mediante la utilización de la función ROW_NUMBER de SQL.
• Consulta:

• Resultado

3.Carga de datos en CRM: Se actualizan todas las cuentas en CRM informando el valor correspondiente a la fecha de actividad más reciente calculada en el paso anterior.

Para ello, se crea un flujo de datos adicional, en el cual se toma como origen la consulta SQL del paso anterior y como destino la actualización de la cuenta en CRM a través del componente Destino de Dynamics de KingswaySoft.

En el componente de destino, se añade el mapeo de campos correspondiente al GUID de la cuenta y el valor calculado de la fecha de actividad más reciente.

Una vez implementados los flujos de datos correspondientes a las 3 fases indicadas, se puede proceder a la ejecución del mismo para la actualización de las cuentas en CRM, resolviendo con ello el requerimiento planteado en el caso de uso.

Como conclusión, a lo largo del artículo se han abordado las ventajas de disponer de una base de datos local de forma gratuita para la ejecución de tareas cotidianas relacionadas con los datos almacenados en Dynamics cuando se requieren transformaciones de datos de mayor complejidad mediante la utilización de sintaxis SQL, detallando el proceso de instalación y la resolución de un caso de uso real.

Espero que os sirva de ayuda en el día a día, ya sea para la ejecución de cargas de datos, regularizaciones o tareas de limpieza de datos, o cuando no se dispone de un servidor SQL dedicado para la ejecución de procesos ETL puntuales.