Strange Error Loading Excel Files (xlsx) using SQL Server Integration Services

After some months without blogging (too many things happening lately), I’m coming back because I tried to get some answer to my problem in the Internet without any success. Thus, here I am sharing this in order to save you some waste time debugging.

Some days ago, I spent almost 6 hours trying to find “a simple” error (and the solution) related to a SSIS 2008R2 package that loads data from an Excel file and writes it to a SQL Server table without any mayor transformation. The strange thing here was that the package was working correctly in our development environment and “erratically” in our test environment.

The situation was:

• The SSIS package witch loads the Excel file shown below (Classicare Rx Network 2012 Q2.xlsx), runs correctly in BIDS and when executing it through a SQL Server Agent Job using the current user identity (Proxy Account). The package is using the 32bits version of the ACE Provider.

• The SQL Server Agent Job fails when running the SQL Agent Job using the identity of our ETLAccount (a standard domain account created for the SSIS packages execution). The error message indicates that the Excel file cannot be reached as you can see below:

    

 
“Message Executed as user: NAGNOILAP030-W8\ETLSystem_SQLAgentProxy. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  12:10:44 AM  Error: 2012-09-06 00:10:46.53     Code: 0xC0202009     Source: Data Flow Task Excel Source [1]     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  End Error  Error: 2012-09-06 00:10:46.53     Code: 0xC02020E8     Source: Data Flow Task Excel Source [1]     Description: Opening a rowset for "RxNetwork$" failed. Check that the object exists in the database.  End Error  Error: 2012-09-06 00:10:48.16     Code: 0xC004706B     Source: Data Flow Task SSIS.Pipeline     Description: "component "Excel Source" (1)" failed validation and returned validation status "VS_ISBROKEN".  End Error  Error: 2012-09-06 00:10:48.16     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2012-09-06 00:10:48.16     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  12:10:44 AM  Finished: 12:10:48 AM  Elapsed:  3.261 seconds.  The package execution failed.  The step failed.”

• However, the strange thing here was that we have many other packages working correctly (to load some manual dimensions without an existing formal data source), including when the SQL Job is configured to use the ETLAccount credentials. For example, the AdjudicationStatus_Medical.xlsx file shown below is one of those files:

• The second, strange  thing was that the failing package executed successfully using a local administrator account:

 

I reviewed several post about using the Excel OLEDB 12 provider (including those about how to use it in 32bits), without getting a solution to my problem. I also found this very interesting post from Dougbert’s Blog entitled “Excel driver now supported on server for use by SSIS”. Beyond the Microsoft official support for Excel running on SSIS servers, the most important part for me was: “…ACE uses the impersonated user’s Windows temp folder to read-write its data. Therefore if your application is using impersonation with an account that does not have a profile on the server (not an uncommon situation), then ACE will not be able to create its temp files.”

So, according to this post, the ACE driver (used by Excel and Access) requires that a user profile exists in the server for the user (identity) running the Excel packages, but in this case we don’t have a user profile for the ETLAccount. At this point I was more confused: how my other packages work correctly and the one for the Classicare..xlsx don’t?

To understand more the mystery, I did try and error tests to check what was causing the package to fail when it was running using the SQL Server Agent Job and the ETLAccount credentials. I created several Excel files with different number of columns and rows.

Several hours later, I noticed that the problem was not related to the file format nor the number of rows (directly). As you can see in the table below (file with only 3 columns), I was able to reproduced the error when my files were more than 1.7Mb in size approximately:

Rows	Size (MB)	Job Execution Status
65,000	3.60	Failed
35,000 (and more)	1.79	Failed
33,000	1.69	Failed
32,801	1.68	Failed
32,601	1.67	Failed
32,511	1.66	Success
32,501	1.66	Success
32,000	1.64	Success

To validate my hypothesis, I deleted some random rows to my original file (Classicare…xlsx) to cut if down to 1.66Mbs and it worked correctly! Having 1.67Mb or more, the Job was failing as expected.

After this findings, I reproduced the server environment in my own laptop just to test if creating the profile for the user account running the SQL Jobs works, but unfortunately it didn’t. Maybe an additional requirement is needed for this to work, but I got only this work, granting local administrator privileges to the user account.

