Replace null value in SSIS with value from previous row?

I am pulling in a flat file that formatted like the following example.  Basically there is a column that has an ID in it and that a few rows about that ID.  I want to import the data using SSIS that will pull add the ID in every row.


Current:
cardholder	balance1	balance2	date
12345	        1.11	        1.11	       102010
	        0	        0	       92010
	        0	        0	       82010
123456	        23.33	        23.33	       102010
	        0	       0	       92010

Want to be:

cardholder	balance1	balance2	date
12345	        1.11	        1.11	       102010
12345	        0	        0	       92010
12345	        0	        0	       82010
123456	        23.33	        23.33	       102010
123456	        0	       0	       92010

Open in new window

LVL 1
mtuepkerAsked:
Who is Participating?
 
strickddConnect With a Mentor Commented:
I think you will need to loop through all the records from the flat file as you import them and do something like this (note, it is just psedo-code)

DECLARE currentID int

BEGIN LOOP
   IF currentRowID IS NULL
      SET currentRowID = currentID
   IF currentID <> currentRowID
      SET currentID = currentRowID
 
   --Import data with currentID as the cardholder id val
END LOOP
   
0
 
mtuepkerAuthor Commented:
Good solution, thanks.
0
 
mtuepkerAuthor Commented:
Sorry, didnt mean to give it a C rating...too early in the morning to be playing on EE apparently.  What I ended up doing (thanks to a time crunch) was just importing the file in w/ it as null and then running this:

SELECT  *,
        ISNULL(cardid, (SELECT TOP 1 cardid FROM table WHERE ID < t.ID AND cardid IS NOT NULL ORDER BY ID DESC))
FROM    table t

to get the IDs as I needed them.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.