April 12, 2011 at 4:12 pm
I have to import another text file. The data looks something like this:
"P R O P R I E T A R Y "
"BRANCH","CUST-NBR","SUFFIX","CUST-NAME","ADDR1","ADDR2","CITY","STATE","ZIPCODE","COUNTRY","PHONE","SHIP-TO-STATE","SHIP-TO-ZIP","PART","PART-DESC",QTY-SHIP,PRICE-SHIP,"SALE-DATE","VEND-PART-NBR","INVOICE-NBR"
"10","001629","200","XXXXXXXXXX YYYYYYYYYY GROUP ","XXXXXXXXXX YYYYYYYYYY GROUP ","RANDOM COMPANY INC ","SAN DIEGO ","CA","12345","US","12345","NM","87106","Q77118 ","RANDOM PRODUCT", 2, 17.50,"02/19/11","K00000US ","5434321"
I just want to bring in the first row of data (the third row of the file) and get the SALE-DATE date column. I don't want to have to load the entire file into memory. I'm guessing I could use the script task to do this, but I'm not sure how. Does anyone have any advice?
April 13, 2011 at 10:26 am
In the Flat File Connection manager you can tell it to skip the first row ("Proprietary") by setting Header Rows to Skip to 1 and that the second row is column names by checking the Column names in the first data row box.
Yes this would load the whole file into memory, but are there many other rows in the file? I think you'd end up loading the whole file into memory even with a script.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 13, 2011 at 3:00 pm
Okay, this makes sense. I now have the flat file source bringing in all the rows. I tried to use a lookup based on SELECT DISTINCT SALE_DATE FROM [DESTINATION_TABLE]. But how do I insert only those rows from the source table where SOURCE.SALE_DATE <> DESTINATION.SALE_DATE? I'm not having any luck with the Lookup transformation or the Merge Join transformation.
April 13, 2011 at 4:31 pm
Wait, I think I figured it out with the Lookup.
April 13, 2011 at 4:36 pm
I'd be interested in hearing what you come up for a solution.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 13, 2011 at 4:48 pm
I kept the lookup based on the SELECT statement I mentioned earlier. SALE_DATE (the only column in the lookup) became the Lookup Column. The Lookup Operation was <add as new column>. The Output Alias was something I called "dSALE_DATE."
Then I connected the Lookup Error Output to the OLE DB Destination.
In the OLE DB Destination, I mapped the columns from the Flat File Source to the Destination Table. That way I could use the original SALE-DATE from the source.
I hope this works and I hope it helps.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply