Converting FoxPro Date type to SQL Server 2005 DateTime using SSIS
- by Avrom
Hi,
When using SSIS in SQL Server 2005 to convert a FoxPro database to a SQL Server database, if the given FoxPro database has a date type, SSIS assumes it is an integer type. The only way to convert it to a dateTime type is to manually select this type. However, that is not practical to do for over 100 tables.
Thus, I have been using a workaround in which I use DTS on SQL Server 2000 which converts it to a smallDateTime, then make a backup, then a restore into SQL Server 2005.
This workaround is starting to be a little annoying.
So, my question is: Is there anyway to setup SSIS so that whenever it encounters a date type to automatically assume it should be converted to a dateTime in SQL Server and apply that rule across the board?
Update
To be specific, if I use the import/export wizard in SSIS, I get the following error:
Column information for the source and the destination data could not be retrieved, or the data types of source columns were not mapped correctly to those available on the destination provider.
Followed by a list of a given table's date columns.
If I manually set each one to a dateTime, it imports fine.
But I do not wish to do this for a hundred tables.
