patternsqlMinor
CAST SUBSTRING to NUMERIC SSIS
Viewed 0 times
substringssisnumericcast
Problem
I have a fixed width text file that I am bringing in through SSIS. As part of the input into SQL Server I am grabbing a string of characters and trying to CAST them as numeric in a derived column.
The data looks like this:
I need to grab 17943 and convert it to 179.43
The expressions I have tried are:
I keep getting the following error:
The ProcessInput method on component "Derived Column" (2) failed with
error code 0xC0209029 while processing input "Derived Column Input" .
I have looked but have not been able to find a solution to this.
The data looks like this:
02 PR000000000000017943 0287801709I need to grab 17943 and convert it to 179.43
The expressions I have tried are:
(DT_NUMERIC,18,2)(SUBSTRING(EntireRow,10,18)
(DT_NUMERIC,18,2)TRIM(SUBSTRING(EntireRow,10,18))I keep getting the following error:
The ProcessInput method on component "Derived Column" (2) failed with
error code 0xC0209029 while processing input "Derived Column Input" .
I have looked but have not been able to find a solution to this.
Solution
Casting a string of
Also, given your source data, your substring formula is off. It's resulting in a source string of
That said, I take one of 2 approaches. Either I use a string operation to insert the decimal place or I use math to divide by 100. If I'm already dealing with strings, I usually stick with that approach as I hate to keep casting values about.
Off to lunch, but here is the Biml I used to generate the package. Grab bidshelper and use that to generate the same SSIS package as me.
000000000000017943 to decimal(18,2) is going to result in a value of 17943 The source doesn't have a decimal place, so why would the cast know you need a decimal there?Also, given your source data, your substring formula is off. It's resulting in a source string of
R00000000000001794That said, I take one of 2 approaches. Either I use a string operation to insert the decimal place or I use math to divide by 100. If I'm already dealing with strings, I usually stick with that approach as I hate to keep casting values about.
Off to lunch, but here is the Biml I used to generate the package. Grab bidshelper and use that to generate the same SSIS package as me.
SUBSTRING([EntireRow],1,2)
[Header] == "02"
TRIM(SUBSTRING(EntireRow,11,18))
SUBSTRING([strTemporaryMyValue], 1, LEN([strTemporaryMyValue]) -2) + "." + RIGHT([strTemporaryMyValue], 2)
(DT_NUMERIC, 18, 2)[withDecimalPlace]
(DT_NUMERIC, 20, 0) TRIM(SUBSTRING(EntireRow,11,18))
(DT_NUMERIC, 18, 2)decTemporaryMyValue/100.0
Code Snippets
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<FileFormats>
<FlatFileFormat Name="FFF" IsUnicode="false" FlatFileType="Delimited">
<Columns>
<Column Name="EntireRow" DataType="AnsiString" Delimiter="CRLF" Length="58" />
</Columns>
</FlatFileFormat>
</FileFormats>
<Connections>
<FlatFileConnection FilePath="C:\ssisdata\dba_89464.txt" FileFormat="FFF" Name="CM_FF" />
</Connections>
<Packages>
<Package ConstraintMode="Linear" Name="dba_89464">
<Tasks>
<Dataflow Name="DFT Source">
<Transformations>
<FlatFileSource ConnectionName="CM_FF" Name="FF Source" />
<DerivedColumns Name="DER Split to row type">
<Columns>
<Column DataType="AnsiString" Name="Header" Length="2">SUBSTRING([EntireRow],1,2)</Column>
</Columns>
</DerivedColumns>
<ConditionalSplit Name="CSPL - Split on Header">
<OutputPaths>
<OutputPath Name="Type 2">
<Expression>[Header] == "02"</Expression>
</OutputPath>
</OutputPaths>
</ConditionalSplit>
<Multicast Name="MC Demo approaches">
<InputPath OutputPathName="CSPL - Split on Header.Type 2" />
<OutputPaths>
<OutputPath Name="String Approach" />
<OutputPath Name="Math Approach" />
</OutputPaths>
</Multicast>
<!--
Generate our value as string and one without the decimal place but of a numeric type
-->
<DerivedColumns Name="DER Type 2 processing">
<InputPath OutputPathName="MC Demo approaches.String Approach" />
<Columns>
<Column DataType="AnsiString" Name="strTemporaryMyValue" Length="18">TRIM(SUBSTRING(EntireRow,11,18))</Column>
</Columns>
</DerivedColumns>
<!--
Add our decimal place, string operations
This is not concerned with empty strings, etc
-->
<DerivedColumns Name="DER Add decimal place">
<Columns>
<Column DataType="AnsiString" Name="withDecimalPlace" Length="19">SUBSTRING([strTemporaryMyValue], 1, LEN([strTemporaryMyValue]) -2) + "." + RIGHT([strTemporaryMyValue], 2)</Column>
</Columns>
Context
StackExchange Database Administrators Q#89464, answer score: 6
Revisions (0)
No revisions yet.