SQL Server - Introduction to Business Intelligence Development Studio
March 8, 2010
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 Packages\MSDB 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 Start\Run 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):
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.