Uno de los problemas que nos encontramos a veces en nuestros clientes son los permisos que tienen o que les podemos otorgar a los usuarios para ejecutar procedimientos almacenados, Jobs, paquetes de SSIS, etc… En este caso concreto, los usuarios solo tenían permisos para ejecutar procedimientos almacenados, pero no podían ejecutar Jobs o paquetes de SSIS y necesitaban cargar ficheros de Excel en tablas de SQL Server utilizando una aplicación web.

La solución final por la que optamos fue utilizar un procedimiento almacenado que modifica y ejecuta un Job dinámicamente y el Job es el que se encarga de hacer la llamada al paquete de SSIS. De este modo solo tenemos que otorgar a la aplicación permisos de ejecución sobre el procedimiento y luego dentro del procedimiento cambiamos el usuario por uno que tenga permisos de ejecución sobre paquetes de SSIS.

Vamos a explicar todo esto con un ejemplo para que quede mucho más claro. Este es el caso, Los usuarios quieren cargar ficheros de Excel en tablas, para lo cual, nos van a proporcionar la ruta en la que se encuentra el fichero, el nombre del fichero y el nombre del paquete de SSIS al que quieren invocar (crearemos un paquete de SSIS por cada tipo de fichero Excel diferente). Los usuarios también quieren mantener un log de lo que está pasando mientras se ejecuta la ETL por eso se han creado unas tablas de log, de las cuales nos pasarán también como parámetro un IdLog que nosotros utilizaremos en nuestros paquetes para introducir información en su tabla para ir logueando el estado de la carga. Para este ejemplo vamos a cargar un Excel sencillo con nombre de ciudades.

Creación de los paquetes de SSIS

Vamos a utilizar una estructura sencilla para ilustrar el ejemplo, pero como mejoras al paquete se le podría añadir por ejemplo, un mejor control de errores, se podría cargar el Excel en una tabla intermedia y hacer una limpieza y comprobación de tipo de datos y avisar al usuario si hay algo mal con los datos del Excel antes de cargar en la tabla final, también se podría añadir la parte de copiar los ficheros una vez cargados a otro directorio con la fecha y hora de la carga, etc…

La estructura de nuestro paquete de SSIS será la siguiente:

  • Logueado de inicio
  • Carga de Excel
  • Logueado de fin

Dentro del Data Flow de Carga Excel tendremos un origen Excel (Excel Source) y un destino SQL Server (OLE DB Destination).

Nuestro paquete también tendrá las variables que recibiremos por medio del Job, estas variables son: El IdLog y nombre y ruta del fichero.

Para el logueado utilizaremos una SQL Task (Log Inicio) con una sentencia de insert en las tabla de logueado usando el IdLog que nos pasan a través del Job. Este es el script de creación de la tabla de login para este ejemplo:

IF OBJECT_ID('LOG_EJECUCIONES_DETALLE') IS NOT NULL
    DROP TABLE LOG_EJECUCIONES_DETALLE
 
CREATE TABLE LOG_EJECUCIONES_DETALLE
(
  ID_LOG_DETALE INT IDENTITY(1,1) PRIMARY KEY,
  ID_LOG INT,
  MENSAJE NVARCHAR(1000)
)

Hacemos doble click en nuestra tarea de “Log Inicio” y en la parte de “SQLStatement” introducimos la siguiente sentencia:

INSERT INTO LOG_EJECUCIONES_DETALLE(ID_LOG, MENSAJE)
VALUES(?, 'Inicio Ejecución')

 

paquetes de SSIS

La interrogación representa la variable IdLog que tenemos que parametrizar. Para ello, pulsamos sobre “Parameter Mapping” y añadimos la variable como se ve en la imagen.

Repetimos el mismo proceso para el SQL Task de “Log Fin”.

Ahora pasamos a la parte de la carga del fichero Excel en el interior del Data Flow. Para que la ruta y el nombre del fichero se cambien de forma dinámica vamos a poner una “Expression” en la cadena de conexión de nuestro “Excel Connection Manager”. Para ello pulsamos sobre nuestro “Excel Connection Manager” y nos vamos a su ventana de propiedades y pulsamos sobre “Expressions”.

properties excel connection manager paquetes de ssis

Ahora seleccionamos la propiedad de “ExcelFilePath” e introducimos lo siguiente en la ventana de “Expression”:

@[User::Ruta]+ @[User::NombreFichero]

Ahora tenemos nuestra ruta del fichero parametrizada para cuando nos pasen estos valores a través del Job.

carga excel ciudades paquetes de ssis

De momento para probar el paquete en nuestro local nos creamos un Excel que tendrá en mismo formato que el Excel que nos pasarán a modo de plantilla, en este caso solo tenemos la columna CIUDADES. Colocamos el Excel que acabamos de crear en la ruta de prueba, en nuestro caso “C:temp” y llamamos a nuestro paquete plantilla “ciudades.xlsx”.

La tabla que utilizaremos para almacenar las ciudades será la siguiente:

IF OBJECT_ID('CIUDADES') IS NOT NULL
  DROP TABLE CIUDADES

CREATE TABLE CIUDADES
(
  ID_CIUDADES INT IDENTITY(1,1) PRIMARY KEY,
  NOMBRE_CIUDAD NVARCHAR(1000)
)

El siguiente paso será configurar nuestro “Excel Source” para leer del fichero Excel y configurar nuestro “OLE DB Destination” para utilizar la tabla que acabamos de crear y ya tenemos nuestro paquete listo para subirlo a nuestro servidor de SSIS.

 

Creación del Job

Ahora vamos a crear un Job en nuestro servidor que será el que modificaremos y ejecutaremos desde nuestro procedimiento almacenado. Para ello vamos a nuestra instancia y creamos un nuevo Job, en nuestro ejemplo, el Job se llamará “CARGA_FICHEROS”. Creamos un nuevo “Step” que llamaremos “Paquete SSIS” y seleccionaremos el paquete que acabamos de crear, posteriormente mediante el procedimiento almacenado cambiaremos de forma dinámica el paquete que se llamará en este “Step”.

paquetes de ssis

Ahora en la pestaña de “Set Values” vamos a añadir las variables que le vamos a pasar al paquete de SSIS. Al igual que el nombre del paquete, estas variables también serán modificadas en tiempo de ejecución a través del procedimiento almacenado.

Job properties paquetes de ssis

 

Creación del procedimiento almacenado

El procedimiento lo vamos a crear en la base de datos de “msdb” para poder modificar y ejecutar el Job y luego en le daremos permisos al usuario que utilizará la aplicación web para ejecutar este procedimiento.

Este es el código TSQL:

CREATE PROCEDURE [dbo].[SP_LANZA_ETL]
(
  @pIdLog int,
  @pNombrePaqueteETL varchar(200),
  @pRuta varchar(500),
  @pNombreFichero varchar(200)
)
WITH EXECUTE AS 'CARMINABERNABEUEjecutaPaquetes'
AS
SET NOCOUNT ON

-- Declaracion de variables internas  
DECLARE
 --Variables de error
@Error int, @Severity int, @MensajeErrorSQL varchar(4000), @ret int, @MensajeError999 varchar(100),

--Variable para guardar el comando a ejecutar con el job
@comandoJob VARCHAR(800),
@EnEjecucion INT,
@ReturnCode INT,
@jobName VARCHAR(50),
@servidor VARCHAR(20)

-- Inicialización de variables  
Select @ret = -999,
@MensajeError999 = 'Error -999: Error no controlado de severidad %d.' + char(13) + char(10) + 'Mensaje: %s',
@ReturnCode = 0,
@jobName = 'CARGA_FICHEROS',
@comandoJob = '',
@servidor = 'CARMINABERNABEU'

SET @comandoJob = N'/SQL ""Pruebas' + @pNombrePaqueteETL + '"" /SERVER ""' + @servidor + '"" /X86  /CHECKPOINTING OFF'
SET @comandoJob = @comandoJob + ' /SET ""Package.Variables[IdLog].Value"";""' + CAST(@pIdLog AS VARCHAR(20)) + '""'
SET @comandoJob = @comandoJob + ' /SET ""Package.Variables[Ruta].Value"";""' + @pRuta + '""'
SET @comandoJob = @comandoJob + ' /SET ""Package.Variables[NombreFicheroExt].Value"";""' + @pNombreFichero + '""'
SET @comandoJob = @comandoJob + ' /REPORTING E'

BEGIN TRY
--comprobamos si el job ya se está ejecutando
IF EXISTS(
  SELECT *
  FROM msdb.dbo.sysjobactivity AS sja
  INNER JOIN msdb.dbo.sysjobs AS sj ON sja.job_id = sj.job_id
  WHERE sja.start_execution_date IS NOT NULL
  AND sja.stop_execution_date IS NULL
  and sj.name = @jobName
)
  SELECT @EnEjecucion = 1
ELSE
  SELECT @EnEjecucion = 0

