THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Eric Johnson

My Thoughts on SQL Server and Database Related Technologies.

SQL Server 2008’s New Import and Export Wizard

I have written a lot lately about SSIS package development, and that doesn’t apply to everyone that works with SQL Server 2008. So you might be asking, what’s does SSIS have to offer me, the production support DBA? Well, I am glad you asked. In SQL Server 2008, the Import and Export Wizard is built on the SSIS framework. In fact, at the end of the Wizard, you can save your settings into an SSIS package. If you have used the Wizard before, it will feel much the same, choose a source, choose a destination, specify your data, and go. In fact, if you don’t look close you may miss some of the new features.

First, when you choose a destination or source, you will see the .NET Framework Data Providers in the drop downs. This means you can run the import and export wizard directly against Oracle as well as ODBC data sources.

Next, there is a whole set of mapping files that define how data from one data source should map into destinations that are of a different type, Oracle to SQL Server or SQL Server to DB2 for example. These XML files can be found here (Assuming you installed to C:\): C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles. During the wizard, you can accept the decisions made by using these files or you can edit the data type mappings. For example, below is a snippet of the OracleToMSSql file which maps Oracle data to SQL Server data:

<dtm:DataTypeMapping >
        <dtm:SourceDataType>
            <dtm:DataTypeName>NUMBER</dtm:DataTypeName>
        </dtm:SourceDataType>
        <dtm:DestinationDataType>
            <dtm:NumericType>
                <dtm:DataTypeName>NUMERIC</dtm:DataTypeName>
                <dtm:UseSourcePrecision/>
                <dtm:UseSourceScale/>
            </dtm:NumericType>
        </dtm:DestinationDataType>
    </dtm:DataTypeMapping>   

As you can see, NUMBER in Oracle maps to NUMERIC in SQL Server. Lastly, the wizard has been tuned to scale better. For example, if you import a large number of tables, they will be spilt across multiple data flows to reduce concurrent processing.

In the past, the import and export wizard has been a bit of a last resort for DBAs, but now I think its time to give this old dog a second chance.

Published Monday, October 19, 2009 10:58 AM by ejohnson2010
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About ejohnson2010

Eric (Microsoft SQL MVP) is the co-founder of Consortio Services, and the primary Database Technologies Consultant. His background in Information Technology is diverse, ranging from operating systems and hardware to specialized applications and development. He has even done his fair share of work on networks. Since IT is really just a way to support business processes, he has also acquired his MBA. All in all, he has 10 years of experience with IT, a great amount of which has been working with Microsoft SQL Server. Eric has managed and designed databases of all shapes and sizes. He has delivered numerous SQL Server training classes and webcasts as well as presentations at national technology conferences. Most recently, he presented at TechMentor on SQL Server 2005 Replication, Reporting Services, and Integration Services. In addition, he is active in the local SQL Server Community, serving as the President of the Colorado Springs SQL Server Users Group.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement