I see that it has been over a month since your post and see no replies to this. Have you had any success in this area? I ask because I am about to dive head first into altering the MappingFiles and the DtwTypeConversion.xml file for SSIS.

I have come across only a few articles on this topic that are "weedy enough"

and am including below the two that have been most informative for me, (so far at least):

http://www.simple-talk.com/sql/ssis/sql-server-2008-ssis-cribsheet/

(look under "Data Integration" ...especially look immediately above "Date/Time Data Types"... for the DtwTypeConversion.xml info.)

and

http://support.microsoft.com/kb/2152728

Both of these refer to the Wizard picking up the data types for change options during a Wizard driven SSIS import/export.

I've only just begun looking into this myself so please forgive me if I am under-informed or missing the point completely. (and please do let me know if that is the case!)

Currently, I find the data in the two above articles informative and helpful, but perhaps not fully applicable when applied across the board for the various levels of the BI Stack.

What I'm really wanting is to alter the "look here first" capability of SSIS internals to alleviate the constant "Gotta Convert" pick and choose that takes up so much time for one-time data grabs AND through the BIDS environment too. For me, it seems that 9.9 times out of 10, I am constantly having to transform because the data types 'cannot be converted' seamlessly, or are being converted to a data type I do not want. I would love to create a set of mapping files that would bring up my own preferred mapping scenarios as the top-choice default so that a "no explicit conversion needed" data type mapping is the true default both environments, with or without the Wizard. (but I'd certainly settle for just improving the "Wizard experience" if possible!)

If anyone else out there is having grins and giggles with this, I'd love to know about it!

Thanks!

~ MJ Mathias