Asistente Import/Export – Tarea Bulk Insert

Asistente Import/Export

Este asistente proporciona una sencilla ETL y es fácil de usar para las operaciones básicas de transferencia de datos. Con este asistente puede elegir un origen, un destino y mapear columnas con pocas restricciones sobre las opciones de transferencia de datos.

Alistarse

Necesita tener las bases de datos de muestra AdventureWorks2014 y AdventureWorksLT2014 instalados.

Crear una nueva base de datos en SQL Server. Abra el SQL Server Management Studio (SSMS) desde el menú Inicio | Microsoft SQL Server 2014 | SQL Server Management Studio. En el SSMS, conéctese a la instancia del equipo local y cree una nueva base de datos. Nombre esta base de datos como AIE_SSIS.

Tenga en cuenta que debe ejecutar SSMS como administrador, para ello simplemente haga clic derecho en SQL Server Management Studio de la ruta anterior y seleccione Ejecutar como administrador.

Ejercicio

1.    Abra el Asistente Import/Export; hay tres maneras que puede hacerlo:

·         En la ventana Ejecutar, escriba DtsWizard.

·         Abra el asistente desde la siguiente dirección: Inicio | Todos los programas | Microsoft SQL Server 2014 | Importación y Exportación de Datos.

·         En SSMS, haga clic derecho en cualquier base de datos y, a continuación, en Tareas, seleccione Importar datos o Exportar datos.

2.    En el primer paso del Asistente Import/Export, aparecerá una página de bienvenida. Haga clic en Next para entrar a Seleccionar un Origen de Datos, en este paso se debe elegir de donde viene el Data Source. El Data Source puede ser cualquier origen; de una base de datos Oracle o SQL Server para cualquier otra base de datos, archivos planos (como .txt y .csv) o incluso archivos Excel, la gama de orígenes y destinos está basada en los proveedores de datos instalados en la máquina. Para este ejemplo, deje la opción Data Source como su opción por defecto que es SQL Server Native Client 11.0.

3.    Queremos exportar dos tablas de la base de datos AdventureWorks2012 a otra base de datos. Por lo tanto, dejar Server name como (local) o un solo punto (.) o si tiene una instancia con nombre podría utilizar .\<Instance-Name>, y en la sección Autenticación deje el tipo de autenticación como la Autenticación de Windows. Esta opción usará tu cuenta de Windows para conectarse a la base de datos, por lo que, obviamente, la cuenta de Windows debe tener acceso de lectura a la base de datos originaria.

4.    En el cuadro desplegable Base de datos, seleccione AdventureWorks2014 de la lista. Luego haga clic en Siguiente y vaya al siguiente paso. 

5.    El siguiente paso es requerido para elegir un Destino, por lo tanto, proporcionar los detalles de conexión del destino de datos (los tipos de destinos pueden diferir de bases de datos a archivos planos). Para este ejemplo dejar el destino como el valor por defecto que es SQL Server Native Client 11.0.

6.    Establecer el Nombre de servidor a (local) o a un punto (.) Para conectar la instancia predeterminada de la máquina actual. Establezca la Autenticación como la Autenticación de Windows. Seleccione AIE_SSIS en la lista desplegable Base de datos. Luego haga clic en Siguiente para continuar los pasos del asistente.

7.    En el paso Especificar copia de tabla o consulta puede elegir entre seleccionar un nombre de tabla o vista para una base de datos de origen o escribir una consulta para obtener los datos de un origen. Para este ejemplo, seleccione la opción Copiar los datos de una o varias tablas o vistas.

8.    Luego, una lista de tablas y vistas de la base de datos de origen aparecerá. Para este ejemplo, seleccione HumanResource.Department, Person.Address y Production.Product. Luego haga clic en Siguiente.

