• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1090
  • Last Modified:

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.

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

  • 2
1 Solution
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

   IF currentRowID IS NULL
      SET currentRowID = currentID
   IF currentID <> currentRowID
      SET currentID = currentRowID
   --Import data with currentID as the cardholder id val
mtuepkerAuthor Commented:
Good solution, thanks.
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:

        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.
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.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now