Thursday, March 29, 2012

Converting DataType=TEXT to DataType=STR

SadHi,

I have an input text file which contains line(s) which can have more than 8000 characters.

I am able to read the lines in this file by specifying that the input column is data type = Text Stream [DT_TEXT] and I can then write the lines to another text file.

But, what I want to do is to use only the right-most 8000 characters. When I try to convert the input column to data type = String [DT_STR] using the Data Conversion transformation then I get the error:-

The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.

I cannot use the string manipulation functions e.g. RIGHT or SUBSTRING in a Derived Column transformation because these functions do not work with DT_TEXT data.
Any Ideas?!

Thanks.SadYou'd only need *1* line of custom code in a Script component transformation to extract your rightmost 8000 characters before moving on to the data type conversion.

-Doug|||Thanks Doug ... Did you mean a Script Component which looks something like:-

Public Class ScriptMain

Inherits UserComponent

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim ascii As Text.Encoding

Dim asciiChars(10) As Char

ascii.GetChars(Row.InputColumn.GetBlobData(1, 1), 0, 1, asciiChars, 0)

Dim asciiString As New String(asciiChars)

MsgBox(asciiString)

End Sub

End Class

When I run the package it fails at the ascii.getchars line with the following error message:-

Error: 0xC0047062 at Data Flow Task, Script Component [106]: System.NullReferenceException: Object reference not set to an instance of an object.

All I am trying to do is extract the first byte and convert it to an ascii char (before I go on to work out the lengths to extract the right-most 8000 chars etc.)

Is this what you had in mind? Or, is there another way to do it?

Many Thanks.

|||This should do what you want. You will need to import System.Text.Encoding.



Dim Start As Integer = Max(0, CInt(Row.InputColumn.Length) - 8001)
Dim Length As Integer = Min(8000, CInt(Row.InputColumn.Length))
Dim asciiString As String = ASCII.GetString(Row.InputColumn.GetBlobData(Start, Length)


|||Just to confirm that Jay's suggestion works. Many Thanks.sqlsql

No comments:

Post a Comment