SQL 301 — SSIS Import And Export Wizard

OK, let’s dive right into the Import and Export Wizard.  If you’re on Vista or Windows 7, hit start, then begin typing “Import and Export Data”  It should find it for you pretty quickly.  If you’re not on one of those two, look for your SQL 2005 or 2008 folder, and search in those folders for the Import and Export Wizard, if you’re on a 64-bit OS, you’ll have two options, one 32-bit, the other 64 bit.  I’m using the Import and Export Wizard for SQL 2008, so if my screen caps look a little different, don’t be too worried.  All this should work on any version.

The splash screen for Import and Export Wizard

The splash screen for Import and Export Wizard

Once you start up the program you should get a screen that looks like the image on the right.  For the example we’re going to run through, please download this file, and save it to your machine.  It’s a CSV file with a list of first middle and last names.

Also, open SSMS, connect to your instance of SQL Server and create the following table:

CREATE TABLE example (
     firstName VARCHAR(255)
   , middleName VARCHAR(255)
   , lastName VARCHAR(255) )

This is the destination table we’re going to use.  I’m putting this table in my AdventureWorks2008R2 Database, if you need a copy, click here.

Define your source.

Define your source.

We’re going to load a simple csv file to a SQL Server 2008 table.  Please click next, to continue.

In this screen, you’re going to select your source.  For our example, please select “Flat File Source” in the first box.  This will cause the wizard to change the layout on the page.  You’ll see new options to browse for your file.  Click “Browse”, and select the file you downloaded earlier.  In order to see the file, you’ll need to change the file type to CSV.  Once you’ve selected the file, names.csv  the only other option you’ll need to change is mark the check box beside next to “Column names in the first data row.”  That way, it will read the header information for you.

Your flat file source configured

Your flat file source configured

Your wizard should know look something like the image to the right.

Now we have to define the destination.  In our example we want to load this data to the table we defined above.  In my example, I’m connecting to my 2008R2 instance, and all I have to change is the database name.

Destination Selected!

Your connection should look like the image to the left.

After you choose your database, click next to continue.  You should now be prompted to select the table name.  Click on the entry that under “Destination” and choose dbo.example.  Since we’ve named the columns in the source csv the same as the the destination table, we don’t need to mess with the Edit Mappings button.

You could press the preview button to verify the mapping works, or you can simply press next to continue.

You’re finished.  Before we carry on, go ahead and check off the Save SSIS Package to your hard drive.  That way you can open the package in BIDS and check out the inner workings of the package you just created.  I’ve uploaded my copy here if you need to grab a copy.  Once you’ve run your package, connect to your instance of SQL and check out the results.

SELECT * FROM dbo.example

You should see an exact copy of the csv in your table.

This may not seem like a huge step, but you’ve now created a simple SSIS package.  The steps you’ve taken are the basis of every ETL you can imagine.  When we come back to SSIS packages, I’ll show you how “you’ve just taken your first step into a larger world.”

If you have any questions, send them in!  I’m here to help!

Tags:

10 Responses to “SQL 301 — SSIS Import And Export Wizard”

  1. Ali 20110227 at 02:11 #

    SQL Server Import and Export Wizard

    SQL Server Import and Export Wizard The SQL Server Import and Export Wizard is the easiest utility to work with. Its interactive GUI provides a simple interface to build and run Integration Services packages. You have already used this tool [...]

    for more visit us
    http://server2008.org/?p=888

  2. Bill 20110602 at 16:01 #

    Hi Shannon,

    The problem I find with creating Import/Export packages is that any changes, even the smallest change, breaks the package, especially when Exporting to an Excel file. For example deleting a field from a SELECT statement causes error to propagate to all other components of the package, which would be fine if the corrections I make would actually correct the errors. I create and save the SQL Export packages to the file system and then import them into a new SSIS project. This is very easy and works well, until the specs change. In the SSIS package I can change any for the SQL Export package that I created, the problem is this breaks the package. It is not difficult to recreate and replace the SQL Export package, but why can’t I just change to code. I am using SQL 2005.

    Thank you,
    Bill

    • Shannon Lowder 20110606 at 21:10 #

      Yeah, the Import and Export Wizard isn’t the end-all when it comes to packages. I use it to set up a quick load, but if I need to do more than a straight import, I usually have to open the package in Visual Studio.

      It can be kind of a pain to manage all the mappings (since you have to update them in both the source connection, and in the data pump steps, but it is a lot easier to maintain that my old DTS packages or BCP scripts.

      I’m also seeing some SSIS package tools to make visual maintenance even easier, though I haven’t played with them yet.

      Thanks for the comment! Let me know if I can be of any further assistance, with SSIS or anything else in SQL!

  3. Siddharth Kakkar 20111215 at 07:28 #

    I have one table a in one db server..and table b in another server.

    I would like to update the table b using values in table a.

    Can this be done using SSIS Import And Export Wizard

    • Shannon Lowder 20111216 at 08:25 #

      If you didn’t want to use a full SSIS package, you could use the import and export wizard to copy the data to a temporary table on server b. Then you would write an update statement like:

      UPDATE tb.columnName = tempTable.columnName
      FROM tableb tb
      INNER JOIN tempTable tt
        ON tb.commonColumn = tt.commonColumn
      

      But if you use a full SSIS package, you could do it all from one package. You could do it in a data flow task and an execute sql task, using the update from above. Or use a Data Flow and a Lookup Transform. There is always more than one way to do something when it comes to SQL. If my explanation is unclear in any way, please reach out again and we can set up a chat to go through your problem.

  4. Liz osbourn 20121102 at 10:09 #

    I have created an SSIS package using the Import Export Wizard. I now want to Edit the same package using the Wizard again – how do I open the existing .dtsx using the Import Export Wizard ? All I want to do is add some more tables to the data that is being transferred between 2 SQL servers.

    thanls for any help !

    Liz

    • Shannon Lowder 20130104 at 08:30 #

      Sorry i’ve been offline for so long. The quick way to edit that package would be to start a new SSIS project in BIDS, then add your existing package to that project. Then you can edit it!

      Once you get that far, let me know, we’ll work through it together!

Trackbacks/Pingbacks

  1. SQL 301-BIDS, The First time around | Shannon Lowder - 20110203

    [...] my last post, we used the Import and Export wizard to create a simple Load process.  We loaded names.csv into a [...]

  2. Preparing For The 70-432 | Shannon Lowder - 20110413

    [...] What about the Microsoft SQL Server Import and Export Wizard? [...]

  3. Ssis import | Ponderandpray - 20120924

    [...] SQL 301 — SSIS Import And Export Wizard | Shannon LowderOK, let’s dive right into the Import and Export Wizard. If you’re on Vista or Windows 7, hit start, then begin typing Import and Export Data It should. [...]

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.