Flat File Source Patterns - SQL Server Integration Services Design Patterns - page 185

depending on the data type chosen. Converting these data to the DT_I4 data type al-
lows me to store that value in 4 bytes. As an added bonus, the data are numeric, so
sorts on this field will outperform sorts on a string data type.
Let's manipulate the data types provided by the Flat File connection manager and
source adapter. Drag a Derived Column transformation onto the data flow canvas and
connect a data flow path from the flat file source to the new Derived Column trans-
formation. Double-click it to open the editor.
Expand the Type Casts virtual folder in the SSIS Expression Language functions
provided in the listbox in the upper-right section of the Derived Column Editor. Drag a
DT_STR type cast into the Expression cell of the first row of the Derived Column
grid in the lower section of the editor. The Derived Column column of the grid de-
faults to “<add as new column>” but allows you to choose to replace the value in any
of the rows flowing through the transformation. You can make changes to the values as
rows flow through the Derived Column transformation, but you cannot change the data
type (which is precisely what you're going to do here), so you need to add a new
column to the data flow. The default derived column name is Derived Column n , where
n is a one-based array of columns configured in the transformation. Change the default
derived column name to strRecordType . Return to the Expression cell and com-
plete the DT_STR cast function by replacing the «length» placeholder text with the de-
sired length of the field: 1. Next, replace the «code_page» placeholder with the number
that matches your Window Code Page identifier. For U.S. English, this number is 1252.
To complete the configuration, expand the Columns virtual folder in the Available In-
puts listbox located in the upper-left section of the Derived Column Transformation
Editor, and drag the RecordType column into the Expression cell to the right of
the DT_STR cast function that you just configured.
When you click anywhere else in the editor, the logic of the transformation valid-
ates the expression. This has been happening all along; the text color was being
changed to red in the Expression when an issue was encountered with the state of
the expression. When you navigate off the Expression cell now, the expression
(DT_STR, 1, 1252) [RecordType] should pass muster. The text should re-
turn to black to indicate a valid expression.
You can similarly create additional columns with casting expressions to manipulate
the data types of the other fields moving through the data flow. Figure 7-4 shows how
my example looks when I've completed editing the Derived Column transformation.