sql-server - manager - ssis excel destination create new file

¿Cómo accedo a la fuente de datos de Excel desde un paquete SSIS implementado en un servidor de 64 bits? (5)

Tengo un paquete de SSIS que exporta datos a un par de archivos de Excel para transferirlos a un tercero. Para que esto se ejecute como un trabajo programado en un servidor de 64 bits, entiendo que necesito establecer el paso como un tipo CmdExec y llamar a la versión de 32 bits de DTExec. Pero parece que no puedo obtener el comando correcto para pasar la cadena de conexión para los archivos de Excel.

Hasta ahora tengo esto:

DTExec.exe /SQL /PackageName /SERVER OUR2005SQLSERVER /CONNECTION LETTER_Excel_File;/""Provider=Microsoft.Jet.OLEDB.4.0";"Data Source=""C:/Temp/BaseFiles/LETTER.xls";"Extended Properties= ""Excel 8.0;HDR=Yes"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E

Esto me da el error: la Option "Properties=Excel 8.0;HDR=Yes" is not valid.

He intentado algunas variaciones con las comillas, pero todavía no he podido hacerlo bien.

¿Alguien sabe cómo arreglar esto?

UPDATE:

Gracias por su ayuda, pero he decidido ir con los archivos CSV por ahora, ya que parece que solo funcionan en la versión de 64 bits.

Este ejemplo paso a paso es para otros que puedan tropezar con esta pregunta. Este ejemplo usa SSIS 2005 y usa SQL Server 2005 servidor de edición de 64 bits para ejecutar el trabajo.

La respuesta aquí se concentra solo en corregir el mensaje de error mencionado en la pregunta. El ejemplo demostrará los pasos para recrear el problema y también la causa del problema seguido por cómo solucionarlo.

NOTE: recomendaría usar la opción de almacenar los valores de configuración del paquete en la base de datos o usar la configuración XML indirecta con la ayuda de Variables de entorno. Además, los pasos para crear el archivo de Excel se realizarían usando una plantilla que luego se archivaría moviéndolos a una carpeta diferente. Estos pasos no se tratan en esta publicación. Como se mencionó anteriormente, el propósito de esta publicación es abordar el error.

Procedamos con el ejemplo. También escribí en mi blog sobre esta respuesta, que se puede encontrar en este enlace . Es la misma respuesta.

Cree un paquete de SSIS ( Pasos para crear un paquete de SSIS ). Este ejemplo utiliza BIDS 2005. He nombrado el paquete con el formato AAAAMMDD_hhmm al principio seguido de SO significa Desbordamiento de pila, seguido del ID de pregunta SO, y finalmente una descripción. No estoy diciendo que debas nombrar tu paquete así. Esto es para mí referir esto fácilmente más tarde. Tenga en cuenta que también tengo una fuente de datos llamada Adventure Works. Utilizaré el origen de datos de Adventure Works, que apunta a la base de datos AdventureWorks que se descargó de este enlace . El ejemplo usa la base de datos de SQL Server 2008 R2. Referencia captura de pantalla # 1 .

En la base de datos de AdventureWorks, cree un procedimiento almacenado llamado dbo.GetCurrency usando la secuencia de comandos indicada a continuación.

CREATE PROCEDURE [dbo].[GetCurrency] AS BEGIN SET NOCOUNT ON; SELECT TOP 10 CurrencyCode , Name , ModifiedDate FROM Sales.Currency ORDER BY CurrencyCode END GO

En la sección Connection Manager del paquete, haga clic con el botón derecho y seleccione New Connection From Data Source . En el cuadro de diálogo Seleccionar origen de datos , seleccione Adventure Works y haga clic en Aceptar. Ahora debería ver el origen de datos de Adventure Works en la sección Administradores de conexión .

En la sección Administradores de conexión del paquete, haga clic con el botón derecho nuevamente pero esta vez seleccione Nueva conexión .... Esto es para crear la conexión de Excel. En el Administrador de conexiones Agregar SSIS, seleccione EXCEL . En Excel Connection Manager, ingrese la ruta C: / Temp / Template.xls . Cuando lo implementemos en el servidor, cambiaremos esta ruta. He seleccionado la versión de Excel Microsoft Excel 97-2005 y opté por dejar la casilla de verificación La primera fila tiene los nombres de las columnas marcadas para que la creación del archivo de Excel se cree encabezados de columna. Haga clic en Aceptar . Cambie el nombre de la conexión de Excel a Excel , solo para mantenerlo simple. Refiere capturas de pantalla # 2 - # 7 .

En el paquete, crea la siguiente variable. Referencia captura de pantalla # 8 .

SQLGetData : esta variable es de tipo String. Esto contendrá la instrucción de ejecución del Procedimiento Almacenado. Este ejemplo usa el valor EXEC dbo.GetCurrency

La captura de pantalla n. ° 9 muestra el resultado de la instrucción de ejecución del procedimiento almacenado EXEC dbo.GetCurrency

En la pestaña Flujo de control del paquete, coloque una Data Flow task y Data Flow task nombre Exportar a Excel. Consulte la captura de pantalla n . ° 10 .

Haga doble clic en la Tarea de flujo de datos para cambiar a la pestaña Flujo de datos.

En la pestaña Flujo de datos, coloque un OLE DB Source para conectarse a los datos del Servidor SQL para buscar los datos del procedimiento almacenado y asígnele el nombre SQL. Haga doble clic en OLE DB Source para abrir el OLE DB Source Editor. En la sección Connection Manager, seleccione Adventure Works del administrador de conexión OLE DB, seleccione el comando SQL de la variable del modo de acceso a datos y seleccione la variable User :: SQLGetData del menú desplegable del nombre de la variable. En la sección Columnas, asegúrese de que los nombres de las columnas estén correlacionados correctamente. Haga clic en Aceptar para cerrar el Editor de origen OLE DB. Consulte capturas de pantalla # 11 y # 12 .

En la pestaña Flujo de datos, coloque un Excel Destination para insertar los datos en el archivo de Excel y asígnele el nombre Excel. Haga doble clic en el Destino de Excel para abrir el Editor de destino de Excel. En la sección Connection Manager, seleccione Excel desde el administrador de conexión OLE DB y seleccione Tabla o vista desde el modo de acceso a datos. En este punto, no tenemos un Excel porque al crear el administrador de conexión de Excel, simplemente especificamos la ruta pero nunca creamos el archivo. Por lo tanto, no habrá ningún valor en el menú desplegable Nombre de la hoja de Excel. Por lo tanto, haga clic en el botón Nuevo ... (el segundo Nuevo) para crear una nueva hoja de Excel. En la ventana Crear tabla, BIDS automáticamente proporciona una hoja de creación basada en la fuente de datos entrante. Puede cambiar los valores de acuerdo a sus preferencias. Simplemente haré clic en Aceptar reteniendo el valor predeterminado. El nombre de la hoja se completará en el menú desplegable Nombre de la hoja de Excel. El nombre de la hoja se toma del nombre de la tarea, aquí, en este caso, el Destino de Excel, que hemos nombrado como Excel. En la sección Asignaciones, asegúrese de que los nombres de las columnas estén correlacionados correctamente. Haga clic en Aceptar para cerrar el Editor de destino de Excel. Consulte capturas de pantalla # 13 - # 16 .

Una vez que se configura la tarea de flujo de datos, debe verse como se muestra en la captura de pantalla n . ° 17 .

Ejecute el paquete presionando F5. Las capturas de pantalla # 18 - # 21 muestran la ejecución exitosa del paquete tanto en Control Flow como en Data Flow Task. Además, el archivo se genera en la ruta C: / Temp / Template.xls proporcionada en la conexión de Excel y los datos que se muestran en la salida de ejecución del procedimiento almacenado coinciden con los datos escritos en el archivo.

El paquete desarrollado en mi máquina local en la ruta de la carpeta C: / Learn / Learn.VS2005 / Learn.SSIS . Ahora, tenemos que implementar los archivos en el servidor que aloja la versión de 64 bits del servidor SQL para programar un trabajo. Entonces, la carpeta en el servidor sería D: / SSIS / Practice . Copie el archivo del paquete ( .dtsx ) de la máquina local y péguelo en la carpeta del servidor. Además, para que el paquete se ejecute correctamente, debemos tener la hoja de cálculo de Excel presente en el servidor. De lo contrario, la validación fallará. Por lo general, creo una carpeta de Plantilla que contendrá el archivo de hoja de cálculo de Excel vacío que coincide con el resultado. Más tarde, durante el tiempo de ejecución, cambiaré la ruta de salida de Excel a una ubicación diferente utilizando la configuración del paquete. Para este ejemplo, voy a mantenerlo simple. Entonces, copiemos el archivo de Excel generado en la máquina local en la ruta C: / Temp / Template.xls a la ubicación del servidor D: / SSIS / Practice . Quiero que el trabajo SQL genere el archivo en el nombre Currencies.xls. Por lo tanto, cambie el nombre del archivo Template.xls a Currencies.xls . Refiérase a la captura de pantalla # 22 .

Para mostrar que de hecho voy a ejecutar el trabajo en el servidor en una edición de 64 bits de SQL Server, ejecuté el comando SELECT @@ version en SQL Server y la captura de pantalla # 23 muestra los resultados.

Usaremos Execute Package Utility (dtexec.exe) para generar los parámetros de línea de comando. Inicie sesión en el servidor que ejecutará el paquete SSIS en un trabajo SQL. Haga doble clic en el archivo del paquete, esto traerá la utilidad de paquete de ejecución. En la sección General, seleccione Sistema de archivos desde el origen del paquete. Haga clic en las elipsis y busque la ruta del paquete. En la sección Administradores de conexión, seleccione Excel y cambie la ruta dentro del archivo de Excel de C: / Temp / Template.xls a D: / SSIS / Practice / Currencies.xls. Los cambios realizados en la utilidad generarán una línea de comando en consecuencia en la sección Línea de comando. En la sección Línea de comando, copie la línea de comando que contiene todos los parámetros necesarios. No vamos a ejecutar el paquete desde aquí. Haga clic en Cerrar . Refiere capturas de pantalla # 24 - # 26 .

A continuación, debemos configurar un trabajo para ejecutar el paquete SSIS. No podemos elegir el tipo de paquete de SQL Server Integration Services porque se ejecutará en 64 bits y no encontrará el proveedor de conexión de Excel. Entonces, tenemos que ejecutarlo como tipo de trabajo Operating System (CmdExec) . Vaya a SQL Server Management Studio y conéctese al Motor de base de datos. Expanda el Agente SQL Server y haga clic derecho en el nodo Trabajos. Seleccione Nuevo trabajo .... En la sección General de la ventana Propiedades del trabajo, proporcione el nombre del trabajo como 01_SSIS_Export_To_Excel, el propietario será el usuario que cree el trabajo. Tengo una Categoría llamada SSIS, así que la seleccionaré pero la categoría predeterminada es [Sin categoría (Local)] y proporciono una breve descripción. En la sección Pasos, haga clic en el botón Nuevo ... Esto traerá propiedades de Paso de trabajo. En la sección General de las propiedades del Paso de trabajo, proporcione Nombre de paso como Exportar a Excel, Seleccione tipo Operating system (CmdExec) , deje la cuenta Ejecutar como cuenta de servicio del Agente SQL Server y proporcione el siguiente Comando. Haga clic en Aceptar. En la ventana Nuevo trabajo, haga clic en Aceptar. Consulte capturas de pantalla # 27 - # 31 .

