[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Replace null value in SSIS with value from previous row?

Posted on 2011-02-17
3
Medium Priority
?
1,009 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 28

Accepted Solution

by:
strickdd earned 1000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

650 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