Sql-server – CAST SUBSTRING to NUMERIC SSIS

sql serversql server 2014ssis-2014

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:

02 PR000000000000017943 0287801709

I 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.

Best Answer

Casting a string of 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 R00000000000001794

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.

enter image description here

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.

<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>
                        </DerivedColumns>

                        <DerivedColumns Name="DER Make string to decimal">
                            <Columns>
                                <Column DataType="Decimal" Name="MyValueViaString" Precision="18" Scale="2">(DT_NUMERIC, 18, 2)[withDecimalPlace]</Column>
                            </Columns>
                        </DerivedColumns>

                        <DerivedColumns Name="DER Dataviewer connector String" />


                        <DerivedColumns Name="DER slice string into numeric type">
                            <InputPath OutputPathName="MC Demo approaches.Math Approach" />
                            <Columns>
                                <Column DataType="Decimal" Name="decTemporaryMyValue" Precision="20" Scale="0">(DT_NUMERIC, 20, 0) TRIM(SUBSTRING(EntireRow,11,18))</Column>
                            </Columns>
                        </DerivedColumns>
                        <!--
                            Behold, the power of math
                        -->
                        <DerivedColumns Name="DER Divide approach">
                            <Columns>
                                <Column DataType="Decimal" Name="MyValue" Precision="18" Scale="2">(DT_NUMERIC, 18, 2)decTemporaryMyValue/100.0</Column>
                            </Columns>
                        </DerivedColumns>

                        <DerivedColumns Name="DER Dataviewer connector Math" />
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
</Biml>
Related Question