Solved

Replace null value in SSIS with value from previous row?

Posted on 2011-02-17
3
931 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:mtuepker
  • 2
3 Comments
 
LVL 28

Accepted Solution

by:
strickdd earned 500 total points
ID: 34925176
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
 
LVL 1

Author Closing Comment

by:mtuepker
ID: 34925763
Good solution, thanks.
0
 
LVL 1

Author Comment

by:mtuepker
ID: 34925775
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

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now