SSIS - Derived task expression help to check if numeric

Question

SSIS - Derived task expression help to check if numeric

Vishal Parikh 1

hi all,

my data is coming from excel to sql via ssis package.

now in my excel , sometimes due to formula i am getting data like #ERROR,#DIV/O,-,N.A,n.a and etc..

Now i want to put condition that,If data is numeric then leave as it is and if it is not numeric then replace source value with db-null.

can you please help me for derived column expression.so,my pakage will not fail due to this string values.

2 answers

Your answer

Answer 1

Monalv-MSFT 5,926

Hi @VishalParikh-3429,

According to my test, we can add Data Conversion and Derived Column in Data Flow Task.

1.Add a Data Conversion to change the Data Type of column as DT_I4.
2.Set the Configure Error Output in Data Conversion.
3.Set the value of column in error data as NULL(DT_I4) in Derived Column.

Please refer to the following pictures:

Best Regards,
Mona

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Monalv-MSFT 5,926 Reputation points

2020-09-11T09:29:32.193+00:00

Hi @Vishal Parikh ,

May I know if you have anything to update?

Best Regards,
Mona
Anonymous

2023-07-11T07:14:10.6566667+00:00

In the same context i need to check if columns on the csv are numeric then load the file on the landing table , if not move the file to error folder , how can this be achieved.

Answer 2

Guoxiong 8,206

Try this:
(DT_I4)YourField == (DT_I4)YourField ? (DT_I4)YourField : NULL(DT_I4)

Share via

SSIS - Derived task expression help to check if numeric

2 answers

Your answer