Import and Export Wizard in SSIS with SQL Server Data

The SSIS Tools

As with any Microsoft product, SQL Server ships with a myriad of wizards and tools to make your life easier and reduce your time to market. In this chapter, you will learn about some of the tools of the trade that are available to you and how to create your first basic package. These wizards make transporting data and deploying your packages much easier and can save you hours of work in the long run, but they’re only a starting point in most cases. In the first part of this chapter, you’ll look at the Import and Export Wizard, which enables you to create a package for importing or exporting data quickly with minimal transformations. As a matter of fact, you may run this tool in your day-to-day work without even knowing that SSIS is the back end for the wizard. The latter part of this chapter explores other, more powerful, tools that are available to you, such as SQL Server Data Tools (SSDT). By the time this chapter is complete, you will have created your first SSIS package.

Import And Export Wizard

The Import and Export Wizard is the easiest method to move data from sources like Oracle, DB2, SQL Server, Excel, and text files to nearly any destination, and it is available across all versions of SQL Server — even those that don’t include SSIS. This wizard uses SSIS as a framework and can optionally save a package as its output prior to executing. The package it produces may not be the most elegant, but it can eliminate a lot of tedious package development work and it provides the building blocks that are necessary for building the remainder of the package. Oftentimes as an SSIS developer, you’ll want to relegate the grunt work and heavy lifting to the wizard when you want to just move data for a onetime load, and then do the more complex coding yourself.

As with any of the SSIS tools, there are numerous ways to open the tool. To open the Import and Export Wizard, right-click the database you want to import data from or export data to in SQL Server Management Studio and select Tasks ⇒ Import Data (or Export Data based on what task you’re performing). You can also open the wizard by right-clicking the SSIS Packages folder in SSDT and selecting SSIS Import and Export Wizard. Another common way to open it is from the Start menu under SQL Server 2014, where it’s called Import and Export Data. The last way to open the wizard is by typing dtswizard.exe at the command line or Run prompt. Regardless of whether you need to import or export data, the first few screens in the wizard look very similar.

When the wizard appears, you’ll see the typical Microsoft wizard welcome screen. Click Next to begin specifying the source connection. If you had opened the wizard from Management Studio by selecting Export Data, this screen is pre-populated. In this screen, you specify where your data is coming from in the Source dropdown box. Once you select the source, the rest of the options on the dialog may vary based on the type of connection. The default source is .Net Framework Data Provider for Odbc. Out of the box, you have ODBC and OLE DB sources that connect to SQL Server, Oracle, and Access. You can also use text files, Excel files, and XML files. Traditionally, the SQL Native Client is the provider used in SSIS because it gives additional functionality during design time. Change the data source to use the SQL Server Native Client 11.0 provider.

For SQL Server, you must enter the server name, as well as the user name and password you’d like 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 to which you want to connect. For most of the examples in this tutorial, you’ll use the AdventureWorksDW database or a variation of that DW database, shown in below diagram. This database can be downloaded at WWW.WROX.COM.

NOTE: Additional sources such as Sybase and DB2 are also available if you install the vendor’s OLE DB providers. You can download the OLE DB provider for DB2 free if you’re using Enterprise Edition by going to the SQL Server Feature Pack on the Microsoft website. (As of this writing, the SQL Server 2014 Feature Pack has not be released. However, the SQL 2012 Feature Pack will work for 2014 as well.)

Frequently Asked SSIS Interview Questions & Answers

 

NOTE: In 2011, Microsoft released information regarding the appropriate provider types to use for new development. It was recommended that any new development should be done with ODBC providers rather than OLE DB. This rule should only be followed for homegrown applications for now. SSIS developers should continue using OLE DB because ODBC does not have full feature parity yet to complete some ordinary tasks.

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 identical to those for the previous screen with the exception of the database. Change the Destination provider to SQL Server Native Client 11.0, then select TempDB from the Database dropdown. This will create and load the tables into a temporary space, which will disappear once you restart your instance of SQL Server. Click Next again to be taken to the Specify Table Copy or Query screen (see below diagram). Here, if you select “Copy data from one or more tables or views,” you can simply check the tables you want. If you select “Write a query to specify the data to transfer,” you can write an ad hoc query (after clicking Next) that defines where to select the data from, or what stored procedure to use to retrieve your data.

For the purpose of this example, select “Copy data from one or more tables or views” and click Next. This takes you to the Select Source Tables and Views screen, where you can check the tables or views that you want to transfer to the destination (see below diagram). For this tutorial, check a couple of tables such as FactResellerSales and FactInternetSales in the AdventureWorksDW database.

If you wish, you can click the Edit buttons to access the Column Mappings dialog for each table (see below diagram). Here you can change the mapping between each source and destination column. For example, if you want the ProductKey column to go to the ProductKey2 column on the destination, simply select the Destination cell for the ProductKey column and point it to the new column, or select to ignore the column altogether.

Note that because you’re moving the data to a new database that doesn’t already contain the FactInternetSales table, the “Create destination table” option is one of the few options enabled by default. This will create the table on the destination before populating it with data from the source. If the table already existed, the data will append existing records but you could specify that all rows in the destination table should be deleted before populating it.

Finally, you can check the “Enable identity insert” option if the table into which you are moving data has an identity column. If the table does have an identity column, the wizard automatically enables this option. If you don’t have the option enabled and you try to move data into an identity column, the wizard will fail to execute.

For the purpose of this example, don’t change any of the settings in this screen. Click OK to apply the settings from the Column Mappings dialog and then click Next to proceed.

If no errors are found, you are taken to the Save and Run Package screen (below diagram). Here you can specify whether you want the package to execute only once, or whether you’d like to save the package for later use. As shown earlier, it isn’t necessary to execute the package here. You can uncheck Run Immediately and just save the package for later modification and execution. For this example, check the options for Run Immediately, Save SSIS Package, and File System. This collection of options will execute the package and save it as a .dtsx file to your computer. You learn more about where to save your SSIS packages later in this chapter. Note that if you save the package to SQL Server or to the file system, you’re saving the package with the Package Deployment Model. We’ll discuss more about the package deployment model in Chapter 22.

In this screen, you’re also asked how you wish to protect the sensitive data in your package. SSIS packages are essentially large XML files behind the scenes, and encryption of sensitive data, such as passwords, is critical to ensuring that no one sees that information by opening the XML manually. Again, you learn more about this later in this chapter, so for now just change the Package Protection Level property to “Encrypt sensitive data with password” to protect your sensitive data with a password, and give the dialog a password (as shown in the above diagram).

You are then taken to the Save SSIS Package screen, where you can type the name of the package and the location to which you want to save it (see below diagram).

Click Next and confirm what tasks you wish the wizard to perform. The package will then execute when you click Finish, and you’ll see the page shown in the below diagram. Any errors are displayed in the Message column. You can also see how many rows were copied over in this column, and you can double-click an entry that failed to see why, in the event that there are errors during execution.

After the wizard executes, the package can be found in the location that you have specified, but the default is the My Documents directory. You can open the package that executed in SSDT by creating a project in SSDT and copying and pasting the package into the project or by right-clicking on Packages and selecting Add Existing Package.

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator