Business Intelligence Development Studio (SSIS) and U2 Toolkit for .NET v.2.1.0

• by Rocket Software
• 13 Comments
• September 4, 2014

Overview

Microsoft positions its Business Intelligence Development Studio as the environment used to develop packages for data extraction, transformation, and loading (ETL) in Integration Services (SSIS). SSIS includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as FTP operations, executing SQL statements, and sending e-mail messages; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management service, the Integration Services service for administering package execution and storage; and application programming interfaces (APIs) for programming the Integration Services object model.

In this tutorial, based on the Microsoft Developer Network (MSDN) article “SSIS Tutorial: Creating a Simple ETL Package”, you will learn how to use the SSIS Designer to create a simple Microsoft SQL Server Integration Services package. The package that you create takes data from a U2 Database, reformats the data, and then inserts the reformatted data into a SQL Server table. For convenience we have included the instructions here, modified to use U2 Toolkit and access data in Rocket UniData or Rocket UniVerse. See below for links to the original Tutorial and to Rocket Software’s Github repository for the Rocket U2 servers lab to find our sample code.

In this tutorial, you will learn how to use SSIS Designer to create a simple Microsoft SQL Server Integration Services package. The package that you create takes data from a U2 Database, reformats the data, and then inserts the reformatted data into a SQL Server table

U2 Toolkit for .NET v2.1.0 supports:

• SQL Access
• Native Access

U2 Toolkit for .NET v2.1.0 is Connection Manager in SSIS Project.

Requirements

Creating the Project and Basic Package

This sample code will show how to create a simple ETL package that extracts data from a U2 Database source and writes that data to the SQL Server Table. It will show how to create new packages, add and configure data source and destination connections, and work with new control flow and data flow components.

Step 1: Creating a New Integration Services Project

The first step in creating a package in Integration Services is to create an Integration Services project. This project includes the templates for the objects — data sources, data source views, and packages — that you use in a data transformation solution.

To create a new Integration Services project

1. On the Start menu, navigate to All Programs > Microsoft SQL Server > SQL Server SQL Server Data Tools.
2. On the File menu, select New > Project to create a new Integration Services project.
3. In the New Project dialog box, select Integration Services Project template.
4. In the Name box, change the default name to SSIS Tutorial. Optionally, clear the Create directory for solution check box.
5. Accept the default location, or click Browse to browse to locate the folder you want to use. In the Project Location dialog box, click the folder and click Select Folder.
6. Click OK.
7. By default, an empty package, titled Package.dtsx, will be created and added to your project.

The new project screen contains the following:

• Tool Box on the left-side navigation bar
• Solution Explorer in the top-right navigation bar
• Property Window in the lower-right navigation bar
• Package Explorer tabs where you can control flow, data flow, and event handlers
• Connection Manager located at the bottom of the window

Step 2: Adding and Configuring a ADO.NET Connection Manager (U2 Source)

In this task, add an ADO.NET connection manager to the package that you just created. The ADO.NET connection manager enables a package to extract data from any ADO.NET source such as U2 database. Using the ADO.NET connection manager, you can specify User, Password, Server, Database, ServerType, and AccessMode.

If the AccessMode is set to Uci, it uses U2 Toolkit for .NET SQL Access.
If the AccessMode is set to Native, it uses U2 Toolkit for .NET Native Access.

To add a ADO.NET connection manager to the SSIS package

Right-click anywhere in the Connection Managers area, and then click New ADO.NET Connection.

In Connection Manager, select U2 Toolkit for .NET Provider

Enter connection string parameters. This requires you to enter the following information: User, Password, Server, Database, ServerType, and AccessMode, and to set the PersistSecurityInfo parameter to True.

Test Connection

You will see ADO.NET Connection Manager (U2 Database) in Connection Managers Pane.

Step 3: Adding and Configuring a ADO.NET Connection Manager (SQL Server Destination)

To add and configure an ADO.NET Connection Manager

1. Right-click anywhere in the Connection Managers area and then click New ADO.NET Connection.
2. In the Configure ADO.NET Connection Manager dialog box, click New.
3. Select “SqlClient Data Provider” from Provider combo box.
   1. For the Server name, enter .\SQLEXPRESS.
   2. For the Database name, select ‘tempdb’.

Test the connection

You will see ADO.NET Connection Manager (Destination) in Connection Managers Pane.

Step 4: Adding a Data Flow Task to the Package

After creating the connection managers for the source and destination data, the next task is to add a Data Flow task to your package. The Data Flow task encapsulates the data flow engine that moves data between sources and destinations, and provides the functionality for transforming, cleaning, and modifying data as it is moved. The Data Flow task is where most of the work of an extract, transform, and load (ETL) process occurs.

To add a Data Flow task

1. Click the Control Flow tab.
2. In the Toolbox, expand Control Flow Items and drag a Data Flow Task onto the design surface of the Control Flow tab.
3. On the Control Flow design surface, right-click the newly added Data Flow Task, click Rename, and change the name to “U2 Database TO SQL Server”.

Step 5: Adding and Configuring the ADO.NET Source

In this task, add and configure an ADO.NET source to your package. The ADO NET source consumes data from a .NET provider and makes the data available to the data flow. You must configure the ADO.NET source to use the U2 Database ADO.NET Source Data connection manager that you previously created.

To add a ADO.NET Source component

1. Open the Data Flow designer, either by double-clicking the U2 Database TO SQL Server data flow task or by clicking the Data Flow tab.
2. In the Toolbox, expand Data Flow Sources, and then drag an ADO.NET Source onto the design surface of the Data Flow tab.
3. On the Data Flow design surface, right-click the newly added ADO.NET Source, click Rename, and change the name to Extract Customer Table.
4. Double-click the ADO.NET source to open the ADO.NET Source Editor dialog box.
5. Set the Data Access Mode as SQL Command.
6. Enter the following SQL command text: SELECT * FROM CUSTOMER.

Click Preview to preview the data.

Step 6: Adding and Configuring the ADO.NET Destination

Your package now can extract data from the ADO.NET source. The next task is to actually load the transformed data into the destination. To load the data, you must add an ADO.NET destination to the data flow.

To add and configure the sample ADO.NET destination

1. In the SSIS Toolbox, expand Other Destinations, and drag an ADO.NET Destination onto the design surface of the Data Flow tab. Place the ADO.NET Destination directly below the Extract Customer Table.
2. Click Extract Customer Table and drag the green arrow over to the newly added ADO.NET Destination to connect the two components together.
3. On the Data Flow design surface, click ADO.NET Destination in the newly added ADO.NET Destination component, and change the name to Sample ADO NET Destination.

1. Double-click Sample ADO.NET Destination.
2. In the ADO.NET Destination Editor dialog box, ensure that SQLEXPRESS is selected in the ADO.NET Connection manager box.
3. Click New and then accept all defaults.

1. Click Preview. You will see an empty table. Close it.
2. Click Mapping. Verify that all columns are mapped correctly.
3. Close the ADO.NET Destination Editor.

Step 7: Testing the Package

To run the package

1. On the Debug menu, click Start Debugging. The package will run, resulting in some rows successfully added into the Sample ADO NET Destination fact table in “tempdb”.
2. After the package has completed running, on the Debug menu click Stop Debugging. The results should look similar to the following image.

Resources

• SSIS Tutorial: Creating a Simple ETL Package

Source Code

• See Github Rocket U2 Server Lab

Appendix A

• BIDS
• SSIS
• U2 Toolkit for .NET v2.1.0
• UniData Database
• UniVerse Database