9.    El paso Guardar y Ejecutar el paquete es el siguiente; que ofrece la posibilidad de guardar todos los ajustes y configuraciones que ha establecido para un paquete SSIS. Guardaremos este paquete para ver como luce el paquete SSIS. Marque la opción Guardar el paquete SSIS y haga clic en el botón Siguiente.

10.  En el siguiente paso, que es el diálogo Guardar el paquete SSIS, escriba el nombre AsistenteImportExport y elija una ubicación para el archivo de paquete. Luego haga clic en Siguiente.

 

11.  Ahora un resumen de todos los ajustes que ha hecho aparece aquí; tras revisar el resumen, haga clic en Finalizar.

12.  Después de hacer clic en el botón Finalizar, el Asistente Import/Export se mostrará y podemos ver todos los mensajes generados durante la ejecución del paquete. Se muestra el número de filas copiadas, o cualquier otra información, como el número de filas transferidas, validadas, y cualquier otra acción.

13.  Abrir y ver el informe de la ejecución haciendo clic en el botón Informe.

14.  Cierre el asistente y abra el SSMS para comprobar la base de datos de destino, se puede ver aquí las tablas transferidas con datos.

Funcionamiento

Este escenario simple exporta algunas tablas de la base de datos AdventureWorks a una base de datos vacía. En los últimos pasos, guardamos todo el escenario de transferencia de datos a un paquete SSIS en un sistema de archivos.

Con el Asistente Import/Export puede importar o exportar datos desde un origen a un destino, este es el escenario ETL más simplista. En el paso Seleccionar un origen de datos realiza la parte Extracción del ETL y obtiene los datos de la base de datos del SQL Server (origen de datos). El segundo paso, fue Seleccionar un destino, fue configurado durante la parte Carga del ETL. La Carga indica dónde se deben exportar los datos; exportamos datos a una base de datos SQL Server. En este primer escenario de ejemplo no tenemos ninguna etapa específica de Transformación.

Cuando elige tabla(s) de la base de datos AdventureWorks en la sección Seleccione Tablas o Vistas de Origen, las tablas que todavía no existen en la base de datos de destino serán creadas primero.

Después de comparar las columnas y los metadatos, los datos serán transferidos y un resumen de todos los registros mostrará lo que pasó durante la ejecución.

Guardamos este paquete para el sistema de archivos; también podemos guardar los paquetes a un SQL Server.

Bulk Insert

Bulk Insert es utilizado en escenarios donde un gran volumen de datos que debe insertarse rápidamente en una base de datos de destino. Hay varias maneras de tratar con esto bajo SSIS, pero la diferencia estará en la velocidad de insertar tales volúmenes de datos en una base de datos. Este enfoque es utilizado para asegurar la velocidad al copiar los datos o realizar las transformaciones mientras los datos están moviéndose.

Algunas restricciones deben ser consideradas antes de utilizar este enfoque:

Ø  Los datos deben ser transferidos desde un archivo de texto a una tabla o vista de SQL Server.

Ø  El destino debe ser una tabla o vista en una base de datos de SQL Server.

Ø  Sólo miembros del rol de servidor fijo sysadmin pueden ejecutar un paquete con esta tarea.

Alistarse

Asegúrese de que la base de datos AIE_SSIS esté disponible en el SQL Server Management Studio (SSMS).

En este ejercicio se requerirá la tabla Customers de la base de datos de muestra AIE_SSIS para almacenar cada valor suministrado desde el archivo plano.

Para prepararse para este ejercicio, utilice los siguientes pasos:

1.    Abra el SQL Server Management Studio (SSMS) y ejecute la sentencia SQL siguiente.

USE AIE_SSIS

GO

CREATE TABLE [dbo].[Customers](

   [ID] [int] NOT NULL,

   [Firstname] [varchar](50) NULL,

   [Lastname] [varchar](50) NULL,

   [Ammount] [decimal](18, 2) NULL,

 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED

(

   [ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

2.    Abra el SQL Server Data Tools (SSDT) y cree un nuevo proyecto de SSIS.

3.    Proporcione un nombre (TareaBulkInsert por ejemplo) y una ubicación para el proyecto SSIS y proceda.

4.    Seleccione el paquete creado por defecto y renómbrelo a CargaBulk.dtsx.

Ejercicio

Considere el escenario donde SSIS necesita periódicamente importar un gran volumen de datos en un repositorio central. Esta importación debe ser lo más rápido posible y ser basado en un archivo plano producido por un Sistema Operativo (OS).

1.    Como se mencionó anteriormente, es necesario que la conexión de origen sea un archivo plano. Por lo tanto, y antes de configurar la tarea principal, debería ser creada una conexión de archivos planos.

2.    Creamos un archivo plano, lo nombramos EjemploArchivoTxt, con el siguiente contenido.

 

3.    En el panel Connection Managers, le damos clic derecho y seleccionamos New Flat File Connection.

4.    Buscamos el archivo plano EjemploArchivoTxt.txt, vamos a la pestaña Columns, mantenemos todas las propiedades predeterminadas y pulsamos OK. La renombramos como cmArchivoPlano.

5.    Agregue una tarea Bulk Insert al Control Flow para la inserción masiva de los datos de origen.

6.    Abra la tarea para la edición.

7.    En la ficha Connection del editor de la tarea, seleccione la casilla desplegable de la propiedad File y luego seleccione la conexión de archivo plano creado en el paso anterior que apunta al origen.

8.    Seleccione el valor que establece la tabla de destino Customers a la base de datos de ejemplo AIE_SSIS en el cuadro desplegable DestinationTable.

 

9.    Para ignorar la primera fila en el archivo plano que corresponde a la cabecera, cambie la propiedad FirstRow de 1 a 2.

10.  Ejecutar el paquete pulsando F5. Si la ejecución tiene éxito, ahora deben existir los nuevos datos en la base de datos AIE_SSIS.

Funcionamiento

La tarea Bulk Insert proporciona un método para insertar registros desde un archivo plano en una base de datos en modo por lotes. El número de línea de línea de inicio y el número de línea final, el delimitador de columnas, los delimitadores de fila, y algunas otras opciones son configurables.

La tarea Bulk Insert utiliza la operación integrada de inserción masiva de SQL Server.

En la conexión de destino, hicimos una conexión a una base de datos y a una tabla originaria para la operación de inserción masiva. La conexión de origen apunta a un archivo plano que es sólo necesaria para crear el administrador de conexión del archivo.

Format define la forma en que está estructurado el archivo de origen. Hay dos maneras de proporcionar el formato, y nuestra primera opción es Specify. Cuando se selecciona Specify en la propiedad desplegable Format, es posible especificar el RowDelimiter y ColumnDelimiter exactamente.

RowDelimiter define cómo son separadas las filas en el archivo de origen (por defecto, este valor es {CR}{LF}, lo que significa una nueva línea, pero es posible especificar cualquier carácter o combinación de caracteres aquí como delimitador). ColumnDelimiter define cómo se separan las columnas. Delimitadores de columnas comunes son coma (,) punto y coma (;) o una línea vertical (|), pero podemos definir cualquier otro conjunto de caracteres(s) como ColumnDelimiter.

Cuando se selecciona Use File en el cuadro desplegable Format, otra propiedad aparecerá y es posible insertar la dirección física de un archivo de formato. El formato de archivo es un archivo de sintaxis que es útil cuando se requiere cargar datos en SQL Server con el modo de inserción masiva. Para obtener más información acerca de cómo el archivo de formato luce y cómo crear un archivo de formato, echa un vistazo a http://msdn.microsoft.com/en-us/library/ms178129.aspx.

El archivo de formato es muy útil cuando es necesario definir estructuras de datos en un archivo plano de formas más avanzadas que una sola fila o columna delimitadora. Con un archivo de formato, es posible definir cada columna de flujo de datos, su tipo de dato, y algunas otras opciones avanzadas.