How Can I Turn A String Into A Decimal In Ssis Using A Derived Column?

I am working in SSIS to fill a staging area database. I want to transfer the following data: ------------------------------------------------------------------- | ShipName (String)

Solution 1:

To restate the problem, you need to split the source column based on the location of the first space. We need to keep the first half from the space and then make that into a decimal (or leave as string and use Gordon's approach for implicit conversions). Makes no matter to me on that point.

When I look at problems like this, I wonder where all things can go wrong which influences my design.

With that in mind, I am going to add 3 derived column expressions to my data flow to solve this.

Set up

I added an OLE DB Source to my package and use the following query

SELECT'74.60 Liters'AS FuelCapacity
UNIONALLSELECT'121.28 Liters'AS FuelCapacity
UNIONALLSELECT'341.56 DeciLiters'AS FuelCapacity
UNIONALLSELECT'0.0 Liters'UNIONALLSELECTNULL

The last 3 are not supplied but seem like reasonable values for the domain.

DER Space

The purpose of this component is to identify the space in the inbound text. I will add a new column to the data flow called SpaceOrdinal as that will be based on the following expression

FINDSTRING([FuelCapacity]," ",1)

DER Get First String

The purpose of this component is to slice out the data we want based on the ordinal position we obtained in the previous step. I created a new column called FirstString as it will contain the first piece of string data.

LEFT([FuelCapacity],[SpaceOrdinal])

Der Convert to decimal

This step is optional as you can let implicit conversion happen and maybe you do/don't want it to. New column, called LitersOfFuel;

(DT_NUMERIC,10,2)FirstString

That works with the supplied data.

But what if the 0.0 Liters was just 0 Liters? The conversion here would fail as the conversion is quite brittle. But you can add a precursor step to transform the data into something that converts better with the default cast.

Solution 2:

You can just load the data in:

insert intoreal_table (ShipName, MaxContainers, FuelCapacity)
    select ShipName, MaxContainers, FuelCapacity
    from staging;

SQL Server will do the conversion implicitly. I'm not a great fan of implicit conversions, but if you know the data is correct, then there is probably no harm for a one-time operation.

However, you might want to validate the data first:

select fuelCapacity
from staging
wheretry_convert(numeric(?, ?), fuelCapacity) isnulland
      fuelCapacity isnotnull;

This will return values that are not correct.

EDIT:

For the supplied data, you want the leading number on the string, apparently:

insertinto real_table (ShipName, MaxContainers, FuelCapacity)
    select ShipName, MaxContainers,
           left(FuelCapacity, charindex(' ', FuelCapacity) -1)
    from staging;