1

I have SSIS Package which will load .EXT file into my Database table.

The package Flat File connection manager Editor properties are
   Format: Ragged Right
   Code Page: 1252 ANSI (Latin-I)
   Text Qualifier: <None>
    Header Row Delimiter: <LF>

While trying to preview the file before loading, i am able to see all the rows in columns and preview tab of Flat File connection manager Editor.

But in actual loading of the file, last record alone is not getting imported into table. It was loading fine and still it is processing the file on daily basis. Only for two days file, it was not imported last records. I am trying to find the root cause.

I suspected something wrong with the file, but i do not find any differences between the working and not-working version of files.

Please suggest us to resolve the same. Kindly let me know if any informations required.

sk7730
  • 636
  • 3
  • 9
  • 32
  • it is very obvious problem.. Edit your file and enter a `line feed` after last character in your file (simple enter a new line after last line). And try again. – Amnesh Goel Jun 08 '15 at 07:08
  • Look at your file in something like notepad++ and show special characters. You'll probably find that it's missing the record delimiter. – Nick.Mc Jun 08 '15 at 07:31
  • @Amnesh Goel, That was the first thing i was looking. When i was checking the file, line feed was there..I am not sure what else is causing issue. – sk7730 Jun 08 '15 at 08:51
  • Okay tell me what is your line terminator? and Did you check that you have the same line terminator at the end. `QuickFix` .. Just copy and paste last row and try it again. If your system read the last line only once, then it confirms that problem is with last line terminator. And if it happens then remove everything from very last line and it will work then. – Amnesh Goel Jun 08 '15 at 08:54
  • @Nick,McDermaid, I have opned file in Notepad++, CRLF special character was there..Something else causing the issue..Any other suggestions.. – sk7730 Jun 08 '15 at 08:57
  • Hi Amnesh, line terminator is CR LF...I have tried already Duplicating last row.. It works fine..The scenario here is if i duplicate last row, it is reading all the row... – sk7730 Jun 08 '15 at 08:59
  • Well now what you need to do it is delete your last row and till its first char.. don't touch your second last line.. it should work then.. and for more details open your input file in `Hex Editor` and see if you get OD OA at the end of last line.. it stands for CR LF in hex editior. – Amnesh Goel Jun 08 '15 at 09:14
  • Hi Amnesh, But my question is, if i look into the original file where last row is i readable mode, it has proper line feed (CR LF). Also i am able to preview all row records. Then why last row is not getting imported or why last row is not in readble mode. – sk7730 Jun 08 '15 at 09:42
  • Hi Amnesh, Any other thing that i am missing here. – sk7730 Jun 08 '15 at 10:21
  • There is something that you're mising - a more detailed explanation of how the data gets into your table. Are you just truncating the table and importing the data? If it's any more complicated than that then the issue might not be in the import step – Nick.Mc Jun 08 '15 at 23:20
  • @Nick, File is flat file and column is splitted by fixed width. Row Delimiter is CR LF. I have header, Trailer and actual records (Detail records) in the File. Header Records starts with H, Trailer records Starts with T and Details Records starts with D. Flat file configuration manager is done to map the Flat file. While making preview, i am able to see all header, trailer and detail records. This is just simple data import. Only a Trailer records are getting missed while data import. Before data import, i am truncating table. While running package, File source ignoring trailer record. – sk7730 Jun 09 '15 at 04:43
  • Please let me know if any other specific informations required. Thanks for your time – sk7730 Jun 09 '15 at 04:44
  • I didn't notice ragged right until now. My gut feel is this is the issue. Between the working and non working files are the last records different widths? Can you just confirm that there are no transformations in your data import, it's just a source feeding into a destination. – Nick.Mc Jun 09 '15 at 05:49
  • Also you need to check the line _before_ the missing line has the correct row terminator – Nick.Mc Jun 09 '15 at 06:01
  • @Nick, Thanks for your input..My first look to trobleshoot the issue was checking row terminator. It was there.. I am sure that row terminator is not an issue. Also working & non working file has same width..It is just source feeding into destination.. – sk7730 Jun 09 '15 at 07:27
  • OK since you can see it in in the preview but can't see it in the table, my next suggestion is to run the package interactively and using a dataviewer, observe the data going into the table. Then you can confirm the data is being picked up by the preview but not while the package is running. This may seem like a silly thing to do but going through the process might uncover more information – Nick.Mc Jun 09 '15 at 22:58
  • @Nick, I agree. I used Grid in the dataviewer to see the actual dataflow happened while running the package. I can't see the last row in the Grid. It is kind of strange issue :) – sk7730 Jun 10 '15 at 05:26
  • So... the record is seen in Preview but it is not seen at runtime. Are you certain you are pointing at the same file at preview time (the source file might be dynamically changed when running the package). I've seen times when Preview behaves differently to what actually gets imported. – Nick.Mc Jun 10 '15 at 07:02
  • Hi Nick,Yes i am pointing the same file for preview. In fact i am testing with only one file. I feel the same, preview behaves different. – sk7730 Jun 10 '15 at 09:39

3 Answers3

1

I ran into the same issue and did some research to find a solution that worked from me. Apparently the SSIS package had gone through a conversion from an earlier version at one point. When the conversion was done, the text qualifier property on the flat file connection was mangled. It had originally been <none>, but the conversion changed it to _x003C_none_x003E_. I opened the flat file connection manager and changed the text qualifier property on the general tab back to the proper value of <none>.

Credit goes to this thread for providing the answer.

Scott
  • 1,223
  • 2
  • 16
  • 35
0

I had a similar issue. My flat file didn't had any text qualifiers. When i added a text qualifier the package ran successfully. My guess is that the file is read as text and the CRLF is not recognized at the last line.

CBS
  • 50
  • 1
  • 6
0

If you can provide a sample of the data from the file

J Sidhu
  • 677
  • 1
  • 4
  • 19