1

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:

sample column

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Seth
  • 33
  • 3

1 Answers1

0

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:

(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
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    I used your code for the derived column + the guide, I got it to work but I have a bit of a monster of a dataflow, though it at least does what I need it to. Time to learn some C# to cut out all these extra steps. Thank you! – Seth Mar 03 '22 at 20:33