Excel and SSIS – the problems and solutions

There are various problems with importing data from Excel files.

Today I came across an article from 2012 by Koen VerBeeck which has been re-blogged on the SQLServerCentral.com website which very succinctly summarises the problems and solutions:

http://www.sqlservercentral.com/blogs/koen-verbeeck/2015/07/10/reblog-whats-the-deal-with-excel-and-ssis/

Please note my comment at the bottom which I will repeat here:

One thing I would add is that if you are in a situation where changing registry settings is difficult or you don’t want to have to bother with changing settings every time you move machines/re-image machines i.e. you have to stick to the default of 8 rows being sampled. Then just add 8 dummy rows to the beginning of your Excel tables with the relevant type of data inserted – bunch of ‘A’s for strings, ‘9’s for numeric etc. – and then delete all the dummy records out with conditional split as mentioned. With this setup you can be certain the SSIS routine will work on any machine with default JET settings.

Powerpoint slides not displaying correctly in older versions

When we started using Powerpoint 2010 some of our users started reporting problems when displaying them at presentations using client equipment.

This particularly affected slides with charts – text on axis would be scrunched up, some series lines or bars would disappear etc.

We did some testing and discovered that using older versions of Powerpoint or powerpoint viewers or using compatability pack could not guarantee that the slides would always display correctly.

We had to issue a policy of:

Making sure clients had PowerPoint 2010 or had the Microsoft PowerPoint 2010 Viewer installed. (The 2010 Compatibility pack is not good enough).

Or

Take our equipment for use at the presentation.

However, we also worked on a solution and found that if the charts were converted to images then the problems did not re-occur.

What you have to do is:

  • Right click on the chart object within the slide and choose cut
  • Then choose Paste Special and choose Picture as the paste option – you may then need to move the image around a bit to the correct position.
  • Save the presentation with a new name

Eventually we created a Powerpoint macro and attached it to a Powerpoint button. The macro would cycle through the entire presentation and convert any chart objects to pictures and then save it as a image only presentation – get in touch if you require this?

 

Powerpoint crashing when breaking links

We have users who have powerpoint presentations linked to excel files – as worksheet links to a range of cells that includes an Excel chart object. This creates their standard slides for use in presentations, they then copy these slides to bespoke presentations for particular clients. So as we can supply the presentation to the client the links are broken so that the slide is just a standalone image.

This all worked ok but after we upgraded to Office 2010 powerpoint crashed when the links were broken.

This was our submission to Microsoft support:

You have a PowerPoint presentation containing charts linked to Excel files.

When you copy slides containing this kind of chart into a new PowerPoint presentation and then break the links, this new PowerPoint presentation stops responding.

Steps to reproduce:

  1.  copy a chart and the surrounding data from an Excel file
  2. paste it into a PowerPoint presentation with Paste Special -> Paste As Microsoft Excel 2003 Worksheet Object
  3. go to Slide Sorter view
  4. copy the slide containing the chart from this PowerPoint presentation to a new presentation
  5. go to File – Edit Links and break the link
  6. at this point the second PowerPoint presentation will stop responding

Microsoft admitted this was a bug and 5 month’s later {:-| they released a Hotfix!

KB article and fix can be found here: 

http://support.microsoft.com/kb/2597145

 

Powerpoint crashing – display issue, hardware acceleration

After upgrading to Office 2010 we had a number of issues with Powerpoint 2010 one of which was to do with random crashing which appeared to be something to do with the graphics system (Powerpoint Graphics engine was completely rewritten for this version).

We tracked it down to hardware acceleration which has been turned on in Office 2010 to improve graphics performance.

Firstly make sure your graphics cards drivers are up to date – you obviously want to use hardware acceleration if you can!

You can disable hardware acceleration by using group policy or within Powerpoint options.

Group policy:

office 2010 ha.png

User ConfigurationPoliciesAdministrative TemplatesClassic Administrative TemplatesMicrosoft Office 2010MiscellaneousDo not use hardware graphics acceleration

Powerpoint options:

Goto File menu, Options, Advanced, Under Display section tick Disable Hardware Graphics Acceleration option

We needed activate this setting via Group Policy as this problem was particularly acute on our terminal server so it needed to be set for all users.

 

 

 

Excel 2010 protected mode trashing my linked workbooks

After installing Excel 2010 on our test network prior to a rollout of Office 2010 across the whole organisation we noticed a problem with linked workbooks when they are opened in Protected Mode.

Protected mode blurb from Microsoft:

By default, Protected View is enabled in Excel 2010, PowerPoint 2010, and Word 2010. However, files open in Protected View only under certain conditions (see below). In some cases, files bypass Protected View and are opened for editing. For example, files that are opened from trusted locations and files that are trusted documents bypass several security checks and are not opened in Protected View.

In our case it became obvious that workbooks which had previously been downloaded from the internet or had been saved from an email attachment were opening in protected view. This seemed sensible to us and did not cause any problems.

However, we rely on a lot of workbooks sent by suppliers which are saved to our network and then have our own workbooks linking to the data within them. When a supplier workbook opened in protected mode and then we opened our own linking workbook all the links to the supplier workbook were trashed.

We setup a test workbook just to work out if there was something corrupt within our own workbooks but, no, we got the same problem. We put together a set of test workbooks and a summary and contacted Microsoft:

Summary:

Opening a workbook may result in it being opened in Protected Mode (Read only due to originally being an email attachment or internet download). If you then open a workbook that was setup to link to it all the links are trashed by inclusion of weird :#REF entry:

e.g.

=’U:JAMES[sub_wb2.xlsx]Sheet1′!$B3

becomes:

='[sub_wb2.xlsx]Sheet1:#REF’!$B3

Opening main workbook first and then double clicking on link works ok (although the initial double click does not go to the linked cell for some reason – subsequent links do). In this case for some reason the workbook is not picked up as needing to be in protected mode!

Response from Microsoft confirmed there was a bug:

I totally understand your concerns here and how it can cause confusion for users. After researching further and testing the issue I can see that all issues including the one you have just highlighted below were explored by the development team during the testing of Excel 2010. However, the development team could not make any changes without breaking other components of Excel and thus a decision was made to postpone a fix and attempt to rectify for future releases. Their statement was as follows:

“Our initial assessment of this request has identified that a correct fix would require redesign work outside the scope of a hotfix cycle. Please know we carefully review all hotfix requests because each code change that we implement must maintain or improve the quality and stability of the product. We strive for this to ensure the continuing integrity of the code base and to maintain a supportable product. While we recognize the impact this issue is having on this customer, we cannot compromise the stability of the product’s code base using the Hotfix process”

I can certainly highlight your case and pass on your comments to the dev team. However, as previous fixes were rejected it will be highly unlikely that it will be possible to resolve this for a future Service Pack release and more than likely will be looked at for the next release of Excel. When researching I could only see three reported issues with Protected View and Link behaviour and it is unfortunate that you have come across all three. As it stands the official guidance is to use the Trusted Locations settings. This can be controlled centrally using Group Policy settings and minimise any I.T overheads.

So Microsoft were not going to do anything about it anytime soon!

Our final response was:

I understand the need to maintain product integrity and that this might mean bug fixes which impact large parts of the product cannot be implemented with a mid-product release via Hot-Fix or Service Pack. However, the seriousness of this bug would, in my view, warrant the extra resources required to produce a fix. It also does not give me great confidence with the entire protected view code if they are of the opinion that any changes will have a major impact on other areas. 

Yes, we could use trusted locations but this does increase the burden on IT and I would rather just switch off Protected View via group policy rather than use something buggy and untrustworthy. Also, it seems to me that having trusted locations negates the whole point of protected view as anyone can then save documents from the internet into the trusted location and these files would then be loaded unprotected – or am I missing something? Also, it does not address the 3rd issue of double clicking a link and the sub document being opened without being put into protected mode when it should be – if trusted locations are being used but a workbook is linked to an untrusted location the sub workbook will open in unprotected view whilst the user will assume it is from a trusted source because that has been set up.

I have not received any further notification that this problem has been fixed and testing still produces the same error.

Solution: don’t use protected view. It can be turned off by going to Excel Options, Trust Center, Protected View and untick all the options within the Protected View section:

Alternatively use group policy to turn it off:

User Configuration, Admin Templates, Microsoft Excel 2010, Excel Options, Security, Trust Center, Protected View:

Note the use of ‘enabled’ to turn this option off – see notes:

 

Microsoft blurb on protected mode opening conditions:

By default, files open in Protected View if any one of the following conditions is true:

• A file skips or fails Office File Validation Office File Validation is a new security feature that scans files for file format exploits. If Office File Validation detects a possible exploit or some other unsafe file corruption, the file opens in Protected View.

• AES zone information determines that a file is not safe Attachment Execution Services (AES) adds zone information to files that are downloaded by Microsoft Outlook or Microsoft Internet Explorer. If a file’s zone information indicates that the file originated from an untrusted Web site or the Internet, the downloaded file opens in Protected View.

• A user opens a file in Protected View Users can open files in Protected View by selecting Open in Protected View in the Open dialog box, or by holding down the SHIFT key, right-clicking a file, and then selecting Open in Protected View.

• A file is opened from an unsafe location By default, unsafe locations include the user’s Temporary Internet Files folder and the downloaded program files folder. However, you can use Group Policy settings to designate other unsafe locations.

In some cases, Protected View is bypassed even if one or more of the previously listed conditions are met. Specifically, files do not open in Protected View if any one of the following is true:

• A file is opened from a trusted location.

• A file is considered a trusted document.

Relevant links:

http://blogs.technet.com/b/office2010/archive/2009/08/13/protected-view-in-office-2010.aspx

http://office.microsoft.com/en-us/excel-help/what-is-protected-view-HA010355931.aspx

 

 

 

Powerpoint files become corrupted after saving

We had a number of powerpoint users complaining that their slide shows were suddenly becoming corrupt even though they had experienced no crash or error message. Just saved as normal but next time they try and open it they get an error:

“The server application source file or item cannot be found or returns an inknown error you may need to re-install the server application.”

We found the cause of this was that network drives were being disconnected and re-connected after the user had opened the slide show.

For example:

1. User logs on to machine

2. They immediately open a Powerpoint slide show from an existing network drive and start working on it

3. The user is using a machine on a corporate  network which runs a logon script. This script has not completed its tasks and part of the script makes sure all relevant network drives exist by disconnecting and re-connecting the network drives (does this to avoid situation where a rogue network drive mapping has occurred i.e. X is connected to something different than what the company policy stipulates).

4. One of these network drives contains the file the user is working on. The file is now in memory and everything carries on as normal but when the user later saves the file it becomes corrupted without informing the user.

This is a know Bug in Powerpoint – basically Powerpoint cannot handle removable media which the network drive will be seen as.

This problem was particularly prevelant on terminal server sessions where the logon script can take some time to complete.

A workaround is to force the logon script to run before explorer.exe loads using group policy:

Run logon scripts synchronously

User ConfigurationAdministrative TemplatesSystemLogon
Description
Directs the system to wait for logon scripts to finish running before it starts the Windows Explorer interface program and creates the desktop.
If you enable this policy, Windows Explorer does not start until the logon scripts have finished running. This setting assures that logon script processing is complete before the user starts working, but it can delay the appearance of the desktop.

If you disable this policy or do not configure it, the logon scripts and Windows Explorer are not synchronized and can run simultaneously.

This policy appears in the Computer Configuration and User Configuration folders. The policy set in Computer Configuration takes precedence over the policy set in User Configuration.

I’ve also attached a screenshot of the location of this feature:

An alternative workaround is to manage your logon script so that disconnections are not needed. In the example given you could issue a group policy to disable users ability to map drives. You would then only need to connect drives in the logon script: