In his latest article, Marcin Policht demonstrates a variety of tools that can be employed to execute SQL Server Integration Services packages, focusing in particular on the method leveraging functionality available within the Business Intelligence Development Studio.
We have already introduced
the most rudimentary approach to extraction, transformation, and loading (ETL)
functionality available in SQL Server 2008 (and 2008 R2, based on its November
2009 Community Technology Preview), that leverages Export and Import Wizard. In the course
of our presentation, we have stepped through the process of creating a sample
package that copies the content of a view in the AdventureWorksDW database to a
flat file. As we have explained, such a process can be used to launch the copy
interactively or its outcome can be saved for subsequent execution and
additional modifications. We will now focus on the latter of these options by
describing methods of package execution and development, including DTExec.exe, DTExecUI.exe, SQL Server Management Studio, and Business Intelligence
Development Studio
(also referred to as BIDS).
As we have explained earlier, the Export and Import Wizard allows you to
save packages to either SQL
Server
or File system (as we will
explain in more detail later. There is also a third option known as SSIS Package Store). In the first
case, each package occupies a separate row in the dbo.sysssispackages table of msdb database. While
it is possible to view packages by querying the table directly, a more
convenient method involves connecting SQL Server Management Studio to the Integration Services subsystem, which
displays packages under the Stored PackagesMSDB subnode in the Object Explorer window (you
have an option to group them in an arbitrary manner by creating subfolders
within this hierarchy). To launch a package that you have located there, select
the Run Package entry from its
context sensitive menu. This action triggers Execute Package Utility, where you have
the ability to assign a number of parameters that dictate execution behavior
(we will cover them in one of our future articles). The same utility is also
available outside of the SQL
Server Management Studio
interface in the form of a standalone executable, DTExecUI.exe, invoked
directly from the Command Prompt or via the StartRun menu. Another alternative involves using
its non-graphical equivalent, DTExec.exe,
whose characteristics and syntax are described in SQL Server 2008
Books Online (which, incidentally, is also used when scheduling execution of
SSIS packages as SQL Server Agent jobs).
These two methods are also available
when calling packages stored within file system. In the case of DTExecUI.exe, you need to
choose the File
system
entry in the Package
source
listbox and enter its location in the Package text box. With DTExec.exe, you have to
apply /File switch followed
by the location of the .DTSX file.
The equivalent functionality is
available from within Business
Intelligence Development Studio, although in this case, there are additional steps
that need to be carried out first. The Open->File… entry in its File menu allows for
opening and editing existing DTSX
files, however, it does not facilitate their execution. This capability is
contingent on the existence of an SSIS project (and the solution that
encompasses it), that such files are part of. One way to satisfy this
requirement is to create a new project that will serve as a hosting container
to which our sample DTSX file will be
added as a package.
Before we step through implementation of
this procedure, let’s first get acquainted with the interface of Business Intelligence
Development Studio.
Once you launch it from the Microsoft SQL Server 2008 menu, you will be presented with the Start Page, which closely resembles
the Visual Studio interface. The main window is divided into several panes,
including Recent
Projects,
Get News from
Microsoft,
Getting Started, and Visual Studio Headlines (to alter the
initial view, modify settings available in the Startup entry in the Options dialog box, accessible
via the Tools menu). In
addition, you will also find Solution Explorer and Properties windows there. (Others can be activated
as needed from the View menu).
SSIS packages designed via Business Intelligence
Development Studio
are implemented according to the Visual Studio paradigm, which employs the
concept of a solution consisting of one or more projects, linked by a common
goal they are supposed to accomplish (and encompassing any combination of SQL
Server-based technologies, including Analysis, Integration, and Reporting
Services). To create one, select the New->Project… entry from the File menu,
triggering the display of the New Project dialog box, from which you should choose Business Intelligence
Project
type with Integration
Services Project
template. The Name: and Location: text boxes will
be automatically populated for you, but you are free to modify them in an
arbitrary manner to match your intended storage and naming conventions.
From a logical standpoint, our sample solution is comprised
of a single project and a package named Package.dtsx,
which is represented by a corresponding entry under the SSIS
Packages
folder in the Solution Explorer window. (While the
solution is hidden by default, you have an option to display it by selecting Always show
solution
in the General
section of the Projects and Solutions entry in the Options
dialog box accessible via Tools->Options menu) . As you
can verify by reviewing its content, other project components include Data
Sources
and Data
Source Views (this way, both can be shared across multiple
packages or projects in multi-project solutions). From an operating system
perspective, each project (and, effectively, its solution) has a corresponding
folder (whose location you designated in the previous step), where you will
find (somewhat confusingly labeled):
-
Analysis
Services Database (in the form of .database
file, which stores the project metadata) -
Integration
Services project (.dtproj
file, containing definitions of packages, data sources, and data source views,
as well as project configurations, which, as we will explain later, facilitate
package portability) -
Microsoft
Visual Studio Solution (.sln file, where the
solution configuration resides) -
Visual
Studio Solution User Options (as .suo
file, hosting a number of solution-wide user preferences, such as, your choice
of debugging breakpoints), and its project-level equivalent .dtproj.user
file, with Visual Studio Project User Options.
Once the project opens, the majority of Business Intelligence
Development Studio
desktop estate is occupied by the Designer window. This is your primary interface
for package development. Its area is divided into five tabs, with four of them,
labeled Control
Flow,
Data Flow, Event Handlers and Package Explorer grouped at the
top, and Connection
Managers
positioned at the very bottom. (There is actually an additional tab labeled,
depending on the context, Progress
or Execution
Results,
which appears dynamically once you execute the package). Each of them
represents a specific group of SSIS features, which we will be discussing in
detail throughout our upcoming articles.
Toolbar contains variety of components that are
utilized during package configuration (its content is context sensitive,
changing as you switch between different areas of the Designer). The Properties window displays
(and accommodates edits of) attributes of objects currently selected within the
Business
Intelligence Development Studio interface. Other windows (such as Bookmark, Class View, Code Definition, Object Browser, Error List, or Output), can be made
available by activating options in the View menu (which also gives you the ability to display a
number of Toolbars). Each of the
visual elements described above is highly customizable in regard to its
visibility, position, and variety of other security and usability-related
characteristics.
Since our intention was to facilitate
execution of our sample package (whose creation was described in our previous
article), let’s walk through the remaining steps necessary to accomplish this
goal. Start by deleting an existing Package.dtsx entry in the SSIS Packages folder of the Solution Explorer window (which
was auto generated based on the Integration Services Project template we
selected earlier). Next, right click on the SSIS Packages folder and select Add Existing Package from its
context sensitive menu. (Note that, as we pointed out before, it is possible to
launch Import
and Export Wizard
from here as well). This will trigger the Add Copy of Existing Package dialog box,
where you need to specify the Package location (SQL Server,
File System, or SSIS Package Store) and Package path. (Keep in mind
that adding an existing package to a project creates a duplicate of the DTSX file in the
same directory that hosts all other project’s files). Once imported, our
package is ready to launch (which can be done directly via the Execute Package entry in its
context sensitive menu).
In our next article, we will review the
content of this sample package by analyzing its structure in the Designer interface of Business Intelligence
Development Studio.
We will also look into modifying some of its characteristics, introducing some
of the more rudimentary principles of SSIS development process.
Additional Resources
MSDN Introducing Business Intelligence Development Studio
Microsoft Support
