Automatización de Transferencias SFTP con SSIS y WinSCP

By /

La transferencia segura de datos es una piedra angular en la gestión de la información moderna, especialmente cuando se trata de mover archivos entre sistemas. Si bien las API ofrecen soluciones avanzadas, no todas las organizaciones o proyectos cuentan con la infraestructura o la experiencia necesaria para implementarlas. En estos escenarios, recurrir a herramientas probadas y confiables como WinSCP, integrado con SQL Server Integration Services (SSIS), proporciona una vía robusta y accesible para automatizar transferencias de archivos mediante el protocolo SFTP (SSH File Transfer Protocol).

Diagrama conceptual de transferencia de archivos SFTP

Este artículo detalla el proceso de configuración y ejecución de transferencias SFTP dentro de un paquete SSIS, utilizando el poder de WinSCP para garantizar la seguridad y eficiencia en la transmisión de datos. Abordaremos desde los requisitos fundamentales hasta la implementación práctica, incluyendo la gestión de fechas y la recuperación ante fallos.

¿Qué es WinSCP y por qué usarlo?

WinSCP (Windows Secure Copy) es un cliente SFTP de código abierto para Windows que utiliza el protocolo SSH (Secure Shell) para establecer conexiones seguras. Su principal ventaja radica en la encriptación de la información de inicio de sesión y los datos transmitidos, protegiéndolos contra accesos ilegales y espionaje en redes inseguras. A diferencia de protocolos más antiguos como FTP, que transmiten datos en texto plano, SFTP opera sobre SSH, garantizando la confidencialidad e integridad de la transferencia.

Las ventajas de WinSCP incluyen:

  • Interfaz de Usuario Intuitiva: Ofrece una interfaz de doble panel (similar a Total Commander) que facilita la visualización y transferencia de archivos entre el sistema local y el servidor remoto mediante arrastrar y soltar. También dispone de una interfaz tipo Explorer.
  • Editor de Texto Integrado: Permite editar archivos de texto directamente en el servidor remoto, con una copia intermedia que se reemplaza al guardar los cambios. También soporta la integración de editores externos como Notepad++ o Eclipse.
  • Sincronización Automática: Facilita la gestión de directorios al permitir la sincronización entre carpetas locales y remotas, automatizando la subida de archivos modificados y la eliminación de versiones antiguas.
  • Uso Gratuito: Al ser de código abierto bajo licencia GNU GPL, WinSCP es gratuito, modificable y distribuible.

WINSCP PARA TRANSFERIR ARCHIVO ENTRE EQUIPOS | FTP

Requisitos para el Uso de WinSCP en SSIS

Para implementar transferencias SFTP con WinSCP en SSIS, se deben cumplir ciertos requisitos técnicos y de configuración:

  1. Instalación de WinSCP: WinSCP debe estar instalado en el servidor donde se ejecutará el paquete SSIS. Es crucial asegurarse de que la versión de WinSCP .NET assembly esté instalada en la Global Assembly Cache (GAC) para que SSIS pueda acceder a ella.
  2. Servidor SSH: El servidor de destino debe tener un servidor SSH habilitado para permitir conexiones SFTP.
  3. Credenciales de Acceso: Se requieren las credenciales de acceso al servidor SFTP, que pueden incluir:
    • Nombre de host o dirección IP del servidor.
    • Nombre de usuario.
    • Contraseña.
    • Opcionalmente, una clave SSH (privada y pública) para una autenticación más segura.
  4. Configuración de Variables en SSIS: Para hacer la conexión dinámica y adaptable, se deben definir variables en SSIS que almacenen la información de conexión. Estas variables se configurarán como "ReadOnlyVariables" en la tarea de Script de SSIS. Ejemplos de variables necesarias incluyen:
    • User::HostName: El nombre de host del servidor SFTP.
    • User::UserName: El nombre de usuario para la conexión.
    • User::Password: La contraseña para la conexión.
    • User::Fingerprint: La huella digital (SSH Host Key Fingerprint) del servidor SSH. Esto es fundamental para verificar la identidad del servidor y prevenir ataques "man-in-the-middle".
    • User::Days_Retrieval: Un parámetro para determinar el rango de fechas de los archivos a descargar.

Autenticación con Claves SSH

Si se opta por la autenticación mediante claves SSH, el proceso implica:

  • Generación de Pares de Claves: Utilizar PuTTYgen (incluido con WinSCP) para generar un par de claves pública y privada.
  • Instalación de Clave Pública: La clave pública debe ser copiada al servidor SSH, generalmente en el archivo ~/.ssh/authorized_keys del usuario.
  • Almacenamiento de Clave Privada: La clave privada debe ser almacenada de forma segura en el equipo cliente (donde se ejecuta SSIS).
  • Uso de Pageant: Para simplificar la autenticación, se puede usar Pageant (el agente de autenticación de PuTTY). Pageant carga la clave privada en memoria, liberando al usuario de tener que introducir la contraseña de la clave repetidamente. Al iniciar sesión en Windows, el usuario introduce la contraseña de la clave privada una vez, y Pageant la mantiene disponible para las sesiones subsiguientes hasta que el usuario cierre sesión.

Implementación del Script Task en SSIS

El Script Task de SSIS es la herramienta principal para integrar la funcionalidad de WinSCP. Permite ejecutar código .NET personalizado dentro del flujo de control de un paquete SSIS.

Configuración de la Tarea de Script

  1. Añadir la Tarea de Script: Arrastre una Script Task desde el SSIS Toolbox a su Control Flow.
  2. Editar la Tarea de Script: Haga doble clic en la Script Task para abrir el Script Transformation Editor.
  3. Configurar Variables de Lectura: En la pestaña Script, bajo la sección ReadOnlyVariables, agregue las variables de SSIS que se utilizarán en el script (por ejemplo, User::HostName, User::UserName, User::Password, User::Fingerprint, User::Days_Retrieval).
  4. Escribir el Código del Script: Seleccione el lenguaje de programación (C# o VB.NET) y haga clic en el botón Edit Script....

Código de Ejemplo para WinSCP en C

A continuación, se presenta un ejemplo de código C# que demuestra cómo utilizar WinSCP dentro de un Script Task de SSIS. Este código se conecta a un servidor SFTP, descarga archivos basándose en un criterio de fecha y maneja la configuración de la sesión.

#region Namespacesusing System;using System.Data;using Microsoft.SqlServer.Dts.Runtime;using System.Windows.Forms;using WinSCP; // Asegúrate de tener la referencia a WinSCP.dllusing System.IO; // Para manejo de directorios y archivosusing System.Text.RegularExpressions; // Para expresiones regulares#endregionnamespace ST_8f76706671c04b07ad1667111a5bdc20{ [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute] public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase { public void Main() { // --- Configuración de la Sesión SFTP --- SessionOptions sessionOptions = new SessionOptions { Protocol = Protocol.Sftp, // Protocolo SFTP HostName = Dts.Variables["User::HostName"].Value.ToString(), // Nombre de host desde variable SSIS UserName = Dts.Variables["User::UserName"].Value.ToString(), // Nombre de usuario desde variable SSIS Password = Dts.Variables["User::Password"].Value.ToString(), // Contraseña desde variable SSIS SshHostKeyFingerprint = Dts.Variables["User::Fingerprint"].Value.ToString() // Huella digital del host }; // --- Configuración de la Recuperación de Archivos --- // Obtener el número de días para la recuperación desde la variable SSIS. // Si el valor no es un número válido o está vacío, se asume 0 (solo día actual). int daysRetrieval = 0; if (int.TryParse(Dts.Variables["User::Days_Retrieval"].Value.ToString(), out int parsedDays) && parsedDays >= 0) { daysRetrieval = parsedDays; } else { // Si la variable no se puede parsear o es negativa, se usa 0 por defecto. // Podríamos lanzar un evento de advertencia aquí si fuera necesario. Dts.Events.FireWarning(14, "Script Task - WinSCP", "Valor inválido para Days_Retrieval. Se usará 0 (solo archivos del día actual).", "", 0); } // Determinar la fecha límite para la descarga de archivos. // Si daysRetrieval es 0, solo se buscan archivos de hoy. // Si es mayor que 0, se buscan archivos desde (hoy - daysRetrieval) hasta hoy. DateTime cutoffDate = DateTime.Today.AddDays(-daysRetrieval); // --- Rutas y Directorios --- // Directorio remoto donde se encuentran los archivos. // El formato es "order_number/item_number/PriceFile_YYYY_MM_DD". // Como los números de orden e ítem cambian, usaremos comodines. string remoteDirectory = "order_*/item_*"; // Patrón para el directorio remoto // Directorio local donde se guardarán los archivos descargados. // Se recomienda usar una ruta especificada en una variable de SSIS o una ruta fija. // Por ejemplo: Dts.Connections["LocalDestinationFolder"].AcquireConnection(Dts.Transaction).ToString(); string localDirectory = @"C:\SSIS_Downloads\"; // Ejemplo de directorio local // Asegurarse de que el directorio local exista. if (!Directory.Exists(localDirectory)) { Directory.CreateDirectory(localDirectory); Dts.Events.FireInformation(0, "Script Task - WinSCP", $"Directorio local creado: {localDirectory}", "", 0, ref false); } try { using (var session = new Session()) { // Conectar al servidor SFTP session.Open(sessionOptions); Dts.Events.FireInformation(0, "Script Task - WinSCP", "Conexión SFTP establecida exitosamente.", "", 0, ref false); // Listar archivos en el directorio remoto que coincidan con el patrón y la fecha. // Usamos RemotePath.TopDirectoryOnly para listar solo los archivos en el directorio especificado, // no recursivamente en subdirectorios (a menos que el patrón remoteDirectory ya lo maneje). // El patrón de nombre de archivo se aplicará después de listar los directorios. RemoteDirectoryInfo directoryInfo = session.EnumerateRemoteFiles(remoteDirectory, null, EnumerationOptions.AllEntries); bool filesDownloaded = false; foreach (RemoteFileInfo fileInfo in directoryInfo) { // Extraer la fecha del nombre del archivo. // El formato esperado es "PriceFile_YYYY_MM_DD". Match dateMatch = Regex.Match(fileInfo.Name, @"PriceFile_(\d{4})_(\d{2})_(\d{2})"); if (dateMatch.Success) { try { DateTime fileDate = new DateTime( int.Parse(dateMatch.Groups[1].Value), // Año int.Parse(dateMatch.Groups[2].Value), // Mes int.Parse(dateMatch.Groups[3].Value) // Día ); // Comparar la fecha del archivo con la fecha límite (cutoffDate). // Si la fecha del archivo es igual o posterior a la fecha límite, se descarga. if (fileDate >= cutoffDate) { string remoteFilePath = session.EscapeFileName(Path.Combine(fileInfo.FullName, fileInfo.Name)); // Construir ruta completa remota string localFilePath = Path.Combine(localDirectory, fileInfo.Name); // Construir ruta completa local // Descargar el archivo session.GetFiles(remoteFilePath, localFilePath).Check(); Dts.Events.FireInformation(0, "Script Task - WinSCP", $"Archivo descargado: {fileInfo.Name} a {localFilePath}", "", 0, ref false); filesDownloaded = true; } } catch (FormatException ex) { Dts.Events.FireWarning(14, "Script Task - WinSCP", $"Error al parsear la fecha del archivo {fileInfo.Name}: {ex.Message}", "", 0); } catch (Exception ex) { Dts.Events.FireError(18, "Script Task - WinSCP", $"Error al descargar el archivo {fileInfo.Name}: {ex.Message}", "", 0); // Considerar si se debe detener la ejecución o continuar con otros archivos. // Para este ejemplo, continuamos con el siguiente archivo. } } } if (!filesDownloaded && daysRetrieval > 0) { Dts.Events.FireInformation(0, "Script Task - WinSCP", $"No se encontraron archivos dentro del rango de {daysRetrieval} días.", "", 0, ref false); } else if (!filesDownloaded) { Dts.Events.FireInformation(0, "Script Task - WinSCP", "No se encontraron archivos para descargar hoy.", "", 0, ref false); } // El resultado Success indica que la tarea se completó correctamente. Dts.TaskResult = (int)ScriptResults.Success; } } catch (WinSCP.Exceptions.SshAuthenticationException ex) { Dts.Events.FireError(18, "Script Task - WinSCP", $"Error de autenticación SFTP: {ex.Message}. Verifique el nombre de usuario, contraseña y huella digital del host.", "", 0); Dts.TaskResult = (int)ScriptResults.Failure; } catch (WinSCP.Exceptions.SshHostKeyException ex) { Dts.Events.FireError(18, "Script Task - WinSCP", $"Error con la huella digital del host SSH: {ex.Message}. Asegúrese de que la huella digital en la variable SSIS sea correcta.", "", 0); Dts.TaskResult = (int)ScriptResults.Failure; } catch (Exception ex) { // Capturar cualquier otro error y registrarlo. Dts.Events.FireError(18, "Script Task - WinSCP", $"Ocurrió un error inesperado: {ex.Message}", "", 0); Dts.TaskResult = (int)ScriptResults.Failure; } } }}// Definición de los posibles resultados de la tarea de script.enum ScriptResults{ Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure};

Manejo de Errores y Recuperación

El código incluye bloques try-catch para manejar posibles errores durante la conexión y la transferencia de archivos. Los errores específicos, como SshAuthenticationException o SshHostKeyException, se capturan para proporcionar mensajes de error más detallados.

La propiedad Dts.TaskResult se establece en ScriptResults.Success o ScriptResults.Failure para indicar el resultado de la ejecución de la tarea al motor de SSIS. Esto es crucial para la lógica de flujo de control del paquete, permitiendo, por ejemplo, reintentar la operación en caso de fallo.

Variable Days_Retrieval

La variable User::Days_Retrieval juega un papel fundamental en la flexibilidad del paquete.

  • Days_Retrieval = 0: Esta es la configuración más común. El script descargará únicamente los archivos correspondientes al día actual. Esto es ideal para la operación diaria normal de transferencia de datos.
  • Days_Retrieval > 0: Cuando se necesita cargar datos históricos (por ejemplo, al adquirir un nuevo conjunto de datos o al recrear la base de datos en caso de corrupción), esta variable se puede configurar con un valor suficientemente grande (por ejemplo, 30 o más) para asegurar que todos los archivos dentro del período deseado sean recuperados. El script calculará una fecha límite (cutoffDate) y descargará todos los archivos PriceFile_YYYY_MM_DD cuya fecha sea igual o posterior a esta fecha límite.
  • Manejo de Valores Inválidos: El script valida la entrada de Days_Retrieval. Si el valor no es un número entero positivo, se utiliza 0 por defecto y se registra una advertencia.

Formato de Archivos y Directorios

El sistema de archivos en el servidor SFTP sigue un patrón específico: order_number/item_number/PriceFile_YYYY_MM_DD.

  • Directorios Remotos: El script utiliza el patrón order_*/item_* para buscar dentro de los directorios que coincidan con esta estructura. Las asteriscos (*) actúan como comodines.
  • Nombres de Archivos: El script utiliza una expresión regular (Regex) para extraer la fecha (YYYY_MM_DD) del nombre del archivo PriceFile_YYYY_MM_DD. Esto permite comparar la fecha del archivo con la cutoffDate calculada.

Descarga de Archivos y Verificación

El método session.GetFiles(remoteFilePath, localFilePath).Check(); se encarga de descargar el archivo. El .Check() asegura que se lance una excepción si la transferencia falla.

La variable booleana filesDownloaded se utiliza para rastrear si se ha descargado al menos un archivo. Esto ayuda a proporcionar información útil en el log si no se encuentran archivos para descargar, especialmente cuando se esperan archivos basados en el parámetro Days_Retrieval.

Consideraciones Adicionales y Buenas Prácticas

  • Seguridad de Credenciales: Evite almacenar contraseñas directamente en el código o en variables SSIS no cifradas. Considere usar el SSIS Package Configuration o SSIS Project Deployment Model con parámetros sensibles para almacenar y gestionar credenciales de forma segura.
  • Gestión de la Huella Digital del Host: La huella digital del host (SshHostKeyFingerprint) es crucial para la seguridad. Asegúrese de que la variable SSIS que la contiene esté actualizada. Si el servidor SFTP cambia su certificado, esta huella digital deberá actualizarse en la variable.
  • Rutas de Directorio: Utilice variables de SSIS o Connection Managers de tipo File para especificar las rutas de los directorios locales y remotos. Esto hace que el paquete sea más flexible y fácil de configurar en diferentes entornos.
  • Registro Detallado: Aproveche los métodos Dts.Events.FireInformation, Dts.Events.FireWarning, y Dts.Events.FireError para registrar el progreso, advertencias y errores. Esto es invaluable para la depuración y el monitoreo.
  • Automatización con SQL Server Agent: Una vez que el paquete SSIS esté configurado, puede ser programado para ejecutarse automáticamente utilizando el SQL Server Agent. Asegúrese de que la cuenta de servicio del SQL Server Agent tenga los permisos necesarios para ejecutar el paquete y acceder a las rutas de red y archivos.
  • Depuración bajo SSIS: Cuando se depura un script que funciona manualmente pero falla bajo SSIS, es común que el problema radique en el entorno de ejecución. La cuenta bajo la cual se ejecuta SSIS puede no tener acceso a ciertas configuraciones o claves de registro que el usuario interactivo sí tiene. Por ejemplo, la clave SSH del host puede no estar cacheada para la cuenta de servicio de SSIS. La inclusión explícita de la huella digital del host (SshHostKeyFingerprint) en las SessionOptions es la solución recomendada.

Conclusión

La integración de WinSCP con SSIS proporciona una solución potente y segura para la automatización de transferencias de archivos SFTP. Al configurar correctamente las variables de SSIS, implementar el código en un Script Task y seguir las mejores prácticas de seguridad y manejo de errores, se puede crear un flujo de trabajo robusto que cumpla con los requisitos de transferencia de datos, incluso en escenarios complejos que involucren datos históricos o recuperación ante desastres. La flexibilidad del parámetro Days_Retrieval y la seguridad inherente de SFTP hacen de esta combinación una herramienta valiosa en el arsenal de cualquier profesional de la integración de datos.

tags: #envio #sftp #por #comando #winscp #ssis

Publicaciones populares:

  • Guía Sentinel Cámaras IP WiFi
  • Telefonía VoIP para empresas
  • Tipos de routers en Packet Tracer
  • Convierte TV clásica a moderna con HDMI a RCA
  • Detalles del monitoreo ambiental Ubiquiti