dotNetChris @ Marisic.Net

January 28, 2010

Making text proper case in SSIS 2008 with a script component

Filed under: Sql Server — Tags: , — dotnetchris @ 1:59 pm

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;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
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.

September 10, 2008

Working with SSRS – Sql Server Reporting Services

So I started my new job doing .NET Consulting work using the 3.5 framework last week unfortunately the start of my job has mostly been limited to updating stored procedures in sql server (thank god I’m done with you oracle) and the reports in sql server reporting services (SSRS) that use them. SSRS sure seems to have alot of quirks and some unusual functionality.

Through out my upcoming days I’ll be posting any minor tips or ins and out that I stumble upon to have them tied together in 1 post both for my reference and hopefully the benefit of others.

Tip #1 -  Inserting text or a Line Break into a text box on the report with data fields

=Fields!LastName.Value & “, ” & Fields!FirstName.Value & vbCrLf & Fields!MiddleName.Value

Tip #2 – Convert a DateTime field to only display the Date portion

=CDate(Fields!BirthDate.Value).ToShortDateString()

Tip #3 – Working with bit flags and IIf statement proper comparison, note case after the equality

iif(Parameters!IsBitFlagSetForSomething.Value = True, “Bit Flag is set”, “Bit Flag is not set”)

Tip #4 Alternating Row background colors, set the background property to:

=iif(RowNumber(Nothing) Mod 2, “LightGrey”,”white”)

More to come hopefully

BloggingContext.ApplicationInstance.CompleteRequest();

The Shocking Blue Green Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 242 other followers

%d bloggers like this: