sql server - txt - ¿Cómo dividir los datos de la tabla en archivos separados de Excel con un paquete SSIS?

ssis excel destination create new file (4)

Estoy trabajando con un conjunto de datos de SQL Server que me gustaría incluir en un grupo de archivos de Excel. Esta tarea debe ser automatizada para ejecutarse mensualmente. Los datos se ven como

Site ID FirstName LastName ------ ------- --------- --------- North 111 Jim Smith North 112 Tim Johnson North 113 Sachin Tedulkar South 201 Horatio Alger South 205 Jimi Hendrix South 215 Bugs Bunny

Me gustaría que los resultados se vean como

In Excel file named North.xls ID FirstName LastName 111 Jim Smith 112 Tim Johnson 113 Sachin Tedulkar In Excel file named South.xls ID FirstName LastName 201 Horatio Alger 205 Jimi Hendrix 215 Bugs Bunny

Hay entre 70 y 100 valores en la columna Sitio que me gustaría dividir. Estoy utilizando SSIS para realizar esta tarea, pero me estoy atascado después de haber extraído los datos de SQL Server con una tarea de origen OLE DB. ¿Qué debería venir después? Si hay una manera más fácil de hacer esto usando otras herramientas, también estoy abierto a eso.

Creo que SSIS es una buena herramienta para usar, y tienes un par de opciones.

Básicamente, puede usar los objetos divididos multidifusión y condicional para lograr lo que desea.

Así es como lo haría:

1) Crearía los dos archivos de MS Excel con todos los datos en ellos, ya que me gustaría verlos. Elimine los datos y consérvelos como archivos de plantilla y haga una copia para los archivos completos.

2) Configure las conexiones de archivos a estos archivos en el administrador de conexión.

3) Realice una tarea del sistema de archivos que sobrescriba los archivos completos con la plantilla al inicio del trabajo (otras formas de hacerlo, pero esta es la mejor).

4) Agregue la tarea de flujo de datos y en ella suelte en una fuente OLE DB, una multidifusión, dos divisiones condicionales y dos destinos de MS Excel.

5) Configura cada uno de estos y deberías haber terminado. La configuración es bastante intuitiva y en el orden que tengo arriba.

No estoy seguro de si esto funcionaría, pero coloque todas las consultas de condición en una tabla junto con otro campo para un nombre de archivo. Luego use un ciclo for para recorrer cada uno de ellos y modificar dinámicamente la cláusula select de la tarea de transformación. SSIS llama a estas modificaciones dinámicas - expresiones.

Haces un seleccionar en tu tabla de declaraciones / condiciones y luego lo lanzas en una variable de objeto. La variable de objeto se usa luego en el ciclo for.

Lo único de lo que no estoy seguro es de la asignación en el nombre de archivo de Excel.

editar: También encontré esto, que usa un servidor vinculado http://codebetter.com/blogs/raymond.lewallen/archive/2005/05/04/62781.aspx

Probablemente ya hayas encontrado una respuesta a tu pregunta. Esto es para otros usuarios que puedan tropezar con esta pregunta. El siguiente ejemplo muestra cómo esto se puede lograr dinámicamente para cualquier cantidad de Sitios que puedan estar presentes. El ejemplo se creó utilizando SSIS 2008 R2 con la base de datos SQL Server 2008 R2 .

Proceso paso a paso:

