Creating an Integration Services package is one of the most complex tasks you will perform as a database administrator. Fortunately, the SQL Server Import And Export Wizard is designed to help you build Integration Services packages with minimal difficulty, but it is still an involved process. To help reduce complexity, we can divide the creation process into stages and then examine each stage individually. The stages you use to create Integration Services packages are as follows:
To begin using Integration Services, start the SQL Server Import And Export Wizard, and then click Next to advance to the Source selection page. You start the SQL Server Import And Export Wizard in SQL Server Management Studio by completing the following steps:
In SQL Server Management Studio, connect to the server instance containing the database with which you want to work.
In Object Explorer view, expand the Databases node. Select a database, and then right-click its name, point to Tasks, and select either Import Data or Export Data.
You can also run the SQL Server Import And Export Wizard from the command line by typing dtswizard.
The first task associated with creating an Integration Services package is to choose the source and destination for the import/export operation. If you started SQL Server Import And Export Wizard and clicked Next, you will see the Choose A Data Source page. At this point, complete the following steps:
Use the Data Source drop-down list box to select the source for the import/export operation. SQL Server has .NET Framework, OLE DB, and ODBC data providers. These data providers allow you to work with SQL Server, Oracle, Access and Excel, Microsoft Analysis Services, Microsoft Data Mining Services, Microsoft Internet Publishing, SQLXML, and flat files. Select the data source that matches the type of file, application, or database you want to use as the source. For example, if you are copying from an Excel spreadsheet, choose Microsoft Excel as the source for the import/export operation.
Fill in any additional information required to establish a connection to the source. (The source you select determines what additional information you need to supply.) Click Next.
Use the Destination drop-down list box to select the destination for the import/export operation.
Fill in any additional information required to establish a connection to the destination. As with the source, the destination you select determines what additional information you need to supply.
Click Next to proceed to the next stage of the operation: Copy or Query.
If choosing a source and destination were as easy as these simple steps appear, this task would require very little effort. But sometimes it is not clear what additional information you need to provide because there are several different kinds of sources and destinations that you can select. These sources include:
.NET Framework Data Provider connections.
File-based data connections.
Server-based connections to databases other than SQL Server.
Server-based connections to SQL Server.
Flat files.
We will examine each of these connection categories more closely.
SQL Server 2005 includes .NET Framework Data providers for ODBC, Oracle, and SQL Server. The .NET Framework Data Provider for ODBC is the only ODBC driver supported. You configure the .NET Framework Data Providers through a dialog box similar to the one shown in Figure 10-1. You must provide the following information, depending on the .NET Framework Data Provider you are using:
If you are using .NET Framework Data Provider for ODBC, you must specify the connection string, the data source name (DSN), and the name of the ODBC driver to use when connecting to the data source.
If you are using .NET Framework Data Provider for Oracle, you must specify the connection string to use in the ConnectionString box, the User ID and Password to use in establishing the connection, and the name of the database to which you want to connect in the Data Source text box. As necessary, you can configure other initialization, pooling, and security parameters as well.
If you are using .NET Framework Data Provider for SQL Server, you must specify the Network Library (options are provided on a drop-down list when you click in the text box), the connection string to use in the ConnectionString box, the User ID and Password to use in establishing the connection, and the name of the database to which you want to connect in the Data Source text box. As necessary, you can configure other initialization, pooling, and security parameters as well.
You use file-based data connections with applications and databases that are file-based. For example, you would use this type of connection with Access and Excel. You use a dialog box similar to the one shown in Figure 10-2 to configure file-based connections. For Access, you must provide the following information:
File Name. The full file name or Uniform Naming Convention (UNC) path to the source or destination file, such as //omega/data/excel/cust.xls
User Name. A valid user name for accessing the source or destination file
Password. A valid password for accessing the source or destination file
For Excel, you must provide the following information:
Excel File Path. The full file name or Uniform Naming Convention (UNC) path to the source or destination file, such as //omega/data/excel/cust.xls
Excel Version. The version of Excel from which you are copying data
You use server-based data connections to connect to databases other than SQL Server. Use this type of connection with Microsoft OLE DB Provider for Oracle, Microsoft OLE DB Provider for Analysis Services 9.0, Microsoft OLE DB Provider for Data Mining Services, Microsoft OLE DB Provider for OLAP Services 8.0, and SQLXMLOLEDB. You configure server-based connections by setting Data Link properties that connect to a data source. Data Link properties have four components:
An OLE DB provider, which you select from the Source or Destination selection list in the SQL Server Import And Export Wizard.
Connection options, which you set using the Connection tab in the Data Link Properties dialog box. Connection options typically include a data source name or connection string accompanied by the user name and password information needed to log on to the database.
Advanced options, which you set using the Advanced tab in the Data Link Properties dialog box. Advanced options let you configure network settings, time-outs, and access permissions (as long as these options are configurable).
Initialization properties, which you view using the All tab in the Data Link Properties dialog box. The initialization properties display all the options you have configured for the provider and provide a central location for editing values. Simply double-click a value to edit the associated settings.
If you are using Oracle, the Oracle client and networking components must be installed on the system running SQL Server. If these components are not installed, you will not be able to use the OLE DB provider. Assuming that the Oracle client is installed on your system, you can set the Data Link properties for Oracle by completing the following steps:
In the SQL Server Import And Export Wizard, select Microsoft OLE DB Provider for Oracle on the Source or Destination selection list, and then click Properties to display the Connection tab of the Data Link Properties dialog box, as shown in Figure 10-3.
Type the name of the Oracle server to which you want to connect in the Enter A Server Name text box.
Then type the user name and password needed to log on to the database in the appropriate text boxes.
To test the connection to the server, click Test Connection. If the connection fails, you may have improperly configured the Oracle client.
You can use the Advanced and All tabs to view additional options. Change these options as necessary.
When you have finished setting the Data Link properties for Oracle, click OK.
In addition to using the .NET Framework Provider for SQL Server, you can connect to SQL Server using the SQL Native Client or the Microsoft OLE DB Provider for SQL Server. The options you have available using either of these alternate connections are shown in Figure 10-4, and you can configure the connection by completing the following steps:
Use the Server Name drop-down list box to select the SQL Server for the connection. If the server you want to use is not listed, type in the server name.
Next, select an authentication method. Type a user name and password, if necessary.
Use the Database drop-down list box to select a database. You must provide valid credentials and those credentials must have sufficient privileges.
Click Advanced to set advanced options for the driver/provider.
You can use flat files as a data source or destination. When you do, you must provide additional information about the input or output formatting. The steps in the process are similar when using flat files as either the source or the destination. To use text files as a data source, use the process below as an example and complete the following steps:
From the SQL Server Import And Export Wizard, choose the Flat File Source option. Then enter the full file name or UNC path to the file with which you want to work.
After you enter the text file information, the wizard page is updated as shown in Figure 10-5.
The values in the Locale and Code Page boxes are set based on the file you have selected. If the values are incorrect, select the appropriate values.
Select the file type using the Format drop-down list box. The file must be formatted in an acceptable flat file format such as ANSI (ASCII text), IBM EBCDIC, MAC, OEM (original equipment manufacturer), UTF-7, or UTF-8.
Specify how the file is delimited. If the file has fixed-width columns, select the Fixed Width option from the Format selection list. If the columns are delimited with commas, tabs, semicolons, or other unique characters, select the Delimited option from the Format selection list.
Use the Text Qualifier box to specify the qualifier for text as Double Quote ("), Single Quote (’), or <None>.
Specify the header row delimiter using the Header Row Delimiter drop-down list box. The available options are:
{CR} {LF} for carriage return and line feed
{CR} for carriage return only
{LF} for line feed only
Semicolon
Colon
Comma
Tab
Vertical bar for the | character
To skip rows at the beginning of a file, use the Header Rows To Skip box to set the number of rows to skip.
If the first row contains column headers, select the check box for the option Column Names In The First Data Row.
This completes the General page options. For an export, the other pages are not available or applicable, so skip to step 17.
Select Columns from the selection list in the left pane of the wizard to proceed. The wizard will attempt to determine the row and column delimiters and then will display a preview of the data.
If you selected fixed-width columns, you must indicate to the SQL Server Import And Export Wizard where columns start and end. Vertical lines indicate the start and end of columns. Add column markers by clicking in the Source Data Columns area to create a column marker. Remove column markers by double-clicking them. Move column markers by clicking them and dragging them to a new position.
If necessary, specify the end-of-row delimiter using the Row Delimiter drop-down list box.
If necessary, specify the column delimiter within rows.
Select the Advanced page from the selection list in the left pane of the wizard to configure the output properties for each column, including the output column name, output column width, and output data type. If there are different delimiters between columns, you can specify the delimiter on a per column basis.
Select the Preview page from the Data Source list to see the data format for the options you have chosen. If you notice data elements out of place, you should reconfigure the options before continuing. You may also need to modify the source file. In this case, click Cancel, modify the file, and then restart the SQL Server Import And Export Wizard.
Click Next when you are ready to select the destination for the import/export operation. After selecting the destination, you are ready to move on to the second stage of creating an Integration Services package.
With most import or export procedures, the second stage of the process involves specifying tables and views to copy or building a query to specify the objects to transfer. You first select the operation using the dialog box shown in Figure 10-6, and then depending on the choice you have made, you will proceed as described in the following subsections.
If you want to copy tables and views to the destination, you must select which tables and views you want to copy. When a text file is the data source, making the selection is easy—only one table is available, and you cannot select any views. If you are using any other data sources, however, you must select the tables and views you want to copy. You use the Select Source Tables And Views page of the wizard, shown in Figure 10-7, to make your selections.
To select tables and views, complete the following steps:
On the Specify Table Copy Or Query page (shown in Figure 10-6), select Copy Data From One Or More Tables Or Views, and then click Next.
On the Select Source Tables And Views Page (shown in Figure 10-7), select a table or view by clicking its entry, and then preview the data the table contains by clicking Preview.
When you find a table or view you want to copy, select the check box next to it in the Source column.
By default, the destination name of the table is set to be the same as the source table name. If you want to change the table name, edit the corresponding value in the Destination column.
If you want to manipulate the row values in a table, select the table, and then click the corresponding Edit button in the Mapping column. Mapping row values is covered in the section titled "Stage 3: Formatting and Transformation" later in this chapter.
Another way to select data for exporting is to build a query and execute it against the source file, spreadsheet, or database. Regardless of the type of data source you select, you build the query in the same way by completing the following steps:
On the Specify Table Copy Or Query page (shown in Figure 10-6), select Write A Query To Specify The Data To Transfer, and then click Next.
On the Provide A Source Query page, you can:
Type a query directly in the text box provided, and then parse it to check for accuracy using the Parse button.
Click Browse to open a previously saved query.
Click Next. On the Select Source Tables And Views page, the tables and views selected by the previously defined query are listed and selected.
By default, the destination name of the table is set to be the same as the source table name. If you want to change the table name, edit the corresponding value in the Destination column.
If you want to manipulate the row values in a table, select the table, and then click the corresponding Edit button in the Mapping column. Mapping row values is covered in the section titled "Stage 3: Formatting and Transformation" later in this chapter.
The Query Designer in SQL Server Management Studio provides the easiest way to design a query to export data. You can start and work with the Query Designer by completing the following steps:
In SQL Server Management Studio, connect to the server instance containing the database in which you want to work.
In Object Explorer view, expand the Databases node. Select a database, right-click it, and then select New Query to display a query window with its own toolbar. A similar list of options (with more selections) is provided on the Query menu.
Access the Query Designer by selecting Design Query In Editor on the Query menu or by pressing Ctrl+Shift+Q.
When you first start the Query Designer, the Add Table dialog box shown in Figure 10-8 is displayed. The Add Table dialog box has tabs that allow you to select the tables, views, functions, and synonyms you want to work with.
In the Add Table dialog box, select a table or other object that contains data you want to add to the query, and then click Add. This displays a view pane for the selected object, which you can use to add columns, fields, and so on to the query you are building. When you are finished working with the Add Table dialog box, click Close. You can display this dialog box again at any time by selecting Add Table on the Query Designer menu.
Use the view panes provided to select the columns and fields to use in the view, as shown in Figure 10-9. Your actions create a SELECT statement that can be used to generate the query.
When you have finished designing the query, click OK to close the Query Designer window. The query you have generated is then added to the Query window.
The result of the Query Designer procedure is a complete SQL statement that you can use to select data for exporting. Click Parse to ensure that the query runs properly. If necessary, rebuild the query or remove statements that are causing errors.
Copy the query to the SQL Server Import And Export Wizard.
Transformation is the process of manipulating the source data and formatting it for the chosen destination. The way you transform and format data depends on the destination you chose. With most types of files, databases, and spreadsheets, you are guided through a column mapping and transformation process. But if you have chosen a text file as the destination, you must also specify the format of the output file. Because the formatting options are essentially the same as those used for importing, you can find more information about these options in the section titled "Importing and Exporting Flat Files" earlier in this chapter.
Unless you specify otherwise, the SQL Server Import And Export Wizard sets default mapping for all selected tables. This default mapping:
Specifies that every column in the source table is copied.
Maps the original column name, data type, nullability, size, precision, and scale to the destination table.
Appends the source data to the destination table or creates the destination table if it does not exist.
You can override the default mapping by completing the following steps:
The Select Source Tables And Views page lists the results of your query or all of the available tables in the source database, spreadsheet, or file that you have selected. If you have selected a particular table, you will see an Edit button in the Mapping column. Click this button to open the Column Mappings dialog box shown in Figure 10-10.
In the Column Mappings dialog box, set the general transfer options:
Create Destination Table. Creates the destination table before copying source data. If the destination table exists, you must select the check box for the Drop And Re-create Destination Table option or an error will occur.
Delete Rows In Destination Table. Deletes all rows in the destination table before copying the source data. Indexes and constraints on the destination table remain.
Append Rows to the Destination Table. Inserts the source data into the destination table instead of overwriting existing data. This option does not affect existing data, indexes, or constraints in the destination table.
Drop and Re-create Destination Table. Drops and re-creates the destination table before attempting to copy data into it, which permanently deletes all existing data and indexes.
Enable Identity Insert. Allows you to insert explicit values into the identity column of a table. This option is available only on SQL Server and only if an identity column is detected.
Edit SQL. Displays the Create Table SQL Statement dialog box, which allows you to customize the default CREATE TABLE statement.
After you set the general transfer options, use the fields in the Mappings list box to determine how values are mapped from the source to the destination. The fields are all set to default values based on the source column. If you want to override these values for a new table or if you are dropping and recreating an existing table, you can modify these values. The Mappings fields are used as follows:
Source. Sets the source column to map to a Destination column.
Destination. Click in this column, and then select an existing column name or type a new column name for the destination table. Use the <ignore> option if a Destination column should not be created.
Note
If a Destination column already exists and you choose <ignore>, the source data will not be copied into this column.
Type. Select a data type for the Destination column. If you select a different data type than the data type of the Source column, the data is converted to the new data type during the transfer.
Note
Make sure you select a valid conversion option. The SQL Server Import And Export Wizard will not let you truncate data, and if you try to do so, an error will occur.
Nullable. Select this check box if the destination allows NULL values.
Size. Sets the length of the Destination column. This value is applicable only for the char, varchar, nchar, nvarchar, binary, and varbinary data types.
Note
Setting the size smaller than the length of the source data can result in data truncation. If this happens, the SQL Server Import And Export Wizard will generate an error and will not complete the data transfer.
Precision. Sets the maximum number of decimal digits, including decimal places. For decimal and numeric data types only.
Scale. Sets the maximum number of digits to the right of the decimal point. This value must be less than or equal to the Precision value, and it applies to decimal and numeric data types only.
Click OK, and then repeat this process for other tables you want to transform.
When you are ready to continue, click Next.
You have nearly completed the process of creating an Integration Services package. At this stage, you specify when to use the package you have created and decide if the package should be saved for future use. After you clicked Next in the Select Source Tables And Views dialog box, the Save And Execute Package page, shown in Figure 10-11, will display. To use this page, complete the following steps:
By default, the Execute Immediately option is selected so you can run the package. If you do not want to run the package immediately, simply clear the check box for this option.
Use the options in the Save area to save the package for future use. If you want to save the package to use later, select the Save SSIS Package check box, and then specify where the package should be saved. The available locations are:
SQL Server. Saves as a local package in the msdb database so the package is accessible for use on the designated server.
File System. Saves as a DTSX file. You can add additional packages to the file as long as they have a different package name. You can then copy, move, or e-mail the file to a different location.
When you have finished configuring the run and save options, click Next to display the Package Protection Level dialog box.
Use the options in this dialog box to set the encryption options for the package. The options are:
Do Not Save Sensitive Data. Creates the package but does not save sensitive data in the package.
Encrypt Sensitive Data With User Key. Creates the package with sensitive data encrypted. The package can only be opened or executed by the user who created the package (the current login account).
Encrypt Sensitive Data With Password. Creates the package with sensitive data encrypted. The package can be opened or executed with the password you specify. This means that anyone with the password can open or execute the package.
Encrypt All Data With User Key. Creates the package with all data encrypted. The package can only be opened by the user who created the package (the current login account).
Encrypt All Data With Password. Saves the package with all data encrypted. The package can be opened or executed with the password you specify. This means that anyone with the password can open or execute the package.
Rely On Server Storage And Roles For Access Control. Creates a package that uses SQL Server permissions and roles to control access (only available if you save the package to SQL Server).
If you have opted to save the package, the next page lets you set the save location (see Figure 10-12). The options may differ slightly from those shown, depending on the save location you previously selected.
Type a name and description of the package in the Name and Description boxes. The name should be unique for the target location.
If you are saving the package to SQL Server, use the Server Name drop-down list to select the name of the SQL Server to which you want to save the package. The package is saved in the msdb database on the designated server.
Select the type of authentication to use by selecting one of the option buttons for either Windows Authentication or SQL Server Authentication. Provide an authorized user name and password if you select the Use SQL Server Authentication option.
If you selected to save the package to a file, set the file location using the File Name box.
Click Next. Review the actions that will be performed, and then click Finish.
If you have elected to run the package immediately, SQL Server runs the package. As each step is completed (or fails), the status is updated. If an error occurs, you can click its message entry to view a detailed report of the error. Errors may halt execution of the package, and if they do, you will have to redesign the package using Integration Services Designer or re-create the package using the SQL Server Import And Export Wizard.
SQL Server stores Integration Services packages as local packages in a designated server’s msdb database and as file-based packages. You manage packages by using SQL Server Management Studio, Business Intelligence Development Studio, or the Execute Package Utility (dtexecui). Two command-line utilities are provided as well: dtutil for copying, moving, signing, and deleting packages and dtexec for executing packages.