Converting FoxPro Date type to SQL Server 2005 DateTime using SSIS

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 dat开发者_高级运维eTime, it imports fine. But I do not wish to do this for a hundred tables.

---

You could make a small FoxPro program that will loop through your list of tables and write out a SQL INSERT INTO statement for each record to a .sql file which you could then open from or paste into SQL Management Studio and execute. You could then control the date formats that will work with SQL Server's date type fields.

Something similar could be done in c#.