0

I have the following SSIS package:

enter image description here

In my package, flat file source data feeds into a conditional split component. One of the columns is AccountNo. That column in the flat file source output datatype is: string [DT_STR].

I want to check in the Constraint Check component if AccountNo is an integer. For example, maybe that column in the flat file source is this:

12355ss2rt3

and that input is not an integer. I want to pass a valid integer number to the constraint check output. In the constraint check component, I have this:

enter image description here

this expression uses Type Cast Group and (DT_I8) condition, but I get this error:

enter image description here

How can I solve that?

Improve this question

2 Answers 2

Reset to default
2

You can use derived column to do that.

In my example, the input is:

AccountNo
123
1234
123f
1e23

Step 1: Try to cast string to integer

Just use derived column, try to cast to I8

(DT_I8)AccountNo

Step 2: Config error out, to catch error row

The success casting rows will go to the nomarl flow, the value that can not cast to I8 will be redirect to the error flow. The general data flow is like this (view screenshot): Data flow

To get the error value, you just drap and drop the red arrow from the driver column.

Now the red flow is contain the value that can not cast to integer the blue flow is flow with value successful casting.

view following screenshot for detail:

Screenshot - Step 2 Config error output

Step 3: process uncast value with the way you want

note that the output of error flow will give you 2 valuable columns are:

  • [ErrorCode]
  • [ErrorColumn]

Let use these column to make sure the error from "cast to integer fail".

You can refer to msdn document to lookup for ErrorCode.

Base on ErrorColumn, you can make sure the error come from column AccountNo or not.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

Directly using conditional split you will not able to do this.

use derived column /Script component to check if data is numeric or not

create an derived column isNumeric

Expression : (DT_I4)AccountNo == (DT_I4)AccountNo ? 1 : 0

now use this in conditional split .

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.