Fix SSIS 2008 Error - The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."

When importing a datasource from a flat file today (a csv), (which ran through a Derived Column Transformation and then a Data Conversion Transformation before hitting SQL), I hit the following error in SQL Server Integration Services 2008 (SSIS 2008):

"Data conversion failed while converting column .....The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data."

The dataviewers and error output SSIS debugging techniques didn't show anything unusual in the data as it was transformed. In addition, this problem only occurred on my float columns, and only when the data in the columns is blank. I did some quick checks to see if there were a few extra spaces in some of the fields and did a TRIM() for the derived column - but the same error kept coming up. I even enabled "Ignore truncation" and it still didn't work.

Information: 0x402090DE at ImportTemplateData, Flat File Source [2917]: The total number of data rows processed for file "C:\WorkforceProfileInitialImport\Q2_2008_162_Data.csv" is 1050.
Error: 0xC02020C5 at ImportTemplateData, Data Conversion [4034]: Data conversion failed while converting column "[8c Override Census Period FTE]" (2050) to column "Copy of [8c Override Census Period FTE]" (5338). The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at ImportTemplateData, Data Conversion [4034]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "output column "Copy of [8c Override Census Period FTE]" (5338)" failed because error code 0xC020907F occurred, and the error row disposition on "output column "Copy of [8c Override Census Period FTE]" (5338)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at ImportTemplateData, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Data Conversion" (4034) failed with error code 0xC0209029 while processing input "Data Conversion Input" (4035). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Information: 0x40043008 at ImportTemplateData, SSIS.Pipeline: Post Execute phase is beginning.

The underlying issue was that it was trying to convert these blank strings (from the flat file source datatype) into the float datatype and was thereforce constantly failing. The fix is to tick on the "Retain null values from the source as null values in the data flow" and the package then started to run successfully for those columns which had just blank values.

Fix - SQL Reporting Services 2008 - An error occurred during local report processing. Access Denied with HRESULT 0x80030005

While attempting to preview a report developed by another user (after getting it out of Visual Source Safe), I was confronted by the following error:

An error occurred during local report processing. Access Denied. (Exception from HRESULT: 0x80030005 (STG_E_ACCESSDENIED))

I created another report exactly the same and it seemed to work fine - so there was some other kind of caching issue causing this error.

After investigating, I noticed that the REPORTNAME.rdl.data file (which is a binary file and is how SSRS reports cache their preview information) - was read-only. SSRS was trying to write to the file and consequently getting the access denied errors. This was because the rdl.data file had erroneously been put into source control.

The fix was to remove it from source control (as it should be generated as each user previews the report), and mark the local file as read only.

Of course it would be much nicer (and simpler to troubleshoot the issue) if this particular "Access Denied" error message was more descriptive and actually told me *which* file it was trying to access.

Fix - SharePoint 2007 Search Scopes not Displaying In Search Scope Dropdown after Deleting and Recreating the Shared Services Provider

