Search code examples
sql-serverssis

How to replace values on ETL process?

I have a simple ETL from CSV to SQL Table.

Here is the current workflow (see picture attached)

  • first object is a Flat file source
  • then a lookup
  • lookup match will update existing record (OLE DB Command - Execute stored procedure)
  • lookup no match will insert into SQL table (OLE DB Destination)

enter image description here

Here is the problematic csv data

ID Name Number
1 Jone 345
2 Jane 222
3 Phil ?

Data type for Number is Integer. So obviously Phil is throwing an error

Now I want to always replace "?" with zero . I have a plan in my head, but I don't know how to execute it. My plan is to load the CSV into a Temp table.And just run a Replace command, But I don't know where to start.

select Replace(Number, '?', '0') "Number" from Temp_Table1

How do I do that?

also open for other suggestion. But I need to be able to automate the clean up of "?"

Solution

  • Create a permanent staging table in your database.

    Then in your control flow

    1. Truncate the staging table in an Execute SQL Task
    2. Load the CSV file into the staging table with a Data Flow Task
    3. Transform and MERGE the staging table into the target table with another Execute SQL Task. As a best practice, build a stored procedure to encapsulate this logic.