Import from a textwhile and different db tables.

Question

Import from a textwhile and different db tables.

holtis

SSC Veteran

Points: 208
More actions
November 21, 2007 at 12:38 am

#377097

Hello.
Im supposed to import data from a text file that contain some basic transction information.
The data will be imported to our DW, the destination table within the DW contains more fields than the textfile which means that I have find the information in some other other table and insert it together with the data from the textfile.
Example:
My Fact table look pretty much like this:
TransactionNr
SalesDate
ItemNr
ProductGroup
BarCode
AmountSold
SalesPrice
PurchPrice
My textile looks like this
TransactionNr
SalesDate
ItemNr
SalesPrice
AmountSold
As you can see there are more fields in the fact table than in the textfile, however all field has to be filled in in the fact tables.
I would like to get the missing data while importing the data from the textfile, maybe this could be done via some kind of function or so.
For an exapmle, in order to find out what PurchPrice an Item(in the textfile) has I could open the PurchPrice Table where that price is stored and find the correct price by identifying it
via the ItemNr. This price should then be inserted in to the DWH togather with the rest of the data.
How could this be done in the best way using SISS or is there even a better way?
Thanks for any ideas.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Chris Becker SSCrazy Points: 2645 More actions · Answer 1

You can use the derived column transformation to add the columns to your text file upload downstream, then use the lookup trans to populate your new columns...

Regards,

ChrisB MCDBA

MSSQLConsulting.com

Chris Becker bcsdata.net

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 2

I'm not sure if this is the same interpretation that Chris offered, but, #1... I don't ever load data from a text file directly into the target table. I always use a "staging table" where I can "glean" the data including adding data in "extra columns" if need be, either through a join to the staging table or by adding the data to the staging table.

It also allows me to evaluate which rows I actually want to insert before I insert them... eliminates the possiblity of a rollback, that way, because I only send the data that I know will not cause an error.

Just an FYI... I usually use Bulk Insert to load the staging table... the current "best" I've see it do is to load 5 million rows, 25 columns wide, in one minute and 5 seconds... that pretty damned fast.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)