Making text proper case in SSIS 2008 with a script component

Drop a new script component on your dataflow task and wire up the input to it. Select the input column that you want to be proper cased. Also set an output column that you will want the correctly cased text to flow from (or you could set it up to alter your input column if you wanted)

After that edit your script so the script editor opens up, the first thing you need to do is right click on the references folder on the right and goto add reference and under the .NET tab pick Microsoft.VisualBasic. Then your code should look similar to

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.VisualBasic;
using System.Globalization;

public class ScriptMain : UserComponent
    public override void Input0_ProcessInputRow(Input0Buffer Row)
        Row.Name = Strings.StrConv(Row.County, VbStrConv.ProperCase,CultureInfo.CurrentCulture.LCID);

This should do it, the properties you need to use Row.Name and Row.County those are all dependent on the columns you specify for your input and output mapping.


Sql Server 2008 File Streaming

So today I was talking in the office about how much Sql Server has grown especially with the relase of 2008 where it really can stand up against Oracle performance wise and even beat Oracle in performance when dealing with Binary and other BLOB type data structures.

If you’re not aware of this feature in Sql Server 2008 you can chose to stream data to be sent to the file system of the server (or probably any mapped network location) instead of it being stored inside the table structure which massively shrinks the table size which immensely improves performance that the table doesn’t have a few hundred megs or into gigabytes of binary data to sift through while it’s doing row relational operations.

I found an example of this on RDoherty’s blog on MSDN while also browsing his blog his newest post also had top 10 new features / enhancements of Sql Server 2008 for Developers both are a great read.

Getting Traction with SQL Server 2008 Filestream

SQL Server 2008 Top 10 List for Developers


An error occured during local report processing

My first biggest headache with sql server reporting services was dealing with this report set that had toggle buttons that could collapse and unfold portions of the report along with a navigation tree built into the report. Both of these features I wasn’t even aware existed until just recently, I’ve found out since then that the toggled reports are known as “drill down reports” and the navigation adeptly named “document navigation”

I was attempting to update a report using these features however anytime I would click a toggle button or try to navigate a link or use the page change buttons I would be presented with this most horrific and meaningless error message ever

An error occurred during local report processing. An internal error occurred on the report server. see the error log for more details.

Great, so an error occured go look at the logs, eventually I figure out that the logs it is referring to are stored in drive:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\LogFiles for Sql Server 2005. In the log files I found 1 error message relating to an invalid in line sql data set for a subreport which I fix figuring that would be it. I build the reports again goto preview and am presented with the exact same error again.

After a great long time searching trying to find cause I eventually found an article that pointed me to this Microsoft article (I’m not sure why I didn’t find this in Google originally usually they’re at the top of the list).

FIX: Error message when you try to drill through a report in SQL Server 2005 Reporting Services: “An internal error occurred on the report server. See the error log for more details”

Which the solution was simple, update sql server 2005 to service pack 2 and all would be all, and so it was said and so it was done and so it was well.