Introducción
SQL Server Integration Services (SSIS) y PowerShell (PS) juntos ofrecen una plétora de oportunidades y algunos atajos cuando se tiene que importar, exportar, o a veces mover datos. Me he encontrado con paquetes que contienen una Tarea de Script con líneas y líneas de código C# que, hecho con PowerShell, podría hacer el mantenimiento de ese paquete mucho más fácil. En general, la cosa más común que veo hacer a la Tarea de Script es acceder al sistema de archivos o hacer alguna manipulación en un archivo. Una cosa que espero que tome velocidad en el mundo BI de SSIS es utilizar PowerShell para este tipo de acciones. Esto no es decir que uno es mejor que otro, ya que usted debería tomar el que es mejor en sus ojos, pero cuando puedo hacer operaciones contra el sistema de archivos con un PowerShell de una línea, es simplemente más fácil de mantener que en el paquete. En este artículo hablaré acerca de cómo usted puede usar la tarea más común utilizada para ejecutar código PowerShell en un paquete SSIS: Execute Process Task (Tarea Ejecutar Proceso).
La Configuración
Ejecutar PowerShell con Execute Process Task significa que usted va a llamar a “powershell.exe” y luego pasarle algunos parámetros. Ahora usted puede simplemente ir a una consola de comandos y escribir “powershell /?” para ver sus opciones, pero como mínimo usted generalmente usará lo siguiente:
- NoProfile – esto asegura que no se trate de cargar ningún perfil en la máquina y yo siempre lo uso como una medida de precaución.
- ExecutionPolicy – Mientras que PowerShell puede ser bloqueado cuando se trara de políticas de ejecución para llamar scripts y comandos, también puede ser “pasado”. Cuando usted está probando, su política puede permitirle ejecutar código PowerShell. Si en 6 meses desde ahora algún administrador de dominio desea cambiar esa política para su servidor, esto asegurará que ellos no rompan todos los paquetes donde ustes usa PowerShell.
- Command – Este es un bloque de script (envuelva el código en llaves) o en comillas dobles.
- File – en lugar del parámetro de comando, si usted quiere llamar a un archivo.
La versión de herramientas SSIS que usaré: SQL Server Data Tools Business Intelligence for Visual Studio 2013.
Tarea Ejecutar Proceso
La tarea ejecutar proceso es bastante simple para trabajar y sólo se necesitan unos pocos campos para ser completada. Usted puede obtener la documentación completa acerca de esta tarea con SSIS yendo al enlace provisto en la sección de referencia al final de este artículo. Usted puede ver que la ventana principal de las propiedades de tarea es el panel “Process”, por lo que he remarcado abajo los campos principales con los que trabajaremos:
Ejecutando Código En Línea
Cuando estoy lidiando con el caso donde sólo necesito ejecutar un cmdlet integrado, o el código de una línea es suficientemente corto, optaré por el parámetro “-Command”. Ahora es posible poner 100 palabras en una línea, pero hay un límite para mí, respecto de cuán fácil es mantener eso y también si es legible. Si trabajamos en el ejemplo de probar una ruta de archivo, el cmdlet que yo usaría es Test-Path, y esto está dentro de mi límite de sólo usar el parámetro de comando. Si hubiera más lógica de negocios a incluir, optaría por el archivo.
Utilizar variables puede variar entre individuos y yo intento no enloquecer en su uso. Mi pensamiento en esta situación, tendría una variable para mantener mi ruta raíz, y luego una variable que mantiene mi nombre de archivo con el que estoy trabajando. Luego tendré otra variable que las combina vía una expresión para trabajar con la ruta completa al archivo, esto hará a mi argumento PowerShell mucho más fácil de leer. La variable final que necesitaré es para los argumentos que paso al proceso “powershell.exe”. Así que la lista de variables y su valor inicial se verán así, y note cuando hago referencia a directorios o carpetas, siempre incluiré la barra invertida al final:
- FilePath (String) = C:\TEMP\CollegeScorecardRaw_Data\
- FileName (String) = MERGED1996_PP.csv
- TestFilePath (String) = @[User::FilePath] + @[User::FileName]
- Cmd (String) = “-NoProfile -ExecutionPolicy ByPass -Command \”if (Test-Path ‘” + @[User::TestFilePath] + “‘) {exit 0} else {exit 999}\””
Explicaré un poco más acerca de la cadena cmd después.
Para pasar las variables al comando PowerShell, usamos la variable “Cmd” y eso está simplemente configurado como una expresión para la propiedad Arguments. Yo prefiero hacer esto porque si necesito actualizar ese comando, simplemente actualizo la variable, comparado con tener que abrir la ventana de propiedades y hacer el cambio. Usted también podría configurar esta variable dentro del archivo de configuración o el parámetro del proyecto para una administración más fácil. Yo configuro la tarea ejecutar proceso como se muestra en la siguiente captura de pantalla:
Usted verá que la propiedad “Arguments” se ve como si hubiera escrito algo ahí, pero yendo al panel Expressions, usted puede ver que he mapeado la propiedad a la expresión:
Una cosa final que haré es simplemente añadir dos tareas de script que tendrán un mensaje definido vía C# para mostrar visualmente si la prueba fue exitosa o no. Una ejecución exitosa donde el archivo no existe proveerá este resultado:
Donde la falla para el archivo no existe, el resultado será esta vista:
¿Qué hay de la variable cmd?
Si usted no lo sabía ya, la tarea ejecutar proceso puede ser quisquillosa por momentos, así que siempre pruebe. En scripts PowerShel normales, si yo necesito retornar un verdadero o falso después de probar una ruta de archivo, yo simplemente ejecutaría esto:
Test-Path ‘C:\TEMP\MyFile.csv’
Este comando retornará un verdadero o falso. De todos modos, en la tarea ejecutar proceso, ésta está esperando un valor entero de 0 (cero) para verdadero por defecto. Este cmdlet no retornaría eso por sí mismo, así que usted tendrá que forzarlo a retornar un valor entero, así que, qué si lo cambio a esto:
If (Test-Path ‘C:\TEMP\MyFile.csv’) {0} else {999}
En una consola de PowerShell usted obtendría un cero si esa ruta es buena, o 999 si no lo es. De todas maneras, de nuevo, no retorna eso si usted lo ejecuta llamando PowerShell.exe. ¿Por qué? La razón es que el proceso en sí mismo (PowerShell.exe) está retornando un cero para completar exitosamente el comando. Así que el potencial está ahí para sobrescribir cualquier valor de falla que necesito que se retorne. Lo que usted hace es simplemente forzar el valor de salida, si usted está familiarizado con los días del archivo de lotes, probablemente recuerda hacer esto todo el tiempo. Así que el resultado final del comando para obtener el resultado esperado en SSIS:
If (Test-Path ‘C:\TEMP\MyFile.csv’) {exit 0} else {exit 999}
Conclusión
PowerShell tiene un potencial enorme para ahorrarle el escribir cualquier código en C# para sus paquetes. En el sentido de que está construido sobre el marco de trabajo .NET, mucho de ese código es accesible en una manera mucho más fácil. Sólo recuerde tenerlo en mente cuando diseñe sus paquetes y se encuentre yendo a C#, “¿cómo haría esto en PowerShell?”.
La solución usada para escribir este artículo está disponible aqui
Referencias
Después de pasar 6 años en el mundo de la administración de sistemas y redes, descubrió que disfrutaba aprender y trabajar con SQL Server. Desde 2010, él ha estado involucrado en SQL Server.
Él es un apasionado acerca de PowerShell y la automatización alrededor de SQL Server y Windows. Su experiencia está enfocada en la administración de SQL Server y algo de desarrollo BI (SSIS, SSRS).
Ver todas las publicaciones de Shawn Melton
- Conectando PowerShell a SQL Server usando una cuenta diferente - May 24, 2018
- Conectando PowerShell a un Servidor SQL Server - April 11, 2018
- Introducción de Visual Studio Code para DBAs - February 28, 2017