Conectar a Microsoft usando el conector de Base de datos en Jitterbit Studio

Esta página explica cómo conectar a estos servicios de Microsoft usando el conector de Base de datos:

• Microsoft Access
• Microsoft Excel
• Microsoft SQL Server

Microsoft Access

Se debe ejecutar un agente privado para conectar a Microsoft Access. Microsoft Office debe estar instalado en el mismo sistema donde se ejecuta el agente privado, y ese sistema debe ser un sistema operativo Windows. Ten en cuenta que el equipo de Jitterbit no ha probado los controladores ODBC de Linux para Microsoft Access.

Instalar el controlador ODBC

El controlador ODBC de Microsoft Access generalmente se instala como parte de Windows. Los controladores más antiguos admiten la extensión de archivo .mdb. Sin embargo, si la base de datos es Access 2007 o posterior con la extensión .accdb, es posible que necesites obtener una versión más reciente del controlador ODBC, como Microsoft Access 2016 Redistributable.

Configurar la conexión de Base de datos

Después de instalar el controlador, especifica estas entradas durante la configuración de la conexión de Base de datos:

• Tipo de controlador: Selecciona ODBC.

• Controlador: Selecciona el controlador de Microsoft Access.

• Usar cadena de conexión: En Configuración opcional, marca esta casilla.

• Cadena de conexión: En Configuración opcional, ingresa la cadena de conexión manual usando este formato:

  DRIVER=<driver>;DBQ=<database-path>;UID=<username>;PWD=<password>;
  

  Usa una de estas cadenas de conexión, según la versión y si tienes una contraseña. Reemplaza la ruta de la carpeta y el nombre del archivo con los específicos del archivo ubicado en tu agente privado.

  Controlador de Microsoft Access (*.mdb) sin contraseña

  DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\myfolder\mydatabase.accdb;
  

  Controlador de Microsoft Access (*.mdb, *.accdb) con contraseña

  DRIVER={Microsoft Access Driver(*.mdb, *accdb)};DBQ=C:\myfolder\mydatabase.accdb;UID=username;PWD=password;
  

Configurar actividades de base de datos

Durante la configuración de la actividad de Base de datos, ten en cuenta que el controlador ODBC de Microsoft Access no admite la búsqueda de objetos. No ingrese criterios de búsqueda en el cuadro de búsqueda de selección de tabla, ya que no se devolverán resultados.

Ejemplos de cláusula WHERE

Estos ejemplos de cláusula WHERE se proporcionan como referencia al configurar una actividad de consulta de Base de datos:

WHERE [LastName]='Bagel'

WHERE field1 LIKE field2

WHERE City = "Chicago" OR BirthDate < DateAdd (" yyyy ", -40, Date())

Para obtener ejemplos adicionales, consulta la documentación de Microsoft:

• Access SQL: cláusula WHERE
• Ejemplos de criterios de consulta

Microsoft Excel

Se debe usar un agente privado para conectar a una hoja de cálculo de Microsoft Excel.

Instalar el controlador ODBC

Para conectar a hojas de cálculo de Excel, es posible que necesites instalar un controlador si el controlador apropiado no está incluido en el sistema operativo del agente privado.

Por ejemplo, Windows 10 viene con controladores ODBC .xls x32 que admiten las versiones 3.0, 4.0, 5.0/95 y 97-2000 de Excel. Sin embargo, para obtener los controladores ODBC más recientes que admitan todas estas versiones y Excel 12.0 (Excel 2007), puedes instalar uno de estos controladores:

• Microsoft Access 2016 Runtime (Win 7/8/10/2008 R2/2012)
• Microsoft Access 2016 Redistributable

Configurar la conexión de base de datos

Después de instalar el controlador, especifica estas entradas durante la configuración de la conexión de base de datos:

• Tipo de controlador: Selecciona ODBC.

• Controlador: Selecciona el controlador de Microsoft Excel.

• Usar cadena de conexión: En Configuración opcional, marca esta casilla.

