<p>I have some SQL Server DTS packages that import data from a FoxPro database.  This was working fine until recently.  Now the script that imports data from one of the FoxPro tables bombs out about 470,000 records into the import.  I'm just pulling the data into a table with nullable varchar fields so I'm thinking it must be a weird/corrupt data problem.</p>

<p>What tools would you use to track down a problem like this?</p>

<p>FYI, this is the error I'm getting:</p>

<blockquote>
  <p>Data for source column 1 ('field1') is not available.  Your provider may require that all Blob columns be rightmost in the source result set.</p>
</blockquote>

<p>There should not be any blob columns in this table.</p>

<hr>

<p>Thanks for the suggestions.  I don't know if it a corruption problem for sure.  I just started downloading FoxPro from my MSDN Subscription, so I'll see if I can open the table.  SSRS opens the table, it just chokes before running through all the records.  I'm just trying to figure out which record it's having a problem with.</p>

[{'answer_id': 10310, 'author': 'Stu', 'author_id': 414, 'author_profile': 'https://Stackoverflow.com/users/414', 'pm_score': 1, 'selected': False, 'text': '<p>Have you tried writing a small program that just copies the existing data to a new table?</p>\n\n<p>Also,</p>\n\n<p><a href="http://fox.wikis.com/wc.dll?Wiki~TableCorruptionRepairTools~VFP" rel="nofollow noreferrer">http://fox.wikis.com/wc.dll?Wiki~TableCorruptionRepairTools~VFP</a></p>\n'}, {'answer_id': 10340, 'author': 'PabloG', 'author_id': 394, 'author_profile': 'https://Stackoverflow.com/users/394', 'pm_score': 2, 'selected': False, 'text': '<p><a href="http://www.cmstory.com/_cm/CMrepair.zip" rel="nofollow noreferrer">Cmrepair</a> is an excellent freeware utility to repair corrupted .DBF files.</p>\n'}, {'answer_id': 10551, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 1, 'selected': False, 'text': '<p>My company uses Foxpro to store quite a bit of data... In my experience, data corruption is very obvious, with the table failing to open in the first place.   Do you have a copy of foxpro to open the table with?   </p>\n'}, {'answer_id': 10934, 'author': 'PabloG', 'author_id': 394, 'author_profile': 'https://Stackoverflow.com/users/394', 'pm_score': 0, 'selected': False, 'text': '<p>@Lance:</p>\n\n<p>if you have access to Visual FoxPro command line window, type:</p>\n\n<pre><code>SET TABLEVALIDATE 11\nUSE "YourTable" EXCLUSIVE    &amp;&amp; If the table is damaged VFP must display an error here\nPACK    &amp;&amp; To reindex the table and deleted "marked" records\nPACK MEMO    &amp;&amp; If you have memo fields\n</code></pre>\n\n<p>After doing that, the structure of the table must ve valid, if you want to see fields with invalid data, you can try:</p>\n\n<pre><code>SELECT * FROM YourTable WHERE EMPTY(YourField)   &amp;&amp; All records with YourField empty\nSELECT * FROM YourTable WHERE LEN(YourMemoField) &gt; 200   &amp;&amp; All records with a long memo field, there can be corrupted data\n</code></pre>\n\n<p>etc. </p>\n'}, {'answer_id': 11206, 'author': 'Kit Roed', 'author_id': 1339, 'author_profile': 'https://Stackoverflow.com/users/1339', 'pm_score': 1, 'selected': False, 'text': "<p>At 470,000 records you might want to check to see if you're approaching the 2 gigabyte limit on FoxPro table size.  As I understand it, the records can still be there, but become inaccessible after the 2 gig point.</p>\n"}, {'answer_id': 13959757, 'author': 'Sher Shah', 'author_id': 1827536, 'author_profile': 'https://Stackoverflow.com/users/1827536', 'pm_score': 0, 'selected': False, 'text': '<p>Use Repair Databases from my site (www.shershahsoft.com) for FREE (and Will always be FREE).</p>\n\n<p>I have designed this program to repair damaged Foxpro/FoxBase/Dbase files. The program is very quick. It will repair 1 GB table in less than a minute.</p>\n\n<p>You can asign files, and folders to the program. As you start the program it will mark all the corrupted files, and by clicking Repair or Check and Repair button, it will repair all the corrupted files. Moreover, it will create a folders "CorruptData" in the folders where the actual data exist,  and will keep copies of the corrupt files there.</p>\n\n<p>One thing to keep in mind, always run Windows CheckDsk on the drives where you store the files. Cause, when records are being copied to a table and power failure occures, there exists lost clusters which Windows converts to files during CheckDsk. After that, the RepairDatabases will do the job for you.</p>\n\n<p>I have used many paid and free programs which repair tables, but all such programs leave extra records in the tables with embiguit characters (and they are time consuming too). The programer needs to find and delete such records manually. But Repair Databases actually recovers the original records, you need no further action. The only action you need is reindexing your files.</p>\n\n<p>In the repair process some times File Open Dialog appears which asks to locate the compact index file for a table with indeces. You may click cancel the dialog at that point, the table will be repaired, however, you will need to reindex the file later. (this dialog may appear several times depending upon the number of corrupted indeces.)</p>\n'}]
['https://Stackoverflow.com/questions/10303', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/571/']