The Import and Export Wizard is the easiest method to move data from sources like Excel, Oracle, DB2, SQL Server, and text files to nearly any destination. This wizard uses SSIS as a framework and can optionally save a package as its output prior to executing. The package it produces will not be the most elegant, but it can take a lot of the grunt work out of package development and provide the building blocks that are necessary for you to build the remainder of the package. Oftentimes as an SSIS developer, you’ll want to relegate the grunt work and heavy lifting to the wizard and do the more complex coding yourself. The wizard does no transformations or cleansing, but instead only moves data from point A to point B.
As with most SQL Server wizards, you have numerous ways to open the tool:
Regardless of whether you need to import or export the data, the first few screens in the wizard look very similar.
Once the wizard comes up, you see the typical Microsoft wizard welcome screen. Click Next to begin specifying the source connection. If you opened the wizard from Management Studio by selecting Export Data, this screen is prepopulated. In this screen, you specify where your data is coming from in the Source drop-down box. Once you select the source, the rest of the options on the dialog box may vary based on the type of connection. The default source is SQL Native Client, and it looks like Figure 1-1. You have OLE DB Sources like SQL Server, Oracle, and Access available out of the box. You can also use text files and Excel files. After selecting the source, you have to fill in the provider-specific information.
For SQL Server, you must enter the server name (localhost means go to your local machine’s SQL Server instance, if applicable) and the username and password you want to use. If you’re going to connect with your Windows account, simply select Use Windows Authentication. Windows Authentication will pass your Windows local or domain credentials into the data source. Lastly, choose a database that you’d like to connect to. For most of the examples in this book, you use the AdventureWorks2012 database. You can see Lesson 3 of this book for more information on installing this sample database.
After you click Next, you are taken to the next screen in the wizard, where you specify the destination for your data. The properties for this screen are exactly identical to those for the previous screen with the exception of the database. On the next screen, if you select the Copy data from one or more tables or views option, you can simply check the tables you want. If you select the Write a query to specify the data to transfer option, you can write an ad hoc query (after clicking Next) addressing where to select the data from or what stored procedure to use to retrieve your data.
The next screen enables you to select the table or tables you want to copy over and which table names you want them to be transferred to. If you want, you can click the Edit button to go to the Column Mappings dialog box (shown in Figure 1-2) for each table. Here you can change the mapping between each source and destination column. For example, if you want the DepartmentID column to go to the DepartmentID2 column on the destination, simply select the Destination drop-down box for the DepartmentID column and point it to the new column, or choose <ignore > to ignore the column altogether. By checking the Enabled identity insert box, you allow the wizard to insert into a column that has an identity (or autonumber) value assigned. If the data types don’t match between the source and destination columns, the wizard will add the necessary components to convert the data to a proper data type if possible.
The next screen enables you to save the package or just choose to run it immediately. You can uncheck Execute Immediately to just save the package for later modification and execution. You can open the package that executed in SQL Server Data Tools (SSDT) if you’d like. You do this by creating a project in SSDT and adding the package to the project. You cannot edit the package without an SSDT project to contain the package. We discuss how to create a project in Lesson 4 later in this book. The final screen executes the process and shows you the output log.
In this Try It, you learn how to quickly load a flat file into a database using the Import and Export Wizard. After this lesson, you’ll have a clear understanding of how the Import and Export Wizard is the easiest way to load data into almost any destination and how it is accessed from Management Studio or SSDT.
You can find the file associated with Lesson 1 on the companion website for this book at www.wrox.com.
Load the ZipCodeExtract.csv file (which you can download at this book’s website at www.wrox.com) into any database of your choosing. We are using the AdventureWorks2012 database as our target, but that’s not a dependency. Note: The file’s first row holds the column names.