SSIS Packages – the Simplest Way to Create Them


Marcin Policht delves deeper into the specifics of the Integration Services feature of SQL Server 2008 and 2008 R2’s capabilities by stepping through the process of creating and configuring sample packages that take advantage of SSIS functionality. He starts by presenting Business Intelligence Development Studio’s considerably simpler alternative, known commonly as the Import and Export Wizard.

In the recent articles dedicated to the subject of Integration
Services feature of SQL Server 2008 and 2008 R2
(based on its November 2009
Community Technology Preview), we have covered its basic principles and core
functionality as well as described its installation and initial configuration.
The information provided so far should give you a high-level understanding of
this technology, its software prerequisites, and its role in business decision
support solutions; however, it is hardly sufficient to appreciate the full
breadth of its capabilities.

In our pursuit of this challenging goal, we will delve
deeper into the specifics of its implementation by stepping through the process
of creating and configuring sample packages that take advantage of SSIS
functionality. Since the interface of Business Intelligence Development Studio might seem
confusing at first (unless you have some previous programming experience), we
will start by presenting its considerably simpler alternative, known commonly
as the Import and
Export Wizard.

The wizard (which takes the form of the executable DTSWizard.exe) is
present in every edition of SQL Server 2008. Even if you decide to exclude
Integration Services from the SQL Server setup, all components necessary to
execute it are automatically installed as part of the Database Engine Service feature. It is
available in both 32 and 64-bit versions (although the latter, installed by
default on x64 and Itanium platforms, is not capable of interacting with
certain data repositories, such as Access or Excel, which rely on 32-bit data
providers). It offers the easiest, but also most limited, method of generating
SSIS packages that perform data copy between arbitrary sources and destinations
(as long as they are accessible via .NET Framework or OLE DB providers).

There are several different ways of starting the wizard. The
most universal one (applicable regardless of the installation type) involves
launching the executable (DTSWizard.exe)
directly (typically via Open
dropdown list in the Run
dialog box accessible via Windows Start menu), although, when operating on an x64 platform,
you should verify that you are running the intended version (32-bit vs.
64-bit). With Integration Services installed on the target computer, you might
want to take advantage of Import
and Export Data entries in the Microsoft SQL Server 2008 (or 2008 R2 submenu of All Programs menu),
which clearly designate the type of executable. Another approach relies on the Import Data… and Export Data…
options in the context sensitive Tasks menu of database objects appearing in the Object Explorer
window of SQL Server
Management Studio. While it is also possible to accomplish the same
objective using the SSIS
Import and Export Wizard… entry in the Project menu of the Business Intelligence Development Studio,
this requires having an existing project already open.

Keep in mind that the method you choose might have an impact
on the subsequent execution behavior. In particular, invoking the wizard from SQL Server Management Studio
will launch the resulting package, as long as you do not clear the ‘enabled by
default Execute
immediately’ checkbox on the Save and Run Package page (which, by the way, also gives
you the ability to save the package, unless you are using SQL Server 2008
Express Edition, which does not offer this functionality). The same rule
applies when activating Import
and Export Data directly from the Start menu or by calling the DTSWizard.exe
executable. On the other hand, when working on an Integration Services project
within the Business
Intelligence Development Studio, you first have to save your modifications
before the newly edited package can be launched (note that despite these
differences, the interface presented by the wizard remains, for the most part,
consistent).

For the sake of simplicity, we will use the first of these
methods. Once you have installed the AdventureWorksDW database (refer to Installing
and configuring SQL Server 2008 and 2008 R2 Integration Services
for
more information on this subject), right click on it and select the TaskExport Data…
entry from its context sensitive menu in the Object Explorer window of SQL Server Management Studio
(this action requires server and database login privileges). After the initial,
purely informational page, you will be prompted to Choose a Data Source from a default list comprised
of a number of .NET
Framework Data (intended for ODBC, Oracle,
and SQL Server)
and OLE DB
providers (for
Analysis Services and SQL
Server), as well as Flat
File Source and SQL
Server Native Client. Accessibility to additional types of data is
contingent on the version of the operating system and components included as
part of its setup (such as, OLE
DB Provider for Search on Windows 7), or the presence of auxiliary
software from Microsoft (for example, SQL Server 2008 Feature Pack,
which facilitates DB2
support, or 2007 Office System Driver:
Data Connectivity Components
, which gives you the ability to work
with the latest versions of Excel and Access) or third party vendors. Other
options appearing on the Choose
a Data Source page are dependent on your selection. For the purpose of
our example, we will use SQL
Server Native Client 10.0, which automatically points to the local
server (designated by a single period in the Server name: listbox) and the AdventureWorksDW
database. Once you click on the Next
button, you will be expected to Choose
a Destination. The range of potential choices in this category extends
beyond those listed above and also includes Microsoft Access or Excel (if you are running 32-bit
version of the wizard), and a number of OLE DB Providers (for Analysis Services 10.0, Data Mining Services,
or OLAP Services 8.0).
Again, we will opt for one of the least complex options and select Flat File Destination,
for which you need to assign the target File name (with its full path), Locale and Code page, Format (Delimited, Fixed width, or Ragged right) and Text qualifier, as
well as designate whether there will be Column names in the first data row.

After defining the source and destination, you reach the Specify Table Copy or Query
page, where you are supposed to decide whether you want to rely on existing
tables or views to extract data you want to transfer (Copy data from one or more tables or views)
or whether you want to customize its scope further by using a query (Write a query to specify the
data to transfer). We will take advantage of the first of these two
choices, which simplifies your task by displaying the listing of all relevant
objects (note that this will require at the very least the SELECT permissions on
them) from our sample database in the Source table or view listbox on the Configuring Flat File
Destination page. Once you have made the selection, assign the desired
values to the Row
delimiter and Column
delimiter (set by default to {CR}{LF} and Comma {,}, respectively). At the lower part of the page,
you will find two command buttons. One of them, labeled Preview allows you to take a quick
look at the content of the selected table or view. The other, named Edit Mappings gives
you the ability to alter default pairing of source column with entries in the
destination file. For each of them, it is possible to alter the name of the
resulting column, its type, nullability, and size, although the significance of
these options varies depending on the destination type (those that affect data
type conversion between disparate data stores are stored in XML files residing
in %ProgramFiles%Microsoft SQL
Server100DTSMappingFiles). In addition, if a target file already exists
(its presence will be automatically detected), you can Delete rows in the destination file or
Append rows to the
destination file (in scenarios where the target is a table with an
identity column, the wizard enables its Identity insert option).

On the Save
and Run Package page, the Run immediately checkbox, which is by default enabled,
triggers the export. By changing the state of the Save SSIS Package checkbox, you have the ability
to store it (which is necessary if you want to schedule its execution) in
either SQL Server
or in File system.
In either of these two cases, you have an option to assign the Package protection level
that secures its content with a password or a user key (as mentioned earlier,
this functionality is not available in SQL Server 2008 Express Edition). When
using SQL Server as the store, you will be prompted for the package Name, Description, the
target SQL Server name
and authentication method associated with it (keep in mind that this approach
requires at minimum INSERT
permissions on the msdb
database). Using File
system for that purpose, results in the creation of an XML formatted DTSX file in an
arbitrary location, to which you have write permissions (we will be discussing
these topics in more detail in future articles).

Once these choices are made, you are ready to carry out the
export. If you decided to run the package immediately, the step-by-step
progress of execution will be displayed on the final page of the wizard (where
you also have an option to save the corresponding report to a file, copy it to
Clipboard, or send it via e-mail). In addition, as long as you decided to save
the resulting package, you have an option to launch it again or edit its
content with Business
Intelligence Development Studio. We will discuss each of these
possibilities in our next article.

»


See All Articles by Columnist
Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles