Tutorial: Publicación de un paquete SSIS como una vista SQL

Artículo
03/30/2023

Se aplica a: SQL Server SSIS Integration Runtime en Azure Data Factory

En este tutorial se explica detalladamente cómo publicar un paquete SSIS como una vista SQL en una base de datos de SQL Server.

Prerequisites

Para realizar este tutorial, debe tener instalado el siguiente software en el equipo:

SQL Server con SQL Server Integration Services.
SQL Server Data Tools.

Paso 1: Compilar e implementar un proyecto de SSIS en el catálogo de SSIS

En este paso creará un paquete SSIS que extrae datos de un origen de datos compatible con SSIS (en este ejemplo, usaremos una base de datos de SQL Server), y genera datos de salida con un componente de Destino de streaming de datos. Luego, compilará e implementará el proyecto de SSIS en el catálogo de SSIS.

Inicie SQL Server Data Tools. En el menú Inicio , elija Todos los programas, Microsoft SQL Servery, a continuación, haga clic en SQL Server Data Tools.
Creación de un nuevo proyecto de Integration Services.
1. En la barra de menús, haga clic en Archivo , elija Nuevoy, después, haga clic en Proyecto.
2. Expanda Business Intelligence en el panel de la izquierda y haga clic en Integration Services en la vista de árbol.
3. Seleccione Proyecto de Integration Services si aún no está seleccionado.
4. Escriba SSISPackagePublishing como nombre del proyecto.
5. Indique una ubicación donde guardar el proyecto.
6. Haga clic en Aceptar para cerrar el cuadro de diálogo Nuevo proyecto .
Arrastre el componente Flujo de datos desde el cuadro de herramientas de SSIS a la superficie de diseño de la pestaña Flujo de control .
Haga doble clic en el componente Flujo de datos en Flujo de control para abrir el diseñador de flujos de datos.
Arrastre un componente de origen desde el cuadro de herramientas al diseñador de flujos de datos y configúrelo para que extraiga datos de un origen de datos.
1. En este tutorial crearemos una base de datos de prueba, TestDB , con una tabla, Employee. Cree la tabla con tres columnas: ID, FirstName y LastName.
2. Establezca ID como clave principal.
3. Inserte dos registros con los siguientes datos.
  
  id FIRSTNAME LASTNAME
  
  1 John Doe
  
  2 Julia Doe
4. Arrastre el componente Origen de OLE DB desde el cuadro de herramientas de SSIS hasta el diseñador de flujos de datos.
5. Configure el componente para que extraiga datos de la tabla Employee de la base de datos TestDB . Seleccione (local).TestDB en Administrador de conexiones OLE DB, Tabla o vista en Modo de acceso a datosy [dbo].[Employee] en Nombre de la tabla o la vista.
Ahora, arrastre el Destino de streaming de datos desde el cuadro de herramientas al flujo de datos. Este componente debería estar en la sección Común del cuadro de herramientas.
Conecte el componente Origen de OLE DB del flujo de datos al componente Destino de streaming de datos .
Compile e implemente el proyecto de SSIS en el catálogo de SSIS.
1. Haga clic en Proyecto en la barra de menús y, después, haga clic en Implementar.
2. Siga las instrucciones del asistente para implementar el proyecto en el catálogo de SSIS en el servidor de base de datos local. En el siguiente ejemplo, se usa Power BI como nombre de carpeta y SSISPackagePublishing como nombre del proyecto en el catálogo de SSIS.

id	FIRSTNAME	LASTNAME
1	John	Doe
2	Julia	Doe

Paso 2: Usar el asistente para la publicación de fuentes de distribución de datos de SSIS para publicar el paquete SSIS como una vista SQL

En este paso, usará el asistente para la publicación de fuentes de distribución de datos de SQL Server Integration Services (SSIS ) para publicar el paquete SSIS como una vista en la base de datos de SQL Server. Los datos de salida del paquete se podrán usar realizando una consulta en esta vista.

El asistente para la publicación de fuentes de distribución de datos de SSIS crea un servidor vinculado mediante el proveedor OLE DB para SSIS (SSISOLEDB) y, tras ello, crea una vista SQL que consta de una consulta en el servidor vinculado. Esta consulta incluye el nombre de la carpeta, el nombre del proyecto y el nombre del paquete en el catálogo de SSIS.

En el tiempo de ejecución, la vista envía la consulta al proveedor OLE DB para SSIS a través del servidor vinculado creado. El proveedor OLE DB para SSIS ejecuta el paquete especificado en la consulta y devuelve el conjunto de resultados tabulares a la consulta.

Para iniciar el Asistente para la publicación de fuentes de distribución de datos de SSIS , ejecute ISDataFeedPublishingWizard.exe desde C:\Archivos de programa\Microsoft SQL Server\130\DTS\Binn o haga clic en Microsoft SQL Server 2016\SQL Server 2016 Data Feed Publishing Wizard en Inicio\Todos los programas.
Haga clic en Siguiente en la página Introducción .
En la página Configuración del paquete , haga lo siguiente:
1. Escriba el nombre de la instancia de SQL Server que contiene el catálogo de SSIS o haga clic en Examinar para seleccionar el servidor.
2. Haga clic en Examinar junto al campo Ruta de acceso, vaya al catálogo de SSIS, seleccione el paquete SSIS que quiera publicar (por ejemplo, SSISDB->SSISPackagePublishing->Package.dtsx) y haga clic en Aceptar.
3. En las pestañas Parámetros de paquete, Parámetros de proyecto y Administradores de conexiones al final de la página, escriba los valores de configuración de los parámetros de paquete, los parámetros de proyecto o los administradores de conexiones relativos al paquete. También puede indicar que se use un entorno de referencia para ejecutar el paquete y enlazar los parámetros de paquete y de proyecto a variables de entorno.
  
  Se aconseja enlazar los parámetros confidenciales a variables de entorno, ya que esto evita que el valor de un parámetro confidencial se almacene como texto sin formato en la vista SQL creada por el asistente.
4. Haga clic en Siguiente para pasar a la página Configuración de publicación .
En la página Configuración de publicación , haga lo siguiente:
1. Seleccione la base de datos relativa a la vista que se va a crear.
2. Escriba un nombre para la vista. También puede seleccionar una vista existente de la lista desplegable.
3. En la lista Configuración , especifique el nombre del servidor vinculado que se va a asociar a la vista. Si el servidor vinculado aún no existe, el asistente lo creará antes de crear la vista. Aquí también puede establecer los valores de User32BitRuntime y Timeout .
4. Haga clic en el botón Avanzadas . Debería abrirse el cuadro de diálogo Configuración avanzada .
5. Haga lo siguiente en el cuadro de diálogo Configuración avanzada:
  1. Especifique el esquema de base de datos en el que quiere crear la vista (campo Esquema).
  2. Especifique si los datos se van a cifrar antes de enviarlos a través de la red (campo Cifrar). Consulte el tema Usar el cifrado sin validación para ver más detalles sobre esta configuración y la configuración de TrustServerCertificate.
  3. Especifique si se puede usar un certificado de servidor autofirmado cuando la opción de cifrado esté habilitada (campoTrustServerCertificate ).
  4. Haga clic en Aceptar para cerrar el cuadro de diálogo Configuración avanzada .
6. Haga clic en Siguiente para pasar a la página Validación .
En la página Validación , revise los resultados correspondientes a la validación de los valores de todas las configuraciones. En el siguiente ejemplo, se abre una advertencia sobre la existencia de un servidor vinculado, ya que no hay un servidor vinculado en la instancia de SQL Server seleccionada. Si ve Error en Resultado, mantenga el puntero sobre Error para ver más detalles al respecto. Por ejemplo, si no se habilitó la opción Permitir InProcess del proveedor SSISOLEDB, obtendrá un error en la acción de configuración del servidor vinculado.
Haga clic en Guardar informe para guardar el informe como un archivo XML.
Haga clic en Siguiente en la página Validación para pasar a la página Resumen .
Revise la selección en la página Resumen y haga clic en Publicar para iniciar el proceso de publicación, en el que se creará el servidor vinculado (si aún no existe en el servidor) con el que, luego, se creará la vista.

Ahora los datos de salida del paquete se pueden consultar ejecutando la siguiente instrucción SQL en la base de datos TestDB: SELECT * FROM [SSISPackageView].
Haga clic en Guardar informepara guardar el informe como un archivo XML.
Revise los resultados del proceso de publicación y haga clic en Finalizar para cerrar el asistente.

Nota

No se admiten los siguientes tipos de datos: text, ntext, image, nvarchar(max), varchar(max) ni varbinary(max).

Paso 3: Probar la vista SQL

Aquí ejecutará la vista SQL creada en el asistente para la publicación anterior.

Inicie SQL Server Management Studio.
Expandaa <nombre del equipo>, Bases de datos, <base de datos seleccionada en el asistente>y Vistas.
Haga clic con el botón derecho en <vista creada por el asistente> creada por el asistente y haga clic en Seleccionar las primeras 1000 filas.
Confirme que ve los resultados del paquete SSIS.

Paso 4: Comprobar la ejecución del paquete SSIS

En este paso comprobará que el paquete SSIS se ha ejecutado.

En SQL Server Management Studio, expanda Catálogos de Integration Services, SSISDBy la carpeta en la que está el proyecto de SSIS. Después, expanda Proyectos, luego el nodo del proyecto y, por último, Paquetes.
Haga clic con el botón derecho en el paquete SSIS, seleccione Informese Informes estándary, después, haga clic en Todas las ejecuciones.
La ejecución del paquete SSIS debería aparecer reflejada en el informe.

Nota

En un equipo con Windows Vista Service Pack 2, es posible que el informe contenga dos ejecuciones de paquete SSIS, una correcta y otra con errores. Omita esta última, ya que se debe a un problema conocido de esta versión.

Más información

El asistente para la publicación de fuentes de distribución de datos realiza los siguientes pasos importantes:

Crea un servidor vinculado y lo configura para que use el proveedor OLE DB para SSIS.
Crea una vista SQL en la base de datos especificada, que consulta el servidor vinculado con la información del catálogo relativa al paquete seleccionado.

En esta sección se describen otros procedimientos para crear un servidor vinculado y una vista SQL sin recurrir al asistente para la publicación de fuentes de distribución de datos. En ella también encontrará más información sobre cómo usar la función OPENQUERY con el proveedor OLE DB para SSIS.

Crear un servidor vinculado con el proveedor OLE DB para SSIS

Cree un servidor vinculado con el proveedor OLE DB para SSIS (SSISOLEDB); para ello, ejecute la siguiente consulta en SQL Server Management Studio.

  
USE [master]  
GO  
  
EXEC sp_addlinkedserver  
@server = N'SSISFeedServer',  
@srvproduct = N'Microsoft',  
@provider = N'SSISOLEDB',  
@datasrc = N'.'  
GO

Crear una vista con un servidor vinculado y la información del catálogo de SSIS

En este paso, creará una vista SQL que ejecuta una consulta en el servidor vinculado que creó en la sección anterior. Esta consulta incluye el nombre de la carpeta, el nombre del proyecto y el nombre del paquete en el catálogo de SSIS.

En el tiempo de ejecución, cuando la vista se ejecuta, la consulta de servidor vinculado definida en la vista inicia el paquete SSIS especificado en dicha consulta y recibe el resultado del paquete como un conjunto de resultados tabulares.

Antes de crear la vista, escriba y ejecute la siguiente consulta en la ventana de nueva consulta. OPENQUERY es una función de conjunto de filas compatible con SQL Server. Ejecuta la consulta de paso a través especificada en el servidor vinculado por medio del proveedor OLE DB asociado al servidor vinculado. Se puede hacer referencia a OPENQUERY en la cláusula FROM de una consulta como si fuera un nombre de tabla. Para obtener más información, consulte la documentación de OPENQUERY en MSDN Library .
```
SELECT * FROM OPENQUERY(SSISFeedServer,N'Folder=Eldorado;Project=SSISPackagePublishing;Package=Package.dtsx')   
GO  
```
Importante

Actualice el nombre de carpeta, el nombre del proyecto y el nombre del paquete si procede. Si la función OPENQUERY genera un error, en SQL Server Management Studio, expanda Objetos de servidor, Servidores vinculadosy Proveedoresy, después, haga doble clic en el proveedor SSISOLEDB y asegúrese de que la opción Permitir InProcess está activada.

Ejecute la siguiente consulta para crear una vista en la base de datos TestDB para este tutorial.


USE [TestDB]   
GO   

CREATE VIEW SSISPackageView AS   
SELECT * FROM OPENQUERY(SSISFeedServer, 'Folder=Eldorado;Project=SSISPackagePublishing;Package=Package.dtsx')   
GO

Ejecute la siguiente consulta para comprobar la vista.
```
SELECT * FROM SSISPackageView  
```

Función OPENQUERY

La sintaxis de la función OPENQUERY es:

SELECT * FROM OPENQUERY(<LinkedServer Name>, N'Folder=<Folder Name from SSIS Catalog>; Project=<SSIS Project Name>; Package=<SSIS Package Name>; Use32BitRuntime=[True | False];Parameters="<parameter_name_1>=<value1>; parameter_name_2=<value2>";Timeout=<Number of Seconds>;')

Los parámetros de carpeta, proyecto y paquete son obligatorios, mientras que los de Use32BitRuntime y Timeout son opcionales.

El valor de Use32BitRuntime puede ser 0, 1, true o false. Indica si el paquete se debe ejecutar con un tiempo de ejecución de 32 bits (1 o true) cuando la plataforma de SQL Server sea de 64 bits.

El valor de Timeout indica el número de segundos que el proveedor OLE DB para SSIS puede esperar antes de que lleguen datos nuevos procedentes del paquete SSIS. El valor predeterminado es 60 segundos. Se puede especificar un valor entero de tiempo de espera de entre 20 y 32 000.

Los parámetros contienen el valor tanto de los parámetros de paquete como de los parámetros del proyecto. Las reglas de los parámetros son las mismas que las de los parámetros de DTExec.

En la siguiente lista se especifican los caracteres especiales que se pueden usar en la cláusula de consulta:

Comilla simple ('): se puede usar en el estándar OPENQUERY. En caso de que quiera usar comillas simples en la cláusula de consulta, use dos comillas simples (").
Comillas dobles ("): los parámetros que forman parte de la consulta se insertan entre comillas dobles. Si el valor de parámetro en sí ya contiene comillas dobles, use el carácter de escape. Por ejemplo, ".
Corchetes de apertura y cierre ([ y ]): estos caracteres sirven para indicar espacios iniciales o finales. Por ejemplo, “[ algunos espacios ]” representa la cadena “ algunos espacios ” con un espacio inicial y un espacio final. Si estos caracteres se usan en la cláusula de consulta, deben ir acompañados de un carácter de escape. Por ejemplo: \[ y \].
Barra diagonal (\): cada \ usada en la cláusula de consulta tiene que ir acompañada de un carácter de escape. Por ejemplo, \\ se evalúa como \ en la cláusula de consulta.

Consulte también

Destino de streaming de datos
Configuración del destino de streaming de datos

Compartir a través de