Some days later, I found this interesting post entitled Excel Imports to SQL Server using Linked Servers, where the author states that any 32bit OLE DB Provider require access permissions to the SQL Server temp folder for the user account who run the query. Thus, I granted permissions to my temp folder (C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp) to my ETL account. Again, no good results.

After all my (unsuccessful) tries, my conclusions at this point are:

• If the account (credential) running your packages has not local admin permissions, you are limited to work with relative small files in size (1.66Mb maximum in my case). The maximum file size could be different in your system.
• Running the same tests in my laptop, I needed to reduce the file size to 1.28Mb to get it work. This tells me that the exact maximum file size could vary depending on your hardware configuration.
• After exceeding the size threshold in an Excel file, the ACE Provider needs some permission to write/read some temporary data (it looks it is more than a user profile). If this criteria is not met, you will get strange error messages about problems connecting to the file.
• My package was failing because it was more than 1.66Mb (12Mb) and the SSIS package execution account was not a local administrator of the machine.
• Even Microsoft Access is not supported in an enterprise environment (PowerPivot for Sharepoint cannot automate data refresh jobs from Access). I think this should be the same reason for what Excel 2010 is so picky for data loading automation.

My recommendation is:

• Avoid loading Excel files in a production / automated environment as possible, try comma separated files (CSV) or any other text file format.

Back in my project, we finally “solved” the problem, saving the file as CVS and changing the data source provider of my package from Excel to Flat File. Everything worked correctly this time even running in 64bits.

If you want to share any other information from your experience working with Excel files, feel free to comment.

Alan

Excel 2010 Technical Preview - Parte 7: Mejoras al Conditional Formatting

Después de unos días muy intensivos por los proyectos que estoy trabajando, ya estoy de vuelta para seguir con la parte 7 de nuestra serie acerca de las novedades en Excel 2010 para Business Intelligence. Hoy nos toca presentar un resumen de las mejoras a la capacidad de aplicar formato condicional (Conditional Formatting) a nuestros datos ya sea dentro o fuera de un Pivot Table.

Si no han tenido la oportunidad de usar esta capacidad de Excel 2007, les puedo decir que el Conditional Formatting es una gran ayuda para visualizar nuestros datos de una manera gráfica, que nos ayuda a entender de más rápida y directa lo que tenemos en nuestros números. A continuación algunas imágenes de lo que se puede hacer desde ya con la versión 2007:

Ya que todos tenemos ya una idea básica de cómo podemos usar estos Conditional Formatting, voy ahora a listar y comentar las mejoras o novedades que hasta ahora aparecen en el Technical Preview liberado en Julio de la versión de Excel 2010. Cabe resaltar que no voy a ahondar mucho en cada punto ya que revisando la web encontre estos excelentes post que equipo de producto de Excel acaba de publicar (en inglés): More Conditional Formating Features in Excel 2010 y Data Bar Improvements en Excel 2010.

Y las novedades son:

1. Soporte para referenciar celdas en hojas (sheets) distintas a la actual (Cross-Sheet References): Hasta ahora, en Excel 2007, nos debemos de limitar a que cuando queremos crear nuestras reglas para la asignación de íconos dentro de un formato condicional basándose en los datos de otras celdas, esos valores deben de existir en la misma hoja de Excel. Pues ahora esta limitación ha sido removida y podemos hacer referencias a otras hojas (worksheets) dentro de nuestro archivo (workbook):

 

