Converting data in column in SSIS-CodePudding

I'm writing an SSIS package to load data from a .csv into a db.

There's a column in the csv file that is supposed to have a count, but the records sometimes have text, so I can't just load the data in as an integer. It looks something like this:

I want the data to land in the db destination as an integer instead of a string. I want the transformation to change any text to a 1, any blank value to a 1, and leave all the other numbers as-is.

My attempts have so far included using the Derived Column functionality, which I couldn't get the right expression(s) for it seems, and creating a temp table to run a sql query through the data, which kept breaking my data flow.

CodePudding user response：

There are three approaches you can follow.

(1) Using a derived column

You should add a derived column with the following expression to check if the values are numeric or not:

(DT_I4)[count] == (DT_I4)[count] ? [count] : 1

Then in the derived column editor, go to the error output configuration and set the error handling event to Ignore failure.

Now add another derived column to replace null values with 1 :

REPLACENULL([count_derivedcolumn],1)

You can refer to the following article for a step-by-step guide:

Validate Numeric or Non-Numeric Data in SQL Server Integration Services without the Script Task

(2) Using a script component

If you know C# or Visual Basic.NET, you can add a script component to check if the value is numeric and replace nulls and string values with 1

(3) Update data in SQL

You can stage data in its initial form into the SQL database and use an update query to replace nulls and string values with 1 as follows:

UPDATE [staging_table]
SET [count] = 1
WHERE [count] IS NULL or ISNUMERIC([count]) = 0