To fix an issue with the Crawler mentioned in my previous blog article at
http://ddkonline.blogspot.com/2009/07/fix-when-sharepoint-2007-search-index.html
, I was forced to create a new Shared Services Provider. Everything seemed to work fine apart from the fact that I later noticed that the search scopes dropdown was just showing a single record, when it was previously showing 3 items (This Site, and the Shared Scopes "All Sites", and "All People"

The Problem

Only one search scope is showing in the dropdown (ie "This Site:SITENAME"), or This Site: Home in the example.

This problem has been encountered by others as well:
http://blogs.msdn.com/cjohnson/archive/2006/10/19/moss-search-scopes-missing-in-action.aspx

Found out by comparing to our UAT environment (which did not have the Permanent Crawling issue), that the main difference was that the Display Group for my Search scopes was a name I made up called "Shared", and then moved them from the "Unused" display group to my new "Shared Group".

The Solution:

The trick is that the Search scopes will only show in the search dropdown if the Display group is named "Search Dropdown". Once this is updated, it will magically show up in the list.

On a side note, this is a very clunky and obscure/non-intuitive mechanism for the population of the search dropdown. I would much prefer that it be an explcit flag on the Display group of "Display In Search Dropdown". This would make it much clearer for everyone.

SharePoint 2007 - Things to Check When People Search Doesn't Work

There are a few basic things to check when People Search in SharePoint 2007 does not return as many results as expected:

1. Make sure the User Profile Connection is valid. One issue which I've encountered is when the domain name is auto-detected incorrectly by SharePoint and so attempts to crawl users within an invalid domain. For example, my client's domain name is LOCATION.COMPANYNAME.LOCAL. SharePoint detected the domain name as "LOCATION", but the real NETBIOS domain name was "LOCATIONCOMPANYNAME". You can double check this by going to Shared Services Administration ->SharedServices1 > User Profile and Properties > Manage Connections and try to make a new connection. If the auto-discover is invalid, it will run validation and show the following error:
   
   
2. Even when the User Profile Crawl takes place, search results will only show when the The Search Crawl has to run on the crawled user profiles. In addition, you will need to confirm that under Search settings > Content Sources that the content sources are in fact correctly set up. In particular, click "Edit Content Source" from the Content Sources list and look under the "Start Addresses" section. For people search to work, it should list at least one entry with the "sps3" pseudo-protocol. e.g you should have at least one entry such as:
   sps3://servername. Otherwise, search will not crawl any User Profiles.
   
   

Fix - When SharePoint 2007 Search Index Crawler is in “Crawling” status indefinitely and doesn't respond to stop requests

This problem started happening in a SharePoint production environment.

The Problem:
The Crawl in SharePoint is permanently in a "Crawling State" permanently. In addition, "View Scopes" read as "not available" and "Scope Properties and Rules" just show item counts of "Total: error".



While there were some suggestions on the web to following (e.g. at http://www.tech-archive.net/Archive/SharePoint/microsoft.public.sharepoint.portalserver/2007-02/msg00284.html)
stsadm -o osearch -action
stopstsadm -o osearch -action start -role indexQuery

And also to "Reset All Crawled Content”.

The Solution:
However, none of these solutions worked for me - after running these commands, the Content crawl status stayed indefinitely in a "Stopping" state and never stopped. It would appear that it is in some kind of infinite loop while crawling. After trying about 30 different courses of action, I finally came across a solution. This was basically to blow away the Shared Service Provider (SSP) Completely and move all existing services over to the new service provider:
http://msmvps.com/blogs/obts/archive/2006/12/18/432542.aspx

Here is the solution to fix it:
1. Go to SQL Server database SharedServices1_Search_DB and delete the records from all the tables with crawlID column (ex. CrawlHistory table).
2. This will stop the crawl.
3. Create a new SharedService with new database using SharePoint Centeral Admin
4. Set new Shared Service to default (by choosing Shared Service Providers in Central Admin and clicking on the "Change Associations" link.
5. Delete the old Shared Service (NOTE that even though the admin service is still against "SharedServices1", it will not get deleted with the SSP - it will automatically migrate over to the new default SSP.)
6. Run the User Profile Crawl (if using Active Directory Integrated Profiles)
7. Setup the crawl rules and run the full crawl.

You'll get clean search crawl results. Problem Solved!

[Update 8 July 2009]
Note that you will also need to reassociate your search scopes with the correct display group "Search Dropdown" after recreating your SSP. See http://ddkonline.blogspot.com/2009/07/sharepoint-2007-search-scopes-not.html

Fix - "There has been an error while processing the form" when Submitting a browser enabled InfoPath Form

I just redeployed an InfoPath 2007 form to a SharePoint 2007 server location that had previously been working fine in our SharePoint UAT and DEV environments. Problem is, it just stopped working on submission with the error
"There has been an error while processing the form". In the Event log and ULS Logs.

There was a form postback error. (User: DOMAINNAME\USERNAME, Form Name: RenewalOfLeaseForm, IP: , Request: http://SERVERNAME/_layouts/Postback.FormServer.aspx, Form ID: urn:schemas-microsoft-com:office:infopath:RenewalOfLeaseForm:-myXSD-2009-05-07T05-54-18, Type: ArgumentException, Exception Message: Value does not fall within the expected range.)

None of the validation picked up any issues and deployment all worked fine - but errors occurred on submission. Some articles suggested (e.g. http://www.infopathdev.com/forums/p/5546/25579.aspx ) that the problem was with a secondary datasource - but this would not explain why it was working in the other environments.

Turns out the problem was that when I specified the location of the form template during publication to the production environment, I pasted an extra forward slash into the publishing wizard (see screenshot). This only seemed to generate an error on the submission of the form - but it published without issues. This would suggest that the publication wizard should really do some additional validation when publishing InfoPath forms to a SharePoint location.

How to register and activate the Bamboo Mini Calendar Web Part after Installation

Bamboo solutions (http://www.bamboosolutions.com/) is a 3rd party SharePoint web part provider with some great controls. Many of these web parts can operate together to create some powerful functionality that SharePoint 2007 doesn't provide out of the box (e.g. doing things that the Content Query Web Part cannot do like the rollup of calendars from multiple sites into one site - and displaying them as a calendar).



My client loved the functionality so much (especially the div popups when the user hovers over different days in the calendar) that they purchased a suite of licenses for it. Problem is that it is not particularly clear from the Bamboo Solutions website how to register their web parts after you've purchased a license.

The List Rollup Web Part is installed in the GAC, so it shows up in the Windows Application called the "Bamboo License Manager" in a drop down - however, turns out that the calendar web parts are installed to physical locations (ie in the bin directory of your Site Collection) rather than the GAC. Consequently, they won't ever show up in this dropdown.

It seems odd that the install methods are inconsistent, but the controls work well so I'm not going to complain. Consequently, you will have to browse each of your site collection physical locations (e.g. c:\inetpub\wwwroot\wss\Virtual Directories\PORTNUMBER) and activate your license for each individual site collection you use your mini-calendar or other web part control.

Now you can just do an iisreset at a command prompt and your Bamboo web part is now fully activated!