SSIS Packages - the Simplest Way to Create Them

Thursday Feb 18th 2010 by Marcin Policht

Marcin Policht delves deeper into the specifics of SQL Server 2008 and 2008 R2's Integration Services by presenting the most straightforward method of creating SSIS packages using Import and Export Wizard.

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 Task\Export 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 Server\100\DTS\MappingFiles). 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

Mobile Site | Full Site