CAST SUBSTRING to NUMERIC SSIS 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.
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.



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.

&lt;Biml xmlns="http://schemas.varigence.com/biml.xsd"&gt;
    &lt;FileFormats&gt;
        &lt;FlatFileFormat Name="FFF" IsUnicode="false" FlatFileType="Delimited"&gt;
            &lt;Columns&gt;
                &lt;Column Name="EntireRow" DataType="AnsiString" Delimiter="CRLF" Length="58" /&gt;
            &lt;/Columns&gt;
        &lt;/FlatFileFormat&gt;
    &lt;/FileFormats&gt;
    &lt;Connections&gt;
        &lt;FlatFileConnection FilePath="C:\ssisdata\dba_89464.txt" FileFormat="FFF" Name="CM_FF" /&gt;
    &lt;/Connections&gt;
    &lt;Packages&gt;
        &lt;Package ConstraintMode="Linear" Name="dba_89464"&gt;
            &lt;Tasks&gt;
                &lt;Dataflow Name="DFT Source"&gt;
                    &lt;Transformations&gt;
                        &lt;FlatFileSource ConnectionName="CM_FF" Name="FF Source" /&gt;
                        &lt;DerivedColumns Name="DER Split to row type"&gt;
                            &lt;Columns&gt;
                                &lt;Column DataType="AnsiString" Name="Header" Length="2"&gt;SUBSTRING([EntireRow],1,2)&lt;/Column&gt;
                            &lt;/Columns&gt;
                        &lt;/DerivedColumns&gt;
                        &lt;ConditionalSplit Name="CSPL - Split on Header"&gt;
                            &lt;OutputPaths&gt;
                                &lt;OutputPath Name="Type 2"&gt;
                                    &lt;Expression&gt;[Header] == "02"&lt;/Expression&gt;
                                &lt;/OutputPath&gt;
                            &lt;/OutputPaths&gt;
                        &lt;/ConditionalSplit&gt;
                        &lt;Multicast Name="MC Demo approaches"&gt;
                            &lt;InputPath OutputPathName="CSPL - Split on Header.Type 2" /&gt;
                            &lt;OutputPaths&gt;
                                &lt;OutputPath Name="String Approach" /&gt;
                                &lt;OutputPath Name="Math Approach" /&gt;
                            &lt;/OutputPaths&gt;
                        &lt;/Multicast&gt;
                        &lt;!--
                        Generate our value as string and one without the decimal place but of a numeric type
                        --&gt;
                        &lt;DerivedColumns Name="DER Type 2 processing"&gt;
                            &lt;InputPath OutputPathName="MC Demo approaches.String Approach" /&gt;
                            &lt;Columns&gt;
                                &lt;Column DataType="AnsiString" Name="strTemporaryMyValue" Length="18"&gt;TRIM(SUBSTRING(EntireRow,11,18))&lt;/Column&gt;
                            &lt;/Columns&gt;
                        &lt;/DerivedColumns&gt;
                        &lt;!--
                            Add our decimal place, string operations
                            This is not concerned with empty strings, etc
                        --&gt;
                        &lt;DerivedColumns Name="DER Add decimal place"&gt;
                            &lt;Columns&gt;
                                &lt;Column DataType="AnsiString" Name="withDecimalPlace"  Length="19"&gt;SUBSTRING([strTemporaryMyValue], 1, LEN([strTemporaryMyValue]) -2) + "." + RIGHT([strTemporaryMyValue], 2)&lt;/Column&gt;
                            &lt;/Columns&gt;
                        &lt;/DerivedColumns&gt;

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

                        &lt;DerivedColumns Name="DER Dataviewer connector String" /&gt;


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

                        &lt;DerivedColumns Name="DER Dataviewer connector Math" /&gt;
                    &lt;/Transformations&gt;
                &lt;/Dataflow&gt;
            &lt;/Tasks&gt;
        &lt;/Package&gt;
    &lt;/Packages&gt;
&lt;/Biml&gt;