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')
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”.
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.
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”.
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.
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!
14 comments
Tengo un servidor de aplicaciones web, un servidor integration services y un servidor de base de datos, como puedo hacer para ejecutar mediante, codigo enviandole parametros a mi servidor integration services,todas estas son maquinas diferentes nada es local, necesito un ejemplo gracias estoy en asp.net 20008, integration services 2008, base de datos 2008.
Hola Robert,
Envíame si puedes un mail y me explicas un poco mejor que es lo que necesitas, porque en tu comentario no me queda muy claro.
Gracias 🙂
Hola,
consulta si tengo un campo de orígen en excel 2003 el interprete me lo reconoce como numérico, pero en realidad es campo texto ¿cómo puedo modificar o cambiar el tipo de dato de lectura de la columna? ej:
00015425k
Hola Mauricio,
Lo que puedes hacer es añadir una primera fila en el Excel de metadata, es decir, si la columna tiene que ser texto, entonces pones XXXXXXXX, si tiene que se numérico pones 123456. Y luego con un conditional Split te quitas esa primera fila de metadata del flujo.
Si tienes cualquier duda, me dices tu correo y te envío un ejemplo por mail.
Un saludo y gracias 🙂
Hola Carmina,
Aun tendrás el código de este ejemplo? en mi caso mi usuario necesita ejecutar un paquete desde su pc pero solo tiene office y correo lotus.
Gracias por anticipado.
Esperando alguien pueda ayudarme,
Me gustaría saber si puedo ejecutar un paquete SSIS desarrollado en Visual Studio 2012 desde SQL Server Agent 2008.
Muchas gracias
THANKS A LOT!
UNA EXPLICACION FANTÁSTICA!
No se visualiza la imagen de la creación de las variables ruta y nombrefichero
Muchas gracias por el aviso, en breve será corregido.
Este material es muy bueno para los que comenzamos, pero algunas imagenes no se ven, por favor podrías corregirlo.
Hola, es el proceso que necesito, pero en mi caso quiero ejecutar el SP desde una macro excel, tienes algún ejemplo?
Hola, no tengo ningún ejemplo usando una macro de Excel
Hola Carmina,
es explicación estupenda, gracias.
consulta, es posible capturar los errores en alguna tabla de sql sever si estos ocurriesen en los paquetes del SSIS, en mi caso estoy utilizando el origen del paquete: Sistema de archivos; este paquete esta desarrollado en visual studio 2010 que no se debo modificar pero este tiene la posibilidad de que genere algún error en alguno de los pasos del paquete, es posible capturar el error ?
Creo que este post te puede ayudar con eso:
https://www.mssqltips.com/sqlservertip/5679/capturing-sql-server-integration-services-package-errors-using-onerror-event-handlers/
Un saludo