Recently I had a situation where my SSIS package was not able to read the data from Foxpro, error message is [Conversion failed because the data value overflowed the specified type.];
I spent long hours investigating finding actual record impacted, following is the take away for me!
• How to read data from Foxpro Database:
1. Add Foxpro Database as Linked Server to SQL Server: May be Linked Server is already added to the Server, please check!
EXEC master.dbo.sp_addlinkedserver @server = N'VFP', @srvproduct=N'VFP', @provider=N'VFPOLEDB.1', @datasrc=N'\\FOXPRODBLOCATION\FOLDERS\Data\Main' GO
Basic commands to get the data from Foxpro Database: (Map the location of Foxpro data files as a Drive on local Server, Z drive in below example)
USE z:\data\main\tablename.dbf shared
select * from tablename where colname = “ABCDABCDXYZ”
select * from tablename where col1 subeventid = “D201409301” and col2 = “ABC-DF2”
• Actual problem causing the error:
One of the analyst entered date as ‘0214-10-29’ instead of ‘2014-10-29’. This was a typo but it created all the problem!
Foxpro accepts it and considers as 29th Oct 14 but when the data is transferred to SQL Server it throws an error as SQL Server accepts minimum date ‘1753-01-01’
• Identifying the impacted record is the next most difficult part! It is all your analytical skills, I am not talking about it now! ALL THE BEST…