Best way to move trailing dashes (-) in SSIS

user_14176555 0
Viewed 244

I have a SSIS package which runs on a schedule and pulls in values from flat files. These flat files very occasionally contain negative values, however the negative symbol trails at the end of the value, e.g. 0.01020654-. When the SSIS package runs and tries to import these negative value into the table, I get the following error:

Error converting data type varchar to numeric.

The data type on the column is decimal(18, 10)

These values may not always be the same length, e.g. there may be a different number of decimal places every time. I guess I just need to move the '-' to the front of the value.

I have seen various solutions to the issue using SQL commands such as convert, should I add an extra Execute SQL Task to run this conversion, or maybe add a derived column? I'm just not sure on the best approach and would be interested to see potential solutions for this. The package runs fine 99% of the time, it's only when these negatives appear I'm getting issues.

Thanks in advance.

etl ssis sql-server
3 Answers

You can use a derived colum transformation with the following expression:

RIGHT([Column],1) == "-" ? "-" + SUBSTRING([Column],1,LEN([Column]) -1) : [Column]

If you need to check for null values you can use the following expression:

REPLACENULL([Column],"") == "" ? NULL(DT_WSTR,50) : RIGHT([Column],1) == "-" ? "-" + SUBSTRING([Column],1,LEN([Column]) -1) : [Column]

To convert values into decimal(18,10) you can use the following expression:

REPLACENULL([Column],"") == "" ? NULL(DT_DECIMAL,10) : RIGHT([Column],1) == "-" ? (DT_DECIMAL,10)("-" + SUBSTRING([Column],1,LEN([Column]) -1)) : (DT_DECIMAL,10)[Column]
user_70312300

I wouldn't recommend an extra SQL task. Instead you can try to handle it within the SQL query itself (therefore you should - if not already done - change the source of the dataflow from the table itself to a query):

DECLARE @t TABLE(
myVal NVARCHAR(100)
)

INSERT INTO @t VALUES
('100'),('-100'),('100-'),('100.00'),('-100.00'),('100.00-')

SELECT myVal, CAST(REPLACE(CASE WHEN RIGHT(myVal,1) = '-' THEN '-'+SUBSTRING(myVal,1,LEN(myVal)-1) ELSE myval END, ' ', '') AS DECIMAL(19,4)) AS myValDec
  FROM @t
user_61674400

in Derive column use

  1. (DT_NUMERIC,18,10)REPLACE(column,"-","")
  2. Set Derive column to "Add as New Column"
  3. Enter your newcolumnname on the Derive Column Name [1]: https://i.stack.imgur.com/YesCq.png
user_144888890
Related