SSIS Package - Script Component for Proper Case Conversion Not Working!

• Anonymous

  Mr or Mrs. 500

  Points: 559

  More actions

  March 26, 2013 at 2:00 pm

  #275633

  SQL Server 2008/Visual Studio 2008

  I am attempting to import a fixed-width text file with data that is all Upper case using an SSIS package. As part of the procedure, I need to convert some of the data to Proper/Title Case . I am attempting to do this via a Script Component, but have not been successful. I have very basic SSIS knowledge and know nothing about Visual Basic as you will see below. The following are the sequence of steps I took in creating the Script Component:

  1)Drag over the script component into my existing data flow and select the Transformation radio button.

  2)Connect the a flat file source to the script component and the script component to an OLE DB destination.

  3)Open the script component and select Input Columns. I then select last_name and first_name the available input columns list and change the usage type for each to ReadWrite. I did not select outputs as I didn’t think it was necessary in this instance.

  4)Set the Script Language to Microsoft Visual Basic 2008 and the click Edit Script button. I then enter the following code (the format that at I found on-line) into the script screen:

  Imports System

  Imports System.Data

  Imports System.Math

  Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

  Imports Microsoft.SqlServer.Dts.Runtime.Wrapper

  Public Class ScriptMain

  Inherits UserComponent

  Dim last_name As String

  Dim first_name As String

  Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

  last_name = StrConv(last_name, VbStrConv.ProperCase)

  first_name = StrConv(first_name, VbStrConv.ProperCase)

  End Sub

  End Class

  5)When I paste this code, it generates errors telling me that last_name and first_name are not members of the group. I resolve it with Dim statements which remove the errors.

  6)I then map the two columns (last_name and first_name) in the OLE DB destination.

  7)I run the procedure without error, but the data is unchanged, still in all caps.

  Thanks for any assistance you can provide.