¿Cómo crear una tabla temporal en la tarea de flujo de control de SSIS y luego usarla en la tarea de flujo de datos?

Tengo un flujo de control donde creo una base de datos temporal y una tabla en a con un Comando T-SQL. Cuando agrego un flujo de datos me gustaría consultar la tabla, pero no puedo porque la tabla no existe para tomar información de. Cuando bash, recibo errores sobre el inicio de sesión porque la base de datos no existe (todavía). Tengo la validación de demora como verdadera.

Si creo la base de datos y la tabla manualmente, agrego el flujo de datos con la consulta y dejo caer la base de datos que contiene, pero no parece una solución limpia.

Si hay una mejor manera de crear una base de datos provisional y consultarla en flujos de datos, por favor avíseme.

Solución:

Establezca la propiedad RetainSameConnection en Connection Manager en True para que la tabla temporal creada en una tarea de Control Flow pueda conservarse en otra tarea.

Aquí hay un paquete SSIS de muestra escrito en SSIS 2008 R2 que ilustra el uso de tablas temporales.

Tutorial:

Cree un procedimiento almacenado que creará una tabla temporal llamada ##tmpStateProvince y completará con pocos registros. El paquete SSIS de muestra primero llamará al procedimiento almacenado y luego buscará los datos de la tabla temporal para completar los registros en otra tabla de base de datos. El paquete de muestra usará la base de datos llamada Sora Use la secuencia de comandos de procedimiento almacenada a continuación.

 USE Sora; GO CREATE PROCEDURE dbo.PopulateTempTable AS BEGIN SET NOCOUNT ON; IF OBJECT_ID('TempDB..##tmpStateProvince') IS NOT NULL DROP TABLE ##tmpStateProvince; CREATE TABLE ##tmpStateProvince ( CountryCode nvarchar(3) NOT NULL , StateCode nvarchar(3) NOT NULL , Name nvarchar(30) NOT NULL ); INSERT INTO ##tmpStateProvince (CountryCode, StateCode, Name) VALUES ('CA', 'AB', 'Alberta'), ('US', 'CA', 'California'), ('DE', 'HH', 'Hamburg'), ('FR', '86', 'Vienne'), ('AU', 'SA', 'South Australia'), ('VI', 'VI', 'Virgin Islands'); END GO 

Cree una tabla denominada dbo.StateProvince que se utilizará como la tabla de destino para rellenar los registros de la tabla temporal. Use la siguiente secuencia de comandos de la tabla de creación para crear la tabla de destino.

 USE Sora; GO CREATE TABLE dbo.StateProvince ( StateProvinceID int IDENTITY(1,1) NOT NULL , CountryCode nvarchar(3) NOT NULL , StateCode nvarchar(3) NOT NULL , Name nvarchar(30) NOT NULL CONSTRAINT [PK_StateProvinceID] PRIMARY KEY CLUSTERED ([StateProvinceID] ASC) ) ON [PRIMARY]; GO 

Cree un paquete de SSIS usando Business Intelligence Development Studio (BIDS) . Haga clic con el botón derecho en la pestaña Administradores de conexiones en la parte inferior del paquete y haga clic en New OLE DB Connection... para crear una nueva conexión para acceder a la base de datos de SQL Server 2008 R2 .

Administradores de conexión: nueva conexión OLE DB

Haga clic en New... en Configurar OLE DB Connection Manager .

Configurar OLE DB Connection Manager - Nuevo

Realice las siguientes acciones en el diálogo de Connection Manager .

  • Seleccione Native OLE DB\SQL Server Native Client 10.0 del proveedor ya que el paquete se conectará a la base de datos de SQL Server 2008 R2
  • Ingrese el nombre del Servidor , como MACHINENAME\INSTANCE
  • Seleccione Use Windows Authentication desde Iniciar sesión en la sección del servidor o la que prefiera.
  • Seleccione la base de datos desde Select or enter a database name , la muestra usa el nombre de la base de datos Sora .
  • Haga clic en Test Connection
  • Haga OK en OK en el mensaje Probar conexión exitosa .
  • Haga OK en OK en Connection Manager

Administrador de conexión

La conexión de datos recién creada aparecerá en Configure OLE DB Connection Manager . Haga clic en OK .

Configurar OLE DB Connection Manager - Creado

El administrador de conexión OLE DB KIWI\SQLSERVER2008R2.Sora aparecerá debajo de la pestaña Connection Manager en la parte inferior del paquete. Haga clic derecho en el administrador de conexión y haga clic en Properties

Propiedades de Connection Manager

Establezca la propiedad RetainSameConnection en la conexión KIWI\SQLSERVER2008R2.Sora en el valor True .

Propiedad RetainSameConnection en Connection Manager

Haga clic derecho en cualquier lugar dentro del paquete y luego haga clic en Variables para ver el panel de variables. Crea las siguientes variables.

  • Una nueva variable llamada PopulateTempTable del tipo de datos String en el scope del paquete SO_5631010 y establece la variable con el valor EXEC dbo.PopulateTempTable .

  • Una nueva variable llamada FetchTempData del tipo de datos String en el ámbito del paquete SO_5631010 y establece la variable con el valor SELECT CountryCode, StateCode, Name FROM ##tmpStateProvince

Variables

Arrastre y suelte una Execute SQL Task en la pestaña Flujo de control . Haga doble clic en la tarea Ejecutar SQL para ver el Editor de tareas de SQL Execute .

En la página General del Editor de tareas de SQL Execute , realice las siguientes acciones.

  • Establezca el Nombre para Create and populate temp table
  • Establezca el tipo de conexión a OLE DB
  • Establezca la conexión a KIWI\SQLSERVER2008R2.Sora
  • Seleccionar Variable de SQLSourceType
  • Seleccionar User::PopulateTempTable de SourceVariable
  • Click OK

Ejecutar SQL Task Editor

Arrastre y suelte una Data Flow Task en la pestaña Flujo de control . Cambie el nombre de la tarea de flujo de datos como Transfer temp data to database table . Conecte la flecha verde de la tarea Ejecutar SQL a la tarea de flujo de datos .

Pestaña Control Flow

Haga doble clic en la Data Flow Task para cambiar a la pestaña Flujo de datos . Arrastre y suelte un OLE DB Source en la pestaña Flujo de datos . Haga doble clic en OLE DB Source para ver el OLE DB Source Editor .

En la página Connection Manager del Editor de origen OLE DB , realice las siguientes acciones.

  • Seleccione KIWI\SQLSERVER2008R2.Sora del Administrador de conexión OLE DB
  • Seleccione el SQL command from variable desde el modo de acceso a datos
  • Seleccione User::FetchTempData from Variable name
  • Haga clic en la página Columns

Editor de origen OLE DB - Connection Manager

Al hacer clic en la página Columns en OLE DB Source Editor se mostrará el siguiente error porque la tabla ##tmpStateProvince especificada en la variable de comando de origen no existe y SSIS no puede leer la definición de la columna.

Mensaje de error

Para corregir el error, ejecute la sentencia EXEC dbo.PopulateTempTable utilizando SQL Server Management Studio (SSMS) en la base de datos Sora para que el procedimiento almacenado cree la tabla temporal. Después de ejecutar el procedimiento almacenado, haga clic en la página Columns en OLE DB Source Editor , verá la información de la columna. Haga clic en OK .

OLE DB Source Editor - Columnas

Arrastre y suelte el OLE DB Destination en la pestaña Flujo de datos . Conecte la flecha verde de OLE DB Source al OLE DB Destination . Haga doble clic en OLE DB Destination para abrir OLE DB Destination Editor .

En la página Connection Manager del Editor de destino OLE DB , realice las siguientes acciones.

  • Seleccione KIWI\SQLSERVER2008R2.Sora del Administrador de conexión OLE DB
  • Seleccione Table or view - fast load desde el modo de acceso a datos
  • Seleccione [dbo].[StateProvince] from Name of the table or the view
  • Haga clic en la página Mappings

Editor de destino OLE DB - Connection Manager

Haga clic en la página Mappings en OLE DB. El Editor de destino correlacionará automáticamente las columnas si los nombres de las columnas de entrada y salida son los mismos. Haga clic en OK . Columna StateProvinceID no tiene una columna de entrada coincidente y se define como una columna de IDENTITY en la base de datos. Por lo tanto, no se requiere mapeo.

Editor de destino OLE DB - asignaciones

La pestaña Flujo de datos debería verse más o menos así después de configurar todos los componentes.

Pestaña Flujo de datos

Haga clic en la pestaña OLE DB Source en Flujo de datos y presione F4 para ver Properties . Establezca la propiedad ValidateExternalMetadata en False para que SSIS no intente comprobar la existencia de la tabla temporal durante la fase de validación de la ejecución del paquete.

Establecer ValidateExternalMetadata

Ejecute la consulta select * from dbo.StateProvince en SQL Server Management Studio (SSMS) para encontrar el número de filas en la tabla. Debe estar vacío antes de ejecutar el paquete.

Filas en la tabla antes de la ejecución del paquete

Ejecuta el paquete. Control Flow muestra una ejecución exitosa.

Ejecución del paquete - pestaña Flujo de control

En la pestaña Flujo de datos, observará que el paquete procesó correctamente 6 filas. El procedimiento almacenado creado al principio de esta publicación insertó 6 filas en la tabla temporal.

Ejecución del paquete - pestaña Flujo de datos

Ejecute la consulta select * from dbo.StateProvince en SQL Server Management Studio (SSMS) para encontrar las 6 filas insertadas con éxito en la tabla. Los datos deben coincidir con las filas que se encuentran en el procedimiento almacenado.

Filas en la tabla después de la ejecución del paquete

El ejemplo anterior ilustra cómo crear y usar tablas temporales dentro de un paquete.

Llego tarde a esta fiesta, pero me gustaría agregar un poco a la respuesta completa y excelente del usuario756519. No creo que la propiedad “RetainSameConnection en Connection Manager” sea relevante en esta instancia en función de mi experiencia reciente. En mi caso, el punto relevante fue su consejo para establecer “ValidateExternalMetadata” en False.

Estoy usando una tabla temporal para facilitar la copia de datos de una base de datos (y servidor) a otra, de ahí que el motivo “RetainSameConnection” no sea relevante en mi caso particular. Y tampoco creo que sea importante lograr lo que está sucediendo en este ejemplo, por completo que sea.

    Intereting Posts