locked
not seeing end of record RRS feed

  • Question

  • I am trying to create an 11 character record in a flat file destination, where the first 9 characters are variable values and the last 2 characters are the literal "|Y". When I look at it for the preview in the flat file editor, the records are fine but when I execute the package it creates a single record where the 11 characters are being appended. The HeaderRowDelimiter is defined as carriage return, line feed and the columns are correctly delimited and mapped in the flat file editor.

    What am I doing wrong?

    Thursday, April 5, 2007 8:26 PM

Answers

  • Try a ragged right with 3 columns defined. The first should have a length of 9 characters, the second should have a length  of 2 characters, and the third should have a length of 0 characters, and have the row delimiter set to cr/lf (if that is your desired row delimiter).
    Monday, April 9, 2007 8:49 PM

All replies

  • How are the first 11 characters put together?  It sounds to me like you're building them in a data flow, which operates and sends data to the flat file destination on a row by row basis.  It doesn't aggregate the data first, before writing.
    Thursday, April 5, 2007 8:31 PM
  •  Dennis Cronin wrote:
    I am trying to create an 11 character record in a flat file destination, where the first 9 characters are variable values and the last 2 characters are the literal "|Y". When I look at it for the preview in the flat file editor, the records are fine but when I execute the package it creates a single record where the 11 characters are being appended. The HeaderRowDelimiter is defined as carriage return, line feed and the columns are correctly delimited and mapped in the flat file editor.

    What am I doing wrong?

     

    I don't really understand the problem but be aware that SSIS does not do a search for row delimiters and then work backwards. Instead, it parses the file as a continuous input stream looking for the expected column delimiters. If it doesnt find the next column delimiter then you may have problems.

     

    If you show us a sample of the file and then what SSIS actually gives you in the pipeline it would help a great deal.

     

    -Jamie

     

    Friday, April 6, 2007 4:40 AM
  • I am using data flow to create the file. It works against a sql server table filtering for rows where an amount fireld exceeds 9999 for the variable portion and a derived column with the fixed literal '|Y'.

    I'm expecting:

    073902108|Y
    101137107|Y
    704549104|Y

    but I'm getting

    073902108|Y101137107|Y704549104|Y035229103|Y101121101|Y172967101|Y12673P105|Y205887102|Y14149Y108|Y171232101|YG24182100|Y12497T101|Y200519106|Y17163B102|Y124857202|Y20449H109|Y172737108|Y191219104|Y143658300|Y184502102|Y151020104|Y222372104|Y137801106|Y165167107|Y125509109|Y171779309|Y172062101|Y125581108|Y194162103|Y189054109|Y200340107|Y20030N101|Y167760107|Y231021106|Y137225108|Y20854P109|Y14040H105|Y189754104|Y774341101|Y885535104|Y20825C104|Y218725109|Y134429109|Y89674K103|Y896047107|Y89141B108|Y74144T108|Y792860108|Y902494103|Y901103101|Y88343A108|Y887317105|Y882508104|Y883203101|Y902124106|Y90262T308|Y909214108|Y91324P102|Y91529Y106|Y907818108|Y911312106|Y902973304|Y902911106|Y92220P105|Y918204108|Y92553P201|Y91913Y100|Y929042109|Y92343E102|Y928551100|Y931422109|Y941848103|Y929903102|Y950590109|Y949746101|YG95089101|Y963320106|Y97381W104|Y94973V107|Y939322103|Y969457100|Y94106L109|Y942683103|Y961840105|Y959802109|Y982526105|Y982526204|Y983024100|Y98310W108|YG98255105|Y30231G102|Y984121103|Y98385X106|Y984332106|Y988498101|Y989701107|Y98956P102|Y74838C106|Y47102X105|Y480074103|Y46625H100|Y655664100|Y487836108|Y48666K109|Y486665102|Y493267108|Y50075N104|Y495582108|Y49446R109|Y494368103|Y49455P101|Y501044101|Y500255104|Y524660107|Y524908100|Y526057104|Y50540R409|Y539320101|Y502424104|Y532457108|Y524901105|Y539830109|Y534187109|Y548661107|Y50186V102|Y502161102|Y532716107|Y540424108|Y844741108|Y52078P102|Y529771107|Y552078107|Y571903202|Y574599106|Y577081102|Y55262C100|Y58155Q103|Y615369105|Y589433101|Y585055106|Y58551A108|Y590188108|Y59156R108|Y55305B101|Y580645109|Y58405U102|Y571834100|Y601073109|Y579780206|Y599902103|Y571748102|Y589975101|Y608554101|Y620076109|Y565849106|Y617446448|Y594918104|Y55261F104|Y552848103|Y595112103|Y626717102|Y628530107|YG6359F103|Y635405103|Y62886E108|YG65422100|Y654106103|Y666807102|Y637071101|Y670006105|Y655844108|Y637640103|Y64120L104|Y665859104|Y670002104|Y67066G104|Y670008101|Y651229106|Y65248E104|Y292052107|Y650111107|Y96808B107|Y676220106|Y681919106|Y67622P101|Y68389X105|Y67104A101|Y674599105|Y704326107|Y713409100|Y724479100|Y729251108|Y703395103|Y713448108|Y717081103|Y74251V102|Y742718109|Y743315103|Y701094104|Y693320202|Y745867101|Y714046109|Y743410102|Y696429307|Y         |Y69344F106|Y693475105|Y744320102|Y74460D109|Y70322A101|Y749121109|Y747525103|Y747277101|Y783549108|Y761713106|Y779382100|Y7591EP100|Y770323103|Y756577102|YG90078109|Y731572103|Y773903109|Y257867101|Y755111507|Y449934108|Y852061100|Y786429100|Y800907107|Y808513105|Y847560109|Y848420105|Y78645R107|Y806605101|Y824348106|Y832110100|YG04074103|Y806857108|Y803111103|Y78442P106|Y834182107|Y833034101|Y80004C101|Y87161C105|Y845905108|Y828806109|Y855030102|Y811054204|Y85857R105|Y867914103|Y790849103|Y857477103|Y21036P108|Y86764P109|Y866810104|Y81760N109|Y868536103|Y854616109|Y786514208|Y863667101|Y871503108|Y871829107|Y00206R102|Y60871R209|Y872375100|Y879131100|Y880770102|Y880779103|Y87612E106|Y88033G100|Y886547108|Y872540109|Y879664100|Y891027104|Y883556102|Y87588Q109|Y205638109|Y205363104|Y17275R102|Y126408103|Y156700106|Y192446102|Y152312104|Y178871109|Y212485106|Y222862104|Y126650100|Y166764100|Y17453B101|Y256069105|Y251591103|Y254067101|Y244199105|Y24702R101|Y256669102|Y74834L100|Y23331A109|Y235851102|Y452439201|Y260561105|Y260003108|Y237194105|Y248239105|Y25459L106|Y25179M103|Y278642103|Y285661104|Y28138P100|Y294429105|Y277461109|Y518439104|Y268648102|Y291011104|Y294703103|Y26875P101|Y28336L109|Y29078E105|Y29476L107|Y285512109|Y296643109|Y302182100|Y26874Q100|Y269246104|Y278058102|Y345370860|Y31410H101|Y319963104|Y307000109|Y31428X106|Y320517105|Y314211103|Y31620M106|Y316773100|Y343412102|Y313586109|Y349631101|Y00846U101|Y03073E105|Y038020103|Y023139108|Y002824100|YG0070K103|Y054540109|Y008190100|Y00724F101|Y000886309|Y032654105|Y039483102|Y053015103|Y052769106|Y00817Y108|Y001055102|Y018490102|Y03748R101|Y020002101|Y038222105|Y007903107|Y031162100|Y031652100|Y03076C106|Y05329W102|Y002896207|Y037389103|Y037411105|Y032511107|Y007768104|Y029712106|Y039583109|Y00253U107|Y020039103|Y021489109|Y053499109|Y053484101|Y05367L802|Y054303102|Y053611109|Y019589308|Y053332102|Y060505104|Y071813109|Y054937107|Y086516101|Y117043109|Y067383109|Y091797100|Y075887109|Y354613101|Y115637209|Y057224107|Y089302103|Y055482103|Y064057102|Y055921100|Y110122108|Y12189T104|Y071707103|Y11133T103|Y111621108|Y111320107|Y068306109|Y313400301|Y345838106|Y364730101|Y369550108|Y369604103|Y374163103|Y375558103|Y370334104|Y219350105|Y37245M207|Y37247D106|Y38259P508|Y372460105|Y382388106|Y38141G104|Y382550101|Y384802104|Y75605E100|Y406216101|Y413086109|Y418056107|Y446150104|Y443683107|Y564055101|Y42809H107|Y413619107|Y416515104|Y432848109|Y413216300|Y423074103|Y412822108|Y85590A401|Y414549105|Y093671105|Y441060100|Y44107P104|Y427866108|Y444859102|Y450909106|Y50212A106|Y459902102|Y45666Q102|Y458140100|Y460690100|YG4776G101|Y46069S109|Y450911102|Y452308109|Y         |Y466313103|Y478366107|Y708160106|Y46612J507|Y48203R104|Y

    The connection is described as fixed width. When I preview it in the advanced editor, the file looks correct. When I look at the columns option, the ninth position has a black line and the 11th has a red one. But the output is not correct.




    Monday, April 9, 2007 3:53 PM
  • Your example looks correct for the "fixed width" format. 

    I think what you are looking for is the "ragged right" format which places a new line character at the end of every row. 
    Monday, April 9, 2007 6:57 PM
  • That doesn't work. When I try it, the preview shows 3 columns, not 2 and the third column wraps to the end. To me, that preview makes sense. As I understand it, ragged means variable length for each row's final column. That's not the kind of file I'm trying to create.

    As I mentioned above, I'm trying to write each row as a 2 column, fixed length, 11 character record, 9 cahracters for the first column, 2 for the second.
    Monday, April 9, 2007 8:02 PM
  •  Dennis Cronin wrote:
    That doesn't work. When I try it, the preview shows 3 columns, not 2 and the third column wraps to the end. To me, that preview makes sense. As I understand it, ragged means variable length for each row's final column. That's not the kind of file I'm trying to create.

    As I mentioned above, I'm trying to write each row as a 2 column, fixed length, 11 character record, 9 cahracters for the first column, 2 for the second.


    You want ragged right if you want to be able to view it in notepad or something.

    http://sqljunkies.com/WebLog/knight_reign/archive/2005/01/27/6951.aspx
    Monday, April 9, 2007 8:07 PM
  • Try a ragged right with 3 columns defined. The first should have a length of 9 characters, the second should have a length  of 2 characters, and the third should have a length of 0 characters, and have the row delimiter set to cr/lf (if that is your desired row delimiter).
    Monday, April 9, 2007 8:49 PM