Getting First Line Only

  • 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?

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

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

  • Wait, I think I figured it out with the Lookup.

  • I'd be interested in hearing what you come up for a solution.

  • 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