SQL Server 2014 – How to Drop the Last Character of String if It Is ‘s’

I was able to reproduce this in an Azure VM, and resolve by simply setting the primary database offline and dropping the snapshot, eg

ALTER DATABASE AdventureWorks2012 SET OFFLINE
DROP DATABASE AdventureWorks2012_SN001

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.

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