Jul
19
2008

Conversion should be your own decision!

No, this isn't my first religious posting! We're talking about the SQL Server Import and Export Wizard, which was built upon Data Transformation Services (DTS) in SQL Server 2000, and is now built upon Integration Services in SQL Server 2005 and 2008.

Some users have complained that the new wizard is more troublesome than the DTS wizard. "I never used to get errors in the good old days of DTS." But life was not all good. You didn't get so many errors and warnings from the DTS wizard because it sometimes performed data conversions that you hadn't asked it to perform, including conversions that silently risked the integrity of your data due to potential truncation or conversion issues.

A conscious design decision was made for Integration Services: No more implicit data type conversion! We don't do anything to your data that you don't ask us to do.

And now - in the SQL Server 2008 Import and Export Wizard - we tell you more about the conversions that we think are necessary, and how likely they are to succeed, and we let you choose whether or not to go forward with the data type mappings that the Wizard thinks are best for you. This additional information may appear intimidating to the casual user, but in most cases (after reviewing warnings) it should be OK just to click "Next".

Here's the Review Data Type Mapping page that you now see after selecting source and destination. (For this demo, I have selected to copy all the tables from AdventureWorks to Excel 2003.)  At the top, you have a list of tables, and an icon that indicates whether the table includes any potentially troublesome conversions. At the bottom, you have a list of columns in the selected table. Here I have selected a column with a yellow Warning icon. (There wasn't a single red Error icon among all the tables that I selected, but it exists.) The selected line is warning us that it will be performing a widening conversion from SQL varchar to Unicode Jet longtext (memo).

If I double-click the selected column, I get a lot more information (though not all very exciting) about the planned data type conversion on the Column Conversion Details screen:

I can un-check the check box for a data type conversion, but in that case, the Wizard won't let me run the package immediately...I can only save it. The assumption is that I want to configure my own data type mappings, presumably by using the Data Conversion transformation. (I think "potential lost column conversion" was intended to be "column conversion with potential data loss"...we don't lose entire columns.)

Finally, the Complete the Wizard page shows a little more welcome information than previously about the selections that you've made:

 

Note that you could customize the data type mapping files in XML format that are provided with Integration Services. These files are found (for 2008 32-bit) in C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles. You could even create a new file for a combination of 2 databases that we haven't included. (We've published the schema...see my previous post.)

We hope that users will ultimately appreciate this level of information and control over what happens to their data.

And, after more emails and meetings than it took to put a man on the moon, we have managed to get the Import and Export Wizard back on the Start menu too!

-Doug

Comments (1) -

Ray D

Good, looks like this is MSFT's way of saying "we shouldn't done that in SQL 2005 but we don't want to admit that we were wrong so let's just silently fix it".


This was THE way to do things in SQL2000, to change the conversion to implicit without giving the users a way to still use explicit conversion has been a nightmare that one could only appreciate if you had to manually add all those conversions that were necessary. I agree, conversion should have been our choice, but we didn't have a choice in SQL2005 besides manually mapping all the packages that used to work in SQL2000.


All the interactions on Microsoft's forum digress into "well, that is the way we designed it", well, did you ASK your users what they wanted before you designed it? This seem to fall right in line with Microsoft dropping UAC on Vista users but mellowing it out in Windows 7.


Still, we are very thankful that this change is coming.


Regards


Ray