• Cadena de conexión: En Configuración opcional, utiliza una de estas cadenas de conexión, según tu versión de Excel. Reemplaza la ruta de carpeta y el nombre de archivo con los específicos del archivo ubicado en el agente privado.

  Excel 97, Excel 2000, Excel 2002, Excel 2003

  Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
  DBQ=C:\Folder Path\My File Name.xls;
  

  Excel 2007, Excel 2010, Excel 2013

  Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
  DBQ=C:\Folder Path\My File Name.xlsx;
  

  Para especificar que la conexión sea actualizable (por ejemplo, si deseas permitir la escritura en un archivo de destino), puedes agregar ReadOnly=0; al final de la cadena de conexión. Por ejemplo:

  Actualizable

  Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
  DBQ=C:\Folder Path\My File Name.xlsx;ReadOnly=0;
  

  Consejo

  Consulta opciones adicionales para cadenas de conexión de Excel en Microsoft Excel 2007 ODBC Driver connection strings en The Connection Strings Reference.

Configurar actividades de base de datos

Durante la configuración de la actividad de base de datos, puedes seleccionar y unir hojas de trabajo dentro de la hoja de cálculo de Excel.

Microsoft SQL Server

Se puede acceder a las bases de datos de Microsoft SQL Server desde agentes en la nube y privados. Se recomienda ODBC de Windows en agentes privados para un rendimiento optimizado. Si deseas utilizar funciones adicionales, como proporcionar una consulta manual, estas están disponibles solo con el controlador JDBC. Harmony admite estas opciones de autenticación:

Los procedimientos a continuación muestran cómo configurar Microsoft SQL Server como una conexión de base de datos usando autenticación de Windows o SQL Server. También incluyen ejemplos de cláusulas WHERE que puedes usar con actividades de base de datos.

Autenticación de Windows

La autenticación de Windows se admite usando ODBC y JDBC solo en agentes privados. Se puede usar con una sola cuenta de dominio. Para usar autenticación de Windows, configura estas propiedades en Windows:

1. Abre la herramienta Servicios Administrativos (Inicio > Herramientas administrativas > Servicios).

2. Si usas ODBC, haz clic derecho en el servicio Jitterbit Apache Server y selecciona Propiedades. Si usas JDBC, haz clic derecho en el servicio Jitterbit Tomcat Server y selecciona Propiedades.

3. Ve a la pestaña Iniciar sesión. Selecciona Esta cuenta e ingresa el nombre y las credenciales de la cuenta que deseas usar para la autenticación. Luego haz clic en Aplicar.

4. Repite el paso anterior para el servicio Jitterbit Process Engine.

5. Establece TempDir en el archivo de configuración del agente (jitterbit.conf) en C:\Windows\Temp\jitterbit.

6. Reinicia los servicios de Jitterbit.

Después de completar estos pasos, ve a Studio y configura tu base de datos como de costumbre, especificando estas entradas durante la configuración de la conexión de base de datos:

• Tipo de controlador: Selecciona ODBC o JDBC según corresponda.

  Importante

  La autenticación de Windows se admite con los siguientes controladores JDBC:

  • SQL Server jTDS [JDBC]
  • SQL Server MS JDBC [JDBC]
  • Versiones más recientes del controlador JDBC de Microsoft para SQL Server

  Para usar autenticación de Windows con los controladores JDBC de Microsoft, copia el archivo mssql-jdbc_auth-x.x.x.x64.dll incluido en el paquete de descarga del controlador a las carpetas C:\Program Files\Jitterbit Agent\jre\bin y C:\Program Files\Jitterbit Agent\jre\lib en el agente. La versión del DLL debe coincidir con la versión del archivo JAR de JDBC incluido en el agente. Haz una copia de seguridad del archivo ya que puede eliminarse durante actualizaciones principales del agente.

• Controlador: Selecciona el controlador de SQL Server.

• Nombre del servidor: Ingresa el nombre o la dirección IP del servidor que ejecuta SQL Server al que Harmony necesita conectarse. Es posible que debas especificar el nombre de la instancia de SQL Server (HostName\InstanceName).

• Nombre de la base de datos: Ingresa el nombre de la base de datos en el servidor al que Harmony necesita conectarse.

• Inicio de sesión y Contraseña: Deja estos campos en blanco.

• Parámetros de cadena de conexión adicionales: En Configuración opcional, ingresa lo siguiente según tu controlador:

  • ODBC SQL Server: Si usas el controlador ODBC SQL Server, ingresa integratedSecurity=true. Si esto no funciona, ingresa Trusted_Connection=yes.

  • ODBC Driver 11 para SQL Server, SQL Server Native Client 10.0, SQL Server Native Client 11.0: Si usas otro controlador ODBC SQL Server, ingresa Trusted_Connection=yes.

  • SQL Server jTDS [JDBC], SQL Server MS JDBC [JDBC]: Si usas un controlador JDBC SQL Server, ingresa integratedSecurity=true.

El controlador ahora se autenticará como el usuario del dominio de Windows especificado anteriormente.

Autenticación de SQL Server

Ve a Studio y configura tu base de datos como de costumbre. Especifica estas configuraciones durante la configuración de la conexión de base de datos:

• Tipo de controlador: Selecciona ODBC o JDBC según corresponda.

• Controlador: Selecciona el controlador de SQL Server. Al seleccionar un controlador JDBC, recomendamos usar SQL Server MS JDBC, el controlador JDBC de Microsoft para SQL Server.

• Nombre del servidor: Ingresa el nombre o la dirección IP del servidor que ejecuta SQL Server al que Harmony necesita conectarse. Es posible que debas especificar el nombre de la instancia de SQL Server (NombreDelHost\NombreDeInstancia).

• Nombre de la base de datos: Ingresa el nombre de la base de datos en el servidor al que Harmony necesita conectarse.

• Usuario y Contraseña: Ingresa el nombre de usuario y la contraseña para la autenticación de SQL Server.

El controlador ahora se autenticará usando las credenciales de autenticación de SQL Server especificadas.

Cifrado de conexión y certificados del servidor

Las versiones actuales del controlador SQL Server MS JDBC solicitan una conexión cifrada de forma predeterminada (encrypt=true) y validan el certificado que presenta el servidor de base de datos. Si el controlador no puede validar el certificado del servidor contra el almacén de claves Java del agente, la conexión falla con un error similar a este:

"encrypt" property is set to "true" and "trustServerCertificate" property is set to "false" but the driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption: Error: (certificate_unknown) PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target.

Esto afecta a cualquier SQL Server cuyo certificado no pueda rastrearse hasta una autoridad de certificación (CA) que el agente ya confíe, incluido un certificado autofirmado o emitido internamente. Amazon RDS para SQL Server es un ejemplo: una instancia de RDS presenta un certificado emitido por una CA de Amazon RDS que no está en el almacén de claves predeterminado del agente. Una base de datos puede tener el cifrado habilitado y un certificado válido instalado y aún así fallar esta validación, porque la falla es de confianza y no de cifrado.

La versión 12.8 del agente actualizó el controlador JDBC de Microsoft SQL Server incluido. Microsoft cambió el valor predeterminado de encrypt de false a true en la versión 10.2 del controlador y documenta ese cambio como uno que rompe la compatibilidad, descrito bajo encrypt en Establecer las propiedades de conexión en la documentación de Microsoft. Una conexión que funcionaba en un agente anterior a 12.8 puede comenzar a fallar después de la actualización.

Para resolver esto, ingresa encrypt=false; en el campo Parámetros adicionales de cadena de conexión bajo Configuración opcional de la Conexión de base de datos, o inclúyelo en una cadena de conexión manual. Esto funciona en agentes en la nube y privados.

Tu configuración de SQL Server puede requerir otras propiedades de conexión también. Para las propiedades que admite el controlador, consulta Establecer las propiedades de conexión en la documentación de Microsoft.

Ejemplos de cláusula WHERE

Estos ejemplos de cláusula WHERE se proporcionan como referencia al configurar una Actividad de consulta de base de datos:

WHERE LastName = 'Smith' ;

WHERE LastName LIKE ('%Smi%');

WHERE EmployeeKey  <= 500;

WHERE EmployeeKey = 1 OR EmployeeKey = 8 OR EmployeeKey = 12;

WHERE EmployeeKey <= 500 AND LastName LIKE '%Smi%' AND FirstName LIKE '%A%';

WHERE LastName IN ('Smith', 'Godfrey', 'Johnson');

WHERE EmployeeKey Between 100 AND 200;

Para obtener ejemplos adicionales, consulta la documentación de Microsoft:

• WHERE (Transact-SQL)
• Condición de búsqueda (Transact-SQL)