Hace unos días tuve la oportunidad de presentar el tema que titula este post, dirigido al Capítulo Virtual de SQL Server en Español el cual comparto a continuación.
Es bueno resaltar que ya hace un par de meses había presentado el mismo tema para PASS LATAM 24 Horas, pero en aquella ocasión no pudimos dar los demos por temas técnicos, pero ofrecí grabarlos en el futuro cercano. Lo prometido es deuda.
Alan
Como lo prometido es deuda, a continuación incluyo el video de la segunda presentación de esta serie que presenté el último jueves de Noviembre, como parte de las reuniones mensuales del Capítulo de Usuarios de SQL Server en Puerto Rico (PRPASS).
En esta oportunidad hice un rápido recorrido por la primera parte de la presentación, para que los que no pudieron ver la primera parte no estén tan desubicados, pero la recomendación para todo aquel que esté leyendo este post por internet, es que vea el video de la primera parte.
Alguno de los temas discutidos en esta segunda parte son los siguientes:
Los dejo con el video:
Alan
Desde hace algunas semanas tenía pendiente publicar la grabación de mi última presentación en nuestro Capítulo local de SQL Server – Puerto Rico PASS. El tema presentado fue el que lleva por título este post. Por temas de tiempo (algo que sin duda, debo mejorar), tuve que partir la sesión en dos partes. Así que la que presento a continuación es la primera parte y espero presentar la segunda durante este mes de Noviembre.
Esta presentación asume que la audiencia ya tiene los conceptos básicos de PowerPivot o de Analysis Services en modo Tabular y cómo crear sus primeros modelos, aunque se hace una breve introducción y se aclaran los conceptos principales durante los primeros 15 minutos. Esta presentación es una variación a las que ya he hecho en oportunidades anteriores pero incluyendo nuevos detalles o ejercicios producto de la evolución.
Listo a continuación los temas tratados durante la sesión:
A continuación los “slides” de la presentación:
Ahora sí, la grabación de la sesión (no olviden ajustar la calidad del video según su preferencia):
Ahora si me despido, no sin antes dejar la promesa para la parte 2 de por lo menos los siguientes tópicos:
Nos vemos.
Alan
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:
“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.”
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:
My recommendation is:
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
When: 2012/07/26 @ 6:15 pm - 9:00 pm
Where: Microsoft Puerto Rico, Guaynabo, PR 0068
Cost: Free
More information: http://www.prpass.org/calendar/
SESSION TOPICS:
Look who’s Blocking
Level: 200 (Intermediate)
Audience: IT Professionals / DBAs
Have you ever faced the situation of trying to retrieve some data and the query keeps waiting on. Why this simple query is taking so long? There is a good chance that you are encountering locking/blocking problems. In this session, we will demonstrate how to recognize, diagnose and troubleshoot common locking and blocking scenarios. We will discuss transactions basics, lock types, escalation, and the infamous deadlock, among others.
Speaker: Jose L. Rivera
Abarca Health
Jose Luis has over 9 years of experience developing Data Analytics, Data Warehousing and Business Intelligence solutions using SQL Server technologies, among others. In addition, he shares his knowledge and perspectives on database technologies as PRPASS Community Leader and college professor. He holds the MCTIP in Database Administration, Database Development and Business Intelligence for SQL Server 2008.
Some Ideas to Keep in Mind while Designing your Dashboards
Level: 100 (Begginer)
Audience: BI Developers / Information Workers
As part of the new PRPASS’ Executive Series and Soft Skills Development, in this session we will discuss some important ideas and recommendations for effective visualizations and dashboard design according to Stephen Few, a recognized author and thought leader in the field of Business Intelligence. We will show suggestions about what to do and NOT to do while designing a dashboard.
Important note: This session will be totally focused on design and best practices, not in the development or implementation.
Speaker: Edwin Torres
Nagnoi, Inc.
Edwin Torres is a Business Intelligence Consultant at Nagnoi. Mr. Torres has been involved in Business Intelligence projects for more than 5 years, covering different industries or verticals such as tourism and healthcare among others. He is also founder of Mi Windows Blog (www.miwindowsblog.com), one of the most important blogs in Latinamerica and Spain wich covers topics around Windows Phone and Windows 8.