C:/Program Files (x86)/Microsoft SQL Server/90/DTS/Binn/DTExec.exe /FILE "D:/SSIS/Practice/20110723_1015_SO_21448_Excel_64_bit_Error.dtsx" /CONNECTION Excel;"/"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:/SSIS/Practice/Currencies.xls;Extended Properties=""EXCEL 8.0;HDR=YES"";/"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI

El nuevo trabajo debe aparecer en el Agente SQL Server -> nodo Trabajos. Haga clic derecho en el trabajo recién creado 01_SSIS_Export_To_Excel y seleccione Iniciar trabajo en el paso ... , esto comenzará la ejecución del trabajo. El trabajo fallará como se espera porque ese es el contexto de este problema. Haga clic en Cerrar para cerrar el cuadro de diálogo Iniciar trabajos. Consulte capturas de pantalla # 32 y # 33 .

Echemos un vistazo a lo que sucedió. Vaya al nodo Agente y trabajos de SQL Server. Haga clic derecho en el trabajo 01_SSIS_Export_To_Excel y seleccione Ver historial. Esto traerá la ventana del Visor de archivos de registro. Puedes notar que el trabajo falló. Expanda el nodo cerca de la cruz roja y haga clic en la línea cuyo valor de ID de paso es 1. En la sección inferior, puede ver el mensaje de error La Option “8.0;HDR=YES'';” is not valid. Haga clic en Cerrar para cerrar la ventana del Visor de archivos de registro. Remita las capturas de pantalla # 34 y # 35 .

Ahora, haga clic derecho en el trabajo y seleccione Propiedades para abrir las Propiedades del trabajo. También puede hacer doble clic en el trabajo para abrir la ventana Propiedades del trabajo. Haga clic en los Pasos en la sección de la izquierda. y haz clic en Editar. Reemplace el comando con el siguiente comando y haga clic en Aceptar. Haga clic en Aceptar en Propiedades del trabajo para cerrar la ventana. Haga clic derecho en el trabajo 01_SSIS_Export_To_Excel y seleccione Comenzar tarea en el paso ..., esto comenzará la ejecución del trabajo. El trabajo no se ejecutará correctamente. Haga clic en Cerrar para cerrar el cuadro de diálogo Iniciar trabajos. Echemos un vistazo a la historia. Haga clic derecho en el trabajo 01_SSIS_Export_To_Excel y seleccione Ver historial. Esto traerá la ventana del Visor de archivos de registro. Puede observar que el trabajo tuvo éxito durante la segunda ejecución. Expanda el nodo que se encuentra cerca de la marca de verificación verde y haga clic en la línea cuyo valor de ID de paso es 1. En la sección inferior, puede ver el mensaje Opción El paso fue exitoso. Haga clic en Cerrar para cerrar la ventana del Visor de archivos de registro. El archivo D: / SSIS / Practice / Currencies.xls se completará con éxito con los datos. Si ejecuta el trabajo exitosamente varias veces, los datos se anexarán al archivo y encontrará más datos. Como mencioné antes, esta no es la forma correcta de generar los archivos. Este ejemplo fue creado para demostrar una solución para este problema. Consulte capturas de pantalla # 36 - # 38 .

La captura de pantalla n. ° 39 muestra las diferencias entre los argumentos de línea de comando que funcionan y los que no funcionan. El de la derecha es la línea de comando que funciona y el izquierdo es incorrecto. Requería otras comillas dobles con secuencia de escape de barra invertida para corregir el error. Podría haber otras formas de solucionarlo bien, pero esta opción parece funcionar.

Por lo tanto, el ejemplo demostró una forma de solucionar el problema del argumento de la línea de comando al acceder al origen de datos de Excel desde un paquete SSIS que se implementa en un servidor de 64 bits.

Espero que ayude a alguien.

Capturas de pantalla:

# 1: Solution_Explorer

# 2: New_Connection_Data_Source

# 3: Select_Data_Source

# 4: New_Connection

# 5: Add_SSIS_Connection_Manager

# 6: Excel_Connection_Manager

# 7: Connection_Managers

