StarQuest Technical Documents

How to Use StarSQL in a SQL Server 2005/2008 SSIS Package

Last Update: 4 February 2009
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ011

Abstract

This document describes how to create a SQL Server 2005/2008 Integration Services (SSIS) package to import or export DB2 data using a StarSQL data source.

Solution

The following sections guide you through the creation of a SSIS package using either the DTS Wizard (to import DB2 data) or the SQL Server Business Intelligence Studio (to export SQL Server data to DB2).

StarSQL (64-bit) users should review the StarQuest Technical Document Considerations for using StarSQL (64-bit) with SQL Server 2005/2008.

Import DB2 Data into SQL Server
Export SQL Server data to DB2

To Import DB2 Data

SQL Server 2005
SQL Server 2008

SQL Server 2005

  1. Start the Microsoft SQL Server Management Studio.
  2. Right-click the SQL Server database that you want to import data into, select Tasks, and choose Import Data.
  3. On the Welcome dialog, click Next.
  4. When you are prompted to choose a Data source, select .Net Framework Data Provider for Odbc. 
  5. In the Dsn field, enter the name of the StarSQL System DSN you wish to use.
  6. In the Driver field, enter:

    StarSQL 32, if using StarSQL (32-bit)

    or

    StarSQL (64-bit), if using StarSQL (64-bit)

    and press the Tab key to generate the ConnectionString value.

  7. Edit the ConnectionString value to add a valid DB2 user ID and password using the parameters uid and pwd, respectively, and press Tab. The finished ConnectionString should look similar to the following:

    Dsn=DB2PROD;Driver={StarSQL 32};uid=DB2USER;pwd=SECRET

    Example of ConnectionString

  8. Click Next.  If the connection is successful, the wizard resumes. If an error occurs, review the settings and try again.

SQL Server 2008

  1. Start the Microsoft SQL Server Management Studio.
  2. Right-click the SQL Server database that you want to import data into, select Tasks, and choose Import Data. NOTE: StarSQL (64-bit) users should execute the 64-bit DTS wizard, see StarQuest Technical Document Considerations for using StarSQL (64-bit) with SQL Server 2005/2008 for more information.
  3. On the Welcome dialog, click Next.
  4. When you are prompted to choose a Data source, select .Net Framework Data Provider for Odbc. 
  5. In the Dsn field, enter the name of the StarSQL System DSN you wish to use.
  6. In the Driver field, enter:

    StarSQL 32, if using StarSQL (32-bit)

    or

    StarSQL (64-bit), if using StarSQL (64-bit)

    The dialog should look similar to the following:

    Example of ConnectionString

  7. In SQL Server 2008, the wizard does not provide fields for the User ID and Password. It is therefore necessary to configure the StarSQL DSN with both the User ID and Password. Refer to the StarQuest Technical Document How to Automate StarSQL Logins for instructions.
  8. Click Next.  If the connection is successful, the wizard resumes. If an error occurs, review the settings and try again.

     

To Export Data into DB2

SQL Server 2005/2008 Integration Services (SSIS) does not include a built-in component that can write data to an ODBC destination. To load data into DB2, write a custom script and include it in the SSIS package as a destination component.

Create an SSIS Package and Configure a Connection Manager for the Source Database

  1. Launch the SQL Server Business Intelligence Development Studio.
  2. From the File menu, select New --> Project --> Integration Services Connections Project Wizard.
  3. Specify a project name, location, and select the Create new Solution option. Click OK to start the project wizard.
  4. On the Welcome dialog, click Next.
  5. Choose the Data Provider to be used for a connection to the Source database and click Add.
  6. In the Connection Manager dialog, configure the connection and verify connectivity using the Test Connection button on the same dialog. Click OK to return to the project wizard.
  7. Verify that the newly created connection is listed under the Configured Connection Managers and click Next.
  8. Check the Source box and click Next. Click Finish to create the project.
  9. In the Package.dtsx [Design] tab, click Data Flow to open the Data Flow view.
  10. Right-click the Source component and select Edit.
  11. Select the Data Access Mode and specify the table/view name or the SQL statement to execute.
  12. Choose the columns to include in the data transformation process and set the error handling as desired. Click OK when finished configuring the Source properties.
  13. Leave the Package.dtsx [Design] tab open while you complete the next steps.

Create a Data Connection for the Destination DB2 Database

  1. In the Connections Manager window (below the Design window), right-click in the window and select New Connection.
  2. In the Add SSIS Connection Manager dialog, choose ODBC (ODBC Connection manager for ODBC connections) as the Connection manager type and click Add.
  3. Click the New button to create a new Data connection.
  4. In the Data source specification section, select the StarSQL user or system data source from the drop down list and enter in a valid DB2 userID and password combination. In SQL Server 2008, the wizard does not appear to retain the password. It is therefore necessary to configure the StarSQL DSN with both the User ID and Password. Refer to the StarQuest Technical Document How to Automate StarSQL Logins for instructions.
  5. Verify that the connection works properly using the Test Connection button. Click OK to create the connection manager and click OK once again to save the connection manager.
  6. In the Package.dtsx [Design] tab, click Data Flow to open the Data Flow view.
  7. From the toolbox, locate the Script Component under Data Flow Transformations. Click on it and drag it to the Data Flow view. When prompted to select the Script Component Type, choose Destination and click OK.
  8. In the Data Flow view, connect the Source to the Script Component by dragging the green arrow from the Source until it touches the Script Component box.
  9. Right-click the Script Component and select Edit. Select the Script Language under Custom Properties and set the other properties on this panel as desired.
  10. Under Input Columns, select the columns from the source table that contain data you want to write to DB2.
  11. On the Connection Managers panel, add a new connection manager. For the name of the connection, do not use any spaces or special characters. An example of a valid name is "DB2PROD". You will use this name in the custom script. Specify the Connection Manager (the one created in the previous step) and provide an optional description. Click OK.
  12. Under the Script panel, click the Edit Script button. Write a custom script that loads data from the Source into DB2. For a sample script, refer to the SQL Server 2005/2008 Books Online topic Creating an ODBC Destination with the Script Component.
  13. After the script has been created, save the package. Test the package by right-clicking it under the Solutions Explorer and choosing Execute Package.

DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.