Wizards for SSIS

Wizards for SSIS

Microsoft has updated the Import/Export Wizard from SQL Server 2000 that was used to help DBA and developers automate the repetitive tasks of moving and copying data from one location to another.

Import/Export Wizard

The simplest and most commonly used wizard in DTS 2000 was the Import/Export Wizard. It allowed DBAs, developers, and novice users to move data from a source of any type to a target of any type, very quickly, with no code and very little hassle. It also provided a starting point for learning how to build and design DTS packages. The Import/Export Wizard makes an encore appearance in SQL Server 2005 with some improvements including:

· Improved flat-file control for both source and destination.

· Real-time preview of data.

· Optimization of processing when a large number of tables and views is desired.

· Creation of a new database directly from the wizard.

Figure 1 shows the opening screen of the wizard while the next several figures show subsequent steps in the wizard.

Figure 1

Along with the new features, the user interface has been updated and should save time for those who frequently use the wizard.

One of the more pleasant improvements includes the capability of creating the target database directly from the wizard. In DTS 2000, the user would have to stop the Import/Export process, return to Enterprise Manager, and create the database. Now, the administrator can create and configure a new database using the dialog box shown in Figure 2

SSIS Introduction

Introduction

Microsoft® SQL Server™ Integration Services (SSIS) has gone through a complete redesign and rewrite from Data Transformation Services (DTS) in SQL Server 2000. As part of that process, many of the product design and administration paradigms were rethought. SSIS is now no longer a stand-alone designer; SSIS adopts Microsoft Visual Studio® as its development environment, but continues to leverage the standard management tool for SQL Server 2005. The new development environment is called the Business Intelligence (BI) Development Studio, while the administration environment is aptly named SQL Server Management Studio. The separation of the two environments allows developers and database administrators (DBAs) to focus on specific tasks of development or administration.

Both environments are capable of executing packages, but the BI Development Studio can only execute on the developer console, while the Management Studio executes those packages on the server that have been imported. BI Development Studio solutions contain one or more projects, while projects contain data sources, data source views, SSIS packages, and other miscellaneous files. For example, a new project can contain the SSIS package you’re developing and also any packages that you may be calling out to. It also might contain all of your supporting files such as data definition language (DDL) and data manipulation language (DML) files for databases you’re working with. When it comes time to deploy to a production environment, you’ve got all the necessary files in one location.

BI Development Studio doesn’t require a direct connection to the SQL Server RDBMS to design packages, nor does it require a connection to save your work. BI Development Studio saves projects into project folders, just like Visual Studio does. BI Development Studio can also have direct integration to Visual SourceSafe (and other source control systems) from the design environment. As changes are made to projects, they can be immediately checked into VSS.

Management Studio, unlike BI Development Studio, is aimed primarily at DBAs and is used to manage SQL, Analysis Services, and Reporting Services servers. It supports the executing and scheduling of SSIS packages, but not the editing or designing of them. However, it does allow users to use the SSIS framework to import and export data and to perform routine maintenance tasks.

ETL Process Diagram