2

I have a fixed width text file. I have created an SSIS package that imports the file into a table in a SQL 2014 server.

When I open the file (with notepad++) i see that it contains 259,735 records.

When I run the SSIS package it only imports 216,610 records. The package finishes and gives a Success output message but I am wondering why it is not importing all the records.

The package was previously working and it would import the 259,735 records but I made a change to the file layout and underlying tables and now I only get the 216,610 loaded.

I am not sure what is the best way to trouble shoot this and can't think of anything that would cause this issue other then maybe a line not being terminated correctly but then I would have probably got a truncation error if that were the case.

my row delimiter is CRLF and it appears in the same position (400) on every line of the file and as I mentioned yesterday I was able to import all 259k records with this same package so I am pretty sure that the file is properly formatted and is not the issue.

Does anyone have any ideas on what can be causing this issue or how I should go about troubleshooting further?

4
  • did you use the same file yesterday as you are using today?
    – Hannah Vernon
    Jun 9, 2016 at 17:20
  • Do you have your settings set up in a way that you eliminate duplicates? You could also have ignore failure set on your destination. Either of these could be your issue.
    – Zane
    Jun 9, 2016 at 17:25
  • @MaxVernon Yes, same file.
    – Juan Velez
    Jun 9, 2016 at 17:48
  • @Zane No duplicate removal in that step. I checked but no failures were set to ignore.
    – Juan Velez
    Jun 9, 2016 at 17:49

1 Answer 1

2

Yesterday when I was working with the file I went to the preview tab and I set the Data rows to skip to around 45k so I could preview what some of those rows were looking like.

I was under the impression that the Data rows to skip value would only affect the preview and not actually be used at run time.

Setting the Data rows to skip back to 0 made it so all the records were once again being imported into the table

Some of the other things I did while trouble shooting which might help someone else who is having a similar issue but due to a different reason can be found here:

ssis-text-file-import-skips-some-rows

enter image description here

Not the answer you're looking for? Browse other questions tagged or ask your own question.