THE SQL Server Blog Spot on the Web

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

Andy Leonard

Andy Leonard is a Data Philosopher at Enterprise Data & Analytics, an SSIS Trainer, Consultant, developer of the Data Integration Lifecycle Management (DILM) Suite, a Business Intelligence Markup Language (Biml) developer and BimlHero; SQL Server database and data warehouse developer, community mentor, engineer, and farmer. He is a co-author of SQL Server Integration Services Design Patterns, and the Stairway to Integration Services. Keep up with Andy, join his mailing list!

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:

ImportExportWizard1

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

ImportExportWizard2

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

ImportExportWizard3

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

ImportExportWizard4

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”:

ImportExportWizard5

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

ImportExportWizard6

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:

ImportExportWizard7

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:

ImportExportWizard8

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

ImportExportWizard9

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

ImportExportWizard10

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

ImportExportWizard11

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

ImportExportWizard12

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:

ImportExportWizard13

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

:{>

Published Monday, February 20, 2017 11:27 AM by andyleonard
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

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement