Search code examples
sqlsql-serverssisscript-task

SSIS fill in Null Value with Non-Null Value

I'm currently able to get this type of output from a flat file after running a script component in SSIS:

Current Output

My goal is to fill in the rest of the column with the same serial number like this:

Goal Output

I tried to do this by setting it a variable and using a derived column, but the variable isn't set til after the data flow. Also the script only reads line by line so I don't see an obvious way to do this simple task.

Is there a way to create a column in the script and give it a default value of the serial number? Or is there a way for use a SQL command to update the Null values Where the column is Not Null?

Solution

  • You can try this design in order to perform what you need (feel free to simplify it, tested with your input data) : enter image description here

    1. Read your source
    2. Multicast
    3. Aggregate on the column you want the max value (serial number)
    4. create a derived column with a constant value ( 1 for example)
    5. sort the set on the constant value
    6. Merge your two set with a full join using the constant key
    7. import data into the destination table and use the new aggregated column to fill the Serial number column