IF @EnEjecucion = 0
BEGIN
  BEGIN TRANSACTION
  --modificamos el step del job para añadirle el nuevo comando a ejecutar
  EXEC @ReturnCode = msdb.dbo.sp_update_jobstep @job_name=@jobName, @step_name=N'Paquete SSIS',
  @step_id=1,
  @cmdexec_success_code=0,
  @on_success_action=1,
  @on_success_step_id=0,
  @on_fail_action=2,
  @on_fail_step_id=0,
  @retry_attempts=0,
  @retry_interval=0,
  @os_run_priority=0, @subsystem=N'SSIS',
  --asignamos el comando
  @command = @comandoJob,
  @database_name=N'master',
  @flags=0

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
    GOTO QuitWithRollback

  --modificamos el job
  EXEC @ReturnCode = msdb.dbo.sp_update_job @job_name = @jobName, @start_step_id = 1

  IF (@@ERROR <> 0 OR @ReturnCode <> 0) 
  GOTO QuitWithRollback

  COMMIT TRANSACTION

  GOTO EndSave


QuitWithRollback:

  IF (@@TRANCOUNT > 0) 
    ROLLBACK TRANSACTION


EndSave:
  --ejecutamos el job
  EXEC msdb.dbo.sp_start_job @job_name = @jobName
END
ELSE
BEGIN
  RAISERROR ('Error -998: El job ya se encuentra en ejecución.', 1, 1)
  RETURN -998
END

REVERT
select @ret = 0

END TRY

BEGIN CATCH
  -- Control de error para errores de severidad mayor a 10 y menor a 20  
  SELECT
  @Error = ERROR_NUMBER(),
  @Severity = ERROR_SEVERITY(),
  @MensajeErrorSQL = ERROR_MESSAGE()

  if @Error <> 0 --> Hay error
  begin
    raiserror (@MensajeError999, 1, 1, @Severity, @MensajeErrorSQL)
    select @ret = -999
  end
END CATCH

RETURN @ret

La partes importantes de este procedimiento son, primero, la parte en la que cambiamos de usuario en la línea 8, en nuestro ejemplo el usuario “EjecutaPaquetes” es el usuario que tiene permisos de ejecución de los paquetes de SSIS, de esta manera establecemos la seguridad que necesitamos. Tenemos el usuario que tendrá permisos para ejecutar el procedimiento y luego totalmente independiente otro usuario que es el que tenemos para ejecutar paquetes en nuestro servidor de SSIS.

La segunda parte importante es la construcción del comando para modificar el Job, a partir de la línea 34, en nuestro ejemplo se usa la carpeta “Pruebas” a fuego que es la que hemos creado para este ejemplo en nuestro servidor, pero esta carpeta también se podría parametrizar sin ningún problema. Como se puede ver en el código al comando le pasamos el nombre del paquete, el nombre del servidor y nuestras variables, el IdLog y el nombre y ruta del fichero.

Y la tercera parte importante se encuentra en la línea 57, que es en la cual ponemos el nombre del “Step” de nuestro Job. En este ejemplo el nombre del Job (línea 30) y el nombre del “Step” están puestos de forma estática pero como mejora esto se podría poner también de forma dinámica.

Una vez creado el procedimiento le otorgamos permisos al usuario que será el que se tendrá permisos desde la aplicación web para ejecutar el procedimiento. Para ello vamos a las propiedades del procedimiento y en la parte de “Permissions” añadimos el usuario deseado, en este ejemplo de forma didáctica se usa el usuario invitado, pero lo normal sería crear un usuario específico para esta aplicación web.

El último paso sería que la aplicación web haga la llamada al procedimiento almacenado de la siguiente manera y nuestra tabla quedará cargada con la información del fichero Excel.

EXEC [dbo].[SP_LANZA_ETL] 1, 'CargaExcelCiudades', 'C:temp', 'ciudades.xlsx'

 

A forma de resumen de los pasos, primero hemos creados nuestros paquetes de SSIS que son los que encargarán de hacer la ETL para cargar los ficheros Excel en tablas, luego tenemos nuestro Job que es el que se encargará de ejecutar el paquete de SSIS y por último nuestro procedimiento almacenado que será el encargado de hacer ese cambio de usuario y de modificar el Job en tiempo de ejecución para modificar el Job con los valores deseados.

Esto puede parecer una forma rebuscada de ejecutar un paquete de SSIS, pero a veces por tema de permisos en nuestra empresa no nos queda mas remedio que buscar este tipo de alternativas a través de procedimientos y Jobs. Espero que os sirva de ayuda y para cualquier duda o si queréis los materiales utilizados en este post, no dudéis en escribir un comentario.

 

¿Conoces nuestro Master en Business Intelligence en Advanced Analytics? Infórmate aquí

Si te ha gustado, ¡recuerda suscribirte a todas nuestra novedades!

 

Carmina Bernabéu