Creating SSIS Packages with the SQL Server Import and Export Wizard

This material was originally posted on the Linchpin People blog.

In this post, I demonstrate how to use the SQL Server 2012 Import and Export Wizard to create a SQL Server 2012 Integration Services (SSIS 2012) package. The demo is created on a virtual machine running Windows Server 2012.

You can use the SQL Server Import and Export Wizard to learn more about SQL Server Integration Services. Often, one will reach the “Save and Run Package” just before executing the Wizard. The “Run immediately” checkbox is checked and the “Save SSIS Package” checkbox is unchecked by default. If you check the “Save SSIS Package” checkbox, you can select to store the SSIS package in the MSDB database (the “SQL Server” option) or File system. You may also select the Package Protection Level:

If you select these options, the next step is selecting the target location of the package:

The next page of the Import and Export Wizard displays a summary of the selected options:

Clicking the Finish button creates, saves, and executes the SSIS package as configured, and the results are displayed:

Cool. Now what?

Now you have an SSIS package saved in the file system. In this case, the file is named ImportExportWizard.dtsx. It can be opened in SQL Server Data Tools by right-clicking the file and selecting “Edit”:

Windows Server 2012 prompts for which program to use to perform the Edit. I select Microsoft Visual Studio Version Selector:

SQL Server 2012 Data Tools uses the Visual Studio 2010 Integration Development Environment (IDE) at the time of this writing. Note the “Run” (Play) button is disabled on the toolbar:

We have an SSIS package created with the Import and Export Wizard, but we cannot re-execute it.

Bummer. But all is not lost.

Visual Studio needs a debugger to execute packages in debug mode. When we open an SSIS (*.dtsx) file all by itself, Visual Studio doesn’t load a debugger. To have Visual Studio load the SSIS debugger, we can create a Solution containing a Project. Begin by clicking File, New, Project:

Name the new solution and project, and select a folder:

Once the solution and project are created, right-click the SSIS Packages virtual folder in Solution Explorer, and select “Add Existing Package”:

Navigate to the location where you stored the SSIS package in the final steps of the Import and Export Wizard:

When you click the OK button, the SSIS package is copied into the project folder and added to the new project:

When you open the SSIS package in the context of a Visual Studio project and solution, the Debug button is enabled on the toolbar and the SSIS package can be executed in debug mode:

You can use your knowledge of the Import and Export Wizard to learn more about SSIS package development. Happy Integrating!

:{>