Ejecute los scripts dados en la sección Scripts SQL en una base de datos de SQL Server para crear una tabla llamada dbo.Source y rellenar con datos (similar a los datos dados en la pregunta). También crea un procedimiento almacenado llamado dbo.GetSiteData .
En la conexión del paquete SSIS, cree una conexión OLE DB para conectarse al servidor SQL. Llegaremos a Excel connect más adelante en los pasos.
En el paquete SSIS, cree 6 variables como se muestra en la captura de pantalla n. ° 1 . Complete la variable Sitio con valor Template , este es un valor de muestra que se usará para evaluar otras expresiones. Establezca la variable SQLUniqueSites con el valor SELECT DISTINCT Site FROM dbo.SourceData . Establezca la variable ExcelFolder con el valor C:/temp/
Seleccione la variable ExcelFilePath y presione F4 para ver Propiedades. Cambie la propiedad EvaluateAsExpression a True y establezca la propiedad Expression con el valor @[User::ExcelFolder] + @[User::Site] + ".xls" . Referencia captura de pantalla # 2 .
Seleccione la variable Hoja Excel y presione F4 para ver Propiedades. Cambie la propiedad EvaluateAsExpression a True y establezca la propiedad Expression con el valor indicado en ExcelSheet Variable Value . Consulte la captura de pantalla n. ° 3 .
En la ficha Flujo de control del paquete SSIS, coloque una Execute SQL Task y configúrela como se muestra en las capturas de pantalla n.º 4 y n.º 5 . Esta tarea obtendrá los nombres de sitio únicos.
En la ficha Flujo de control del paquete SSIS, coloque un Foreach Loop container después de la tarea Ejecutar SQL y configure el contenedor Foreach Loop como se muestra en las capturas de pantalla # 6 y # 7 Este ciclo recorrerá el conjunto de resultados y leerá cada sitio en una variable. Esta variable se usa para proporcionar el nombre del archivo de Excel así como también el parámetro del procedimiento almacenado en una tarea de flujo de datos que se agregará en breve.
Dentro del contenedor Foreach Loop, coloque una Execute SQL Task y luego coloque una Data Flow Task . En este punto, la pestaña Control Flow debería verse como se muestra en la captura de pantalla # 8 . Su paquete puede mostrar errores en este punto, lo solucionaremos pronto en los siguientes pasos. Configuraremos la tarea de flujo de datos y luego regresaremos a la tarea Ejecutar SQL dentro del contenedor de bucles Foreach.
Dentro de la tarea de flujo de datos, coloque un origen OLE DB Source y configúrelo como se muestra en las capturas de pantalla # 9 y # 11 . Esto obtendrá los datos de la tabla en función de un sitio determinado. Haga clic en el botón Parámetros ... para establecer los parámetros de consulta.
Si los tipos de datos del campo de tabla están en VARCHAR, entonces necesitamos convertirlo a NVARCHAR (formato Unicode), de lo contrario este paso no es necesario. En la tarea Flujo de datos, coloque una transformación de Data Conversion después de la fuente OLE DB y configúrela como se muestra en la captura de pantalla n.º 12 .
A continuación, dentro de la Tarea de flujo de datos, coloque un Destino de Excel, haga clic en el primer botón Nuevo ... como se muestra en la captura de pantalla # 13 .
En el cuadro de diálogo Administrador de conexión de Excel, proporcione una ruta de archivo de Excel y haga clic en Aceptar. Referencia captura de pantalla # 14 . De vuelta en el destino de Excel, haga clic en el segundo botón Nuevo ... como se muestra en la captura de pantalla # 15 . En el cuadro de diálogo Crear tabla, asegúrese de que el guión sea como se muestra en la captura de pantalla n. ° 16 y haga clic en Aceptar. Cuando se muestre con una advertencia como se muestra en la captura de pantalla n. ° 17 , haga clic en Aceptar. Seleccione el valor Template en el menú desplegable de la Hoja de Excel como se muestra en la captura de pantalla # 18 . Configure las columnas como se muestra en la captura de pantalla n. ° 19 .
En el administrador de conexión del paquete SSIS, seleccione el administrador de conexión de Excel recién creado y presione F4 para ver las propiedades. Cambie el valor de la propiedad Name a Excel . Cambie DelayValidation a True para que, si el archivo Template.xls no existe, no obtenga un mensaje de error. Establezca ServerName Expression con el valor @[USer::ExcelFilePath] . Referencia captura de pantalla # 20 . NOTE: Se debería haber creado un archivo Excel en la ruta C: / temp / Template.xls. Es posible que desee guardarlo para que no se encuentre durante futuros cambios de diseño. Todavía puede volver a crearlo si el archivo se elimina.
Regrese al destino de Excel y configúrelo como se muestra en la captura de pantalla # 21 . Una vez que la tarea de flujo de datos está configurada, debe verse como se muestra en la captura de pantalla n. ° 22 .
De vuelta en la pestaña Flujo de control, configure la tarea Ejecutar SQL dentro del contenedor de bucles Foreach como se muestra en la captura de pantalla n. ° 23 . Esta tarea creará nuevas hojas de cálculo de Excel para cada nombre de sitio.
La captura de pantalla # 24 muestra los contenidos en la carpeta c: / temp / antes de la ejecución del paquete.
Las capturas de pantalla # 25 y # 26 muestran la ejecución del paquete.
La captura de pantalla # 27 muestra los contenidos en la carpeta c: / temp / después de la ejecución del paquete.
Las capturas de pantalla # 28 y # 29 muestran el contenido de las hojas de cálculo de Excel recién creadas North.xls y South.xls. Ambas hojas contienen los datos respectivos de los sitios del mismo nombre.

Espero que ayude.

Valor de variable de ExcelSheet:

CREATE TABLE `Template` (`Id` Long, `FirstName` LongText, `LastName` LongText)

Scripts SQL:

CREATE TABLE [dbo].[SourceData]( [Id] [int] IDENTITY(1,1) NOT NULL, [Site] [varchar](50) NOT NULL, [FirstName] [varchar](40) NOT NULL, [LastName] [varchar](40) NOT NULL, CONSTRAINT [PK_SourceData] PRIMARY KEY CLUSTERED ([Id] ASC) ) ON [PRIMARY] GO INSERT INTO dbo.SourceData (Site, FirstName, LastName) VALUES (''North'', ''Jim'', ''Smith''), (''North'', ''Tim'', ''Johnson''), (''North'', ''Sachin'', ''Tendulkar''), (''South'', ''Horatio'', ''Alger''), (''South'', ''Jimi'', ''Hendrix''), (''South'', ''Bugs'', ''Bunny''); GO CREATE PROCEDURE dbo.GetSiteData ( @Site VARCHAR(50) ) AS BEGIN SET NOCOUNT ON; SELECT Id , FirstName , LastName FROM dbo.SourceData WHERE Site = @Site END GO

Captura de pantalla n. ° 1:

Captura de pantalla n. ° 2:

Captura de pantalla n. ° 3:

Captura de pantalla n. ° 4:

Captura de pantalla n. ° 5:

Captura de pantalla n.º 6:

Captura de pantalla n. ° 7:

Captura de pantalla n. ° 8:

Captura de pantalla n. ° 9:

Captura de pantalla n. ° 10:

Captura de pantalla n. ° 11:

Captura de pantalla n.º 12:

Captura de pantalla n.º 13:

Captura de pantalla n.º 14:

Captura de pantalla n.º 15:

Captura de pantalla n.º 16:

Captura de pantalla n.º 17:

Captura de pantalla n.º 18:

Captura de pantalla n. ° 19:

Captura de pantalla n. ° 20:

Captura de pantalla n.º 21:

Captura de pantalla n.º 22:

Captura de pantalla n.º 23:

Captura de pantalla n.º 24:

Captura de pantalla n. ° 25:

Captura de pantalla n.º 26:

Captura de pantalla n.º 27:

Captura de pantalla n.º 28:

Captura de pantalla n.º 29:

Encontré que la solución era el problema con el destino de Excel. Reinstalé el motor de acceso de Microsoft Redistribuible (versión de 32 bits). Todo está funcionando bien. Tuve un problema de 64 bits y 32 bits donde no era compatible ya que mi sistema era de 64 bits.