2. Mejoras en el manejo de errores: Este punto se refiere a que en Excel 2007, algunos errores de cálculo en nuestras celdas, pueden hacer que nuestro Conditional Formatting deje de funcionar (por ejemplo un #N/A o un #DIV/0). En esta nueva versión,, un error de este tipo sera totalmente ignorado por el Conditional Formatting sin afectar a las demás celdas con cálculos correctos:

Excel 2007 vs Excel 2010:

 

3. Mejoras en el desempeño: Esta es una parte muy importante, principalmente si tenemos aplicado el Conditional Formatting a una o varias hojas con muchos datos. La primera mejora, se basa en que para el caso del formato aplicado sobre celdas producto de fórmulas o de otras celdas, el formato condicional sólo se re-evaluará, si alguna de las dependencias de la fórmula cambia. La segunda mejora con respecto en el desempeño consiste en que el motor guarda un cache del formato condicional aplicado, para que no tenga que ser re-calculado o re-aplicado en el caso de que el usuario simplemente esté navegando por la hoja (scrolling) sin cambiar ningún valor.

4. Mejoras en los Data Bar – Tamaños proporcionales: En Excel 2007, cuando aplicamos un Conditional Formatting de tipo Data Bar, si bien obtenemos el resultado general esperado dependiendo de los números que estemos analizando (pintando un relleno con mayor intensidad para el valor más alto y con un relleno menos intenso para el valor más bajo. O al revés si los números que analizamos tienen esta naturaleza, como los costos por ejemplo), la forma de relleno de las celdas no se pinta proporcionalmente al número que se está representando en comparación con los demás Valores, lo cual puede llevar a un entendimiento incorrecto de nuestros datos. En Excel 2010 ha sido superado, de manera que ahora los rellenos de las celdas participantes, son pintados proporcionalmente a los números que existan en nuestro dataset.

Data Bars en Excel 2007 vs Excel 2010:

Más ejemplos en Excel 2010:

 

5. Mejoras en los Data Bar – Opciones de formato (rellenos y bordes): Al usar los Data Bars, muchas veces perdemos visibilidad de nuestro datos en las celdas, aunque el efecto de “gradient” o degradado de los colores tratan de minimizar esa pérdida de visibilidad. Por otro lado muchas veces cuando tenemos Data Bars en valores muy similares, no es muy fácil por el mismo efecto de degradado, no es muy fácil darnos cuenta de cuál de las celdas tiene un mayor o menor valor. Para mejorar la visibilidad ahora se agregan nuevas opciones para presentar los Data Bar.

La primera consiste en hacer que el relleno de los mismos no sea de tipo degradado sino de tipo completo.

Ventana de diálogo “More Rules…” en Excel 2007 vs Excel 2010

   

Data Bars con degradado (gradient) vs relleno sólido (solid fill)

 

Como pueden ver en las dos imágenes anteriores (degradado vs relleno sólido), en el primer caso, si nos fijamos sólamente en el relleno del Data Bar y no en los números, es difícil notar si el año 1996 o el 1997 es el que más nacimientos tuvo. En cambio si el relleno lo configuramos de tipo sólido la diferencia es mucho más clara.

La segunda de las mejoras en los Data Bars, nos permite mantener el relleno de tipo degradado o sólido pero incluyendo un borde para resaltar sus límites::

6. Mejoras en los Data Bar – Valores negativos: Esta mejora es algo que estaba siendo pedido a gritos en vario de los forums de Excel, y se refiere a mostrar los Data Bars de manera “correcta” para los valores negativos. Una opción es mostrando el Data Bar del valor negativo en dirección contraria a los valores negativos, y la otra es mostrar los negativos en la misma dirección de los positivos pero con otro color.

Negativos en Excel 2007 vs Excel 2010:

Bueno, con estas mejoras en los Data Bars, dejamos momentáneamente las serie de Excel 2010 y sus mejoras para este mundito de Business Intelligence. Nos queda pendiente presentar cómo Excel 2010 manejará las Medidas Calculadas (Calculated Measures), ya que hasta el momento de escribir este post, aún no está habilitada la opción que posibilitará esta posibilidad.

Hasta la próxima.

Alan

Excel 2010 Technical Preview – Parte 6: Sparklines

Esta es la sexta entrega de la serie de las nuevas características para Business Intelligence en Excel 2010. Lo que nos toca hoy es escribir acerca de los Sparklines, una excelente nueva característica que incluye Excel 2010 para darnos mayores y poderosas capacidades para reflejar información gráfica e histórica que sirva como una referencia rápida a una información principal que se esté revisando.

El concepto ode los Sparklines no es inventado por Microsoft, sino fue propuesto por Edward Tufte para “pequeños gráficos de alta resolución, incrustados en un contexto de palabras, números o imágines.” Además Tufte define a los sparklines como “intensos de datos, diseño simple, gráficos del tamaño del texto”. Más detalles en Wikipedia (en inglés). Los sparklines también existen y han existido como Add-Ins de terceros como el de XLCubed pero en este caso, ya podremos disfrutarlos desde el mismo Excel.

La limitación actual en el Technology Preview de Julio, es que no son parte de los Pivot Tables y por tanto no se sincronizan automáticamente con los datos que estemos mostrando en dicho Pivot Table. No he encontrado alguna fuente que indique para para la versión final de Excel 2010 los Pivot Tables soporten esta integración de manera automática (crucen los dedos). Bueno, pero esto no nos debe detener ya que hay formas creativas de hacer que nuestros sparklines sean de alguna manera “dinámicos”.

Lo primero es mostrarles cómo crear un sparklines, y esto es bastante sencillo, sólo basta tener una serie de datos y mapearla con el sparline. Ojo, que no es un pre-requisito tener los datos en un Pivot Table para poder mapearlo, se puede hacer directamente desde los datos en las celdas de Excel.

 
El ribbon “Insert” de Excel 2010 nos muestra 3 formas distintas de sparklines:

 

A continuación agregamos los sparklines a nuestro pivot table de resultados del estado de Pérdidas y Ganancias del 2003:

 
Adicionalmente tenemos varias opciones de personalizar o mejorar la presentación de nuestros sparlines:

Después de aplicarle alguna de las propiedades a nuestros sparklines, tenemos un efecto visual interesante, mostrando por cada fila de datos, la serie completa de los 12 meses de evolución, en color rojo el punto más bajo alcanzado y en color verde el punto más alto de la serie. Para el caso de contrastar valores positivos y negativos podemos cambiar el tipo de gráfica:

Como dijimos al inicio, la idea de uso de los sparklines, es que se muestren como información complementaria pero poderosa de la representación de un valor. De acuerdo a eso sería ideal poder ocultar los 12 meses de valores y dejar sólamente por ejemplo el valor total de todo el año para la cuenta. Actualmente si ocultamos las columnas con las series (meses), los sparklines desaparecen tambíen (tal vez se esto se trata de un bug), así que se requiere hacer algunos trucos para lograr este efecto.

En mi caso mi idea va por copiar el Pivot Table en una segunda hoja de Excel y referenciarla en los sparklines de la primera. Así en la primera logramos el efecto deseado:

 

Ahora el tema es que si cambiamos de año usando el filtro de nuestro Pivot Table (por ejemplo al año 2002 o 2004, entonces perderemos la sincronización corracta de los datos con la segunda hoja de Excel que está alimentando nuestro datos ya que estos están apuntando al año 2003 y eso no es nada bueno. Por suerte ya que estamos usando la versión 2010 de Excel y si tuvieron la oportunidad de leer la parte 2 de esta serie, sabrán que los Slicers vienen al rescate ya que estos nos permiten conectar a más de un Pivot Table con el mismo filtro. En este caso reemplazamos nuestro parámetro individual del año de cada Pivot Table por un Slicer conectado a ambos Pivot Tables:

Ahora logramos el efecto deseado, podemos cambiar de año, y automáticamente los 2 Pivot Tables se actualizan y por tanto los sparklines también. De acuerdo a esto, ya saben que pueden agregar más slicers a su Pivot y mantener la sincronización de sus sparklines automáticamente.

Esto es todo por ahora, espero que hayan disfrutado este post.

Alan.

Excel 2010 Technical Preview - Parte 5: Filtros a los parámetros

Bienvenidos a la quinta entrega de la serie acerca de las nuevas características de Excel 2010 Technical Preview para Business Intelligence. La facilidad de filtrar nuestros parámetros en un Pivot Table o mejor dicho, filtrar los miembros de una dimensión referenciada por el mismo ha sido una de las características “simples” y a la vez “poderosas” deseada por los usuarios que ahora la tendremos disponibles en la nueva versión de Excel.

Lo nuevo es que ahora podemos filtrar los miembros de la dimensión directamente escribiendo en la caja de texto de la dimensión en nuestro Pivot Table:

Cabe resaltar que el filtro es similar a un %LIKE% de SQL, es decir busca la palabra en todo el nombre del miembro y NO es sensitivo a las mayúsculas o minúsculas (key-sensitve).

 

 

 

Si queremos agregar otro miembro al filtro sólo basta con buscarlo nuevamente en la lista y seleccionarlo ya que los miembros seleccionados inicialmente no se pierden:

 

 

 

Finalmente, si tenemos más de un atributo en el eje (columna o fila), o una jerarquía, entonces podemos seleccionar el atributo sobre el cual queremos hacer el filtro:

Aquí termina este post. Hasta la próxima.

Alan