# 8: Variables

# 9: Stored_Procedure_Output

# 10: Control_Flow

# 11: OLE_DB_Source_Connections_Manager

# 12: OLE_DB_Source_Columns

N.º 13: Excel_Destination_Editor_New

# 14: Excel_Destination_Create_Table

# 15: Excel_Destination_Edito

# 16: Excel_Destination_Mappings

# 17: Flujo de Datos

# 18: Successful_Package_Execution_Control

# 19: Successful_Package_Execution_Data_Flow

# 20: C_Temp_File_Created

# 21: Data_Populated

# 22: File_On_Server

# 23: SQL_Server_Version

# 24: Execute_Package_Utility_General

# 25: Execute_Package_Utility_Connection_Managers

# 26: Execute_Package_Utility_Command_Line

# 27: Job_New_Job

N.º 28: New_Job_General

# 29: New_Job_Step

# 30: New_Job_Step_General

# 31: New_Job_Steps_Added

# 32: Job_Start_Job_at_Step

# 33: SQL_Job_Execution_Failure

# 34: View_History

# 35: SQL_Job_Error_Message

# 36: SQL_Job_Execution_Success

# 37: SQL_Job_Success_Message

# 38: Excel_File_Generated

# 39: Command_Comparison

A menos que sea un requisito empresarial, le sugiero que mueva la cadena de conexión de la línea de comando al paquete y use una configuración de paquete para definir la ruta al archivo de Excel (para no codificarlo). Esto hará que sea más fácil de mantener.

Defina una variable @ExcelPath .
Use la propiedad Expression de la conexión para construir una cadena de conexión, por ejemplo: "Data Source=" + @[User::FilePath] + ";Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;"
Asigne un valor a @ExcelPath en la configuración del paquete.

Eche un vistazo más de cerca a la cadena de conexión anterior. Se toma de un paquete de trabajo. No estoy seguro de esto, pero tal vez no necesites ninguna cotización (las anteriores solo están ahí porque el editor de expresiones las requiere).

También he tenido algunos problemas con SSIS en SQL Server 2005 de 64 bits. Esa publicación de mi blog no responde a su pregunta, pero está algo relacionada, así que estoy publicando el enlace.

Hice lo que hizo el Dr. Zim pero copié el archivo DTExec C:/Program Files (x86)/Microsoft SQL Server/90/DTS/Binn/DTExec.exe a C:/Program Files/Microsoft SQL Server/90/DTS/Binn/ Carpeta C:/Program Files/Microsoft SQL Server/90/DTS/Binn/ pero llamó el 32 bit uno a DTExec32.exe

luego pude ejecutar mi script SSIS a través de un proceso almacenado:

set @params = ''/set /package.variables[ImportFilename].Value;"/"'' + @FileName + ''/"" '' set @cmd = ''dtexec32 /SQ "'' + @packagename + '' '' + @params + ''"'' --DECLARE @returncode int exec master..xp_cmdshell @cmd --exec @returncode = master..xp_cmdshell @cmd --select @returncode

No existe un proveedor Jet OLEDB de 64 bits, por lo que no puede acceder a los archivos de Excel desde el SSIS de 64 bits.

Sin embargo, puede usar SSIS de 32 bits incluso en el servidor de 64 bits. Ya está instalado cuando instaló la versión de 64 bits, y todo lo que necesita hacer es ejecutar el DTEXEC.EXE de 32 bits: el que está instalado Program Files (x86)/Microsoft Sql Server/90/Dts/Binn (reemplace 90 con 100 si usa SSIS 2008).

Puede usar una conexión de Excel en un entorno de 64 bits. Vaya a las propiedades de configuración del paquete.

Depuración -> Opciones de depuración -> Run64BtRuntime -> cambie a False Además, si usa SQL Agent vaya a las propiedades del paso de trabajo y luego verifique el tiempo de ejecución de 32 bits.

Nota: esto solo se aplica a la depuración dentro de Visual Studio ...