Business Intelligence Development Studio (SSIS) and U2 Toolkit for .NET v.2.2.2 with a Native Connection


Microsoft SQL Server Integration Services (SSIS) is a platform for building high performance data integration solutions, including extraction, transformation, and load (ETL) packages for data warehousing. It can transfer UniData or UniVerse data to SQL Server very easily. If you use a uci connection to access a UniData or UniVerse server, you must normalize your account first with proper schema and permission settings. The U2 Toolkit client also provides a native connection option that does not require normalization. However, the result data set is very different from a uci connection. The users might need to make some adjustment for SQL Server tables.

In this blog post, you will learn how to use Visual Studio (VS) 2017 SSIS Designer using .NET Framework 4.6 to create a simple Microsoft SQL Server Integration Services package. The sample package will export UniData file data into a SQL Server table using a native connection.


You need to register your U2 Toolkit for .NET driver with VS 2017. If you do not find U2 Toolkit for .NET Provider 4.6 in the .NET Providers list, you need to reinstall the 32-bit and 64-bit U2 Toolkit providers or use the gacutil tool to register the driver.

In the VS 2017 Configure ADO.NET Connection manager, the U2 Toolkit driver should be on the list.

When using the “gacutil /l U2.Data.Client” command in the VS Developer Command Prompt, it should see eight (8) U2.Data.Client list in the Global Assembly Cache.


Rocket MultiValue Application Platform Version
UniData 7.3 or later
UniVerse 11.2 or later


Microsoft Version
Visual Studio 2017

Professional or Enterprise

SQL Server Data Tools


SQL Server 2014 or higher

Create a SQL Server Integration Services (SSIS) Package

Create an SSIS package based on the U2 database (in this blog post I’m using the UniData/demo database) using a U2 Toolkit native connection. In this tutorial, you will learn how to create an SSIS project, set up U2 connection information, define a SQL statement in the command field, create a SQL Server destination table, map the fields, and transfer data to SQL Server.

Step 1: Creating a SQL Server Integration Services Project

First you need to create an SSIS project where you will save your definition (Package.dtsx) file and any other resource files that you need to run the SSIS package.

When you run the SSIS package, the actual UniData data is retrieved and combined with the layout, and then transferred to a SQL Server table. In this lesson, you will learn how to create an SSIS project in the SQL Server Data Tools (SSDT).

To create an SSIS project

  1. Start Visual Studio 2017 from program menu or from

“C:\Program Files (x86)\Microsoft Visual Studio\2017\Professional or Enterprise\Common7\IDE\devenv.exe”.

  1. On the File menu, click New > Project.
  2. In the Installed Templates list, click Business Intelligence.
  3. Click Integration Services.
  4. In the Name field, enter U2_SSIS_Tutorial.
  5. Click OK to create the project.

Step 2: Creating Connection Information to U2 and SQL Server (SSIS)

You need to define an ADO.NET source connection, which accesses UniData data in native mode. You will use the U2 database (UniData/Demo) sample database as your source data. It also needs another ADO.NET destination connection to SQL Server 2016.

In the Connection Managers pane, you can right-click to create a new ADO.NET connection. When clicking on the “New” button, you can choose the “U2 Toolkit for .NET Provider 4.6” from the .Net Providers list…








to set up a UniData connection in native mode.

You need to change the AccessMode parameter from “Uci” to “Native” and the RpcServiceType from “defcs” to “udcs” for UniData or “uvcs” for UniVerse.

Parameter Value
AccessMode Native
RpcServiceType udcs
ServerType UniData
Password ******
UserID administrator
Database demo
Server localhost

You should see the “succeeded” message, if you click on the “Test Connection” button.

You need to create another SQL Server connection using SqlClient Data Provider.

Step 3: Creating Data Flow Task with AO.NET connections (SSIS)

Drag a Data Flow Task to the Package.dtsx design pane.






In the Data Flow task, drag an ADO.NET Source and an ADO.NET Destination to the pane, then the ADO.NET source will link to the ADO.NET Destination.









Click on the “ADO NET Source” object to select the “localhost.demo.admin” connection as defined in step 2. In Data access mode, it will be set to “SQL command”; next input the “SELECT * FROM STUDENT” SQL statement in the SQL command text field.

Note: If the file is with some virtual fields, the dictionary must be compiled first.

For the “ADO NET Destination” object, you select another “localhost.ud_database” connection as defined in the step 2. It can create a new SQL Server destination table on the second “New” button.

You might modify the table name and some other data type information.















Please click on the “Mappings” option to see the fields mapping.

Step 4: Run the package to migrate UniData data to SQL Server (SSIS)

When you run the DTS package, it shows 28 rows to SQL Server based on the original 6 STUDENT records that will expand multi-value and sub-value data to different rows.
















Step 5: Verify conversion data on SQL Server (SSIS)

In the SQL Server table, it will generate additional Z_MV_KEY and Z_MS_KEY to identify MV & MS position. The first record is expanded to 6 rows.



Paul Chang 10 Posts

Paul Chang is a Principal Technical Support Engineer at Rocket Software. He is responsible for supporting all U2 common middleware client products, Java products and .NET clients. He has 35 years of experience in computing and holds a master’s degree in Computer Science with an emphasis on Relational Database Design.


  • Lawrence Reply

    June 27, 2018 at 9:46 am

    Would there be any articles or references for the use of a “modern ETL platform” connection to U2?

    Reference article

    • Paul Chang Reply

      July 2, 2018 at 7:43 am

      U2 does not provide with ETL tool. But, you can create an SSIS project to convert data as you need using native or UCI connection.

  • Sara Willson Reply

    July 2, 2018 at 12:36 am

    I used to search information related to Business Intelligence development studio and here in this article i got the complete and fully descriptive information about that.

  • Yan Reply

    December 13, 2018 at 4:53 pm

    I am new to Unidata. I installed U2 Toolkit for .NET, and register the driver, I only have 6 U2.Data.Client in GAC

    C:\Program Files (x86)\Microsoft Visual Studio 14.0>gacutil /l U2.Data.Client
    Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.0
    Copyright (c) Microsoft Corporation. All rights reserved.

    The Global Assembly Cache contains the following assemblies:
    U2.Data.Client, Version=, Culture=neutral, PublicKeyToken=7afaaf959fd491a0, processorArchitecture=AMD64
    U2.Data.Client, Version=, Culture=neutral, PublicKeyToken=7afaaf959fd491a0, processorArchitecture=MSIL
    U2.Data.Client, Version=, Culture=neutral, PublicKeyToken=9ab9148678f4f448, processorArchitecture=AMD64
    U2.Data.Client, Version=, Culture=neutral, PublicKeyToken=e2387a1682b4f882, processorArchitecture=AMD64
    U2.Data.Client, Version=, Culture=neutral, PublicKeyToken=9ab9148678f4f448, processorArchitecture=MSIL
    U2.Data.Client, Version=, Culture=neutral, PublicKeyToken=e2387a1682b4f882, processorArchitecture=MSIL

    Number of items = 6

    My issue is: the U2 Toolkit Driver does not display on the ADO.NET Connection Manager.

    Any thoughts or suggestions?

    Really appreciate your guidance.


    • Kathy Larson Reply

      January 7, 2019 at 7:50 am

      Hi Yan, I checked with Paul and here’s his reply:
      are running old U2 Toolkit version 2.2.0 that does not support Visual Studio 2015. It should be updated to U2 Toolkit Developer to support VS 2015 and 2017.

      Here is the sample output for the U2 Toolkit driver.

      C:\Program Files (x86)\Microsoft Visual Studio 14.0>gacutil /l
      Microsoft (R) .NET Global Assembly Cache Utility. Version 4.0.30319.0
      Copyright (c) Microsoft Corporation. All rights reserved.

      The Global Assembly Cache contains the following assemblies:, Version=, Culture=neutral, PublicKeyToken=7afaaf959fd491a0, processorArchitecture=AMD64, Version=, Culture=neutral, PublicKeyToken=7afaaf959fd491a0, processorArchitecture=MSIL, Version=, Culture=neutral, PublicKeyToken=20e25e560a20d05b, processorArchitecture=AMD64, Version=, Culture=neutral, PublicKeyToken=9ab9148678f4f448, processorArchitecture=AMD64, Version=, Culture=neutral, PublicKeyToken=e2387a1682b4f882, processorArchitecture=AMD64, Version=, Culture=neutral, PublicKeyToken=20e25e560a20d05b, processorArchitecture=MSIL, Version=, Culture=neutral, PublicKeyToken=9ab9148678f4f448, processorArchitecture=MSIL, Version=, Culture=neutral, PublicKeyToken=e2387a1682b4f882, processorArchitecture=MSIL

      Number of items = 8

      Hope this helps.

  • Paul K Reply

    April 11, 2019 at 5:10 am

    I am attempting to use this in SSDT 15.1.61804.210 in Visual Studio 2017. I have set up the connection as described and am trying to do a select on a test file “SELECT * FROM PKTEST”. I get the error message:

    An error occurred executing the provided SQL command: “SELECT * FROM PKTEST”. ERROR [] [U2] [UCINET-UO] SELECT * FROM PKTEST?
    Message:Incorrect syntax near ?.
    Source:U2.Data.Client TargetSite:U2.Data.Client.NativeAccess.TableInfoForNativeAccess GetTableInformation(U2.Data.Client.U2Command)

    I also had problems with an UCI connection initially and had to end a semicolon to the end of the ADO.NET SQL command otherwise it errored with “An error occurred executing the provided SQL command: “SELECT ID FROM PKTEST_SQL_DW”. [U2][UCINET-UO] [U2][UCINET][UNIDATA]: pKey:SS_SERVER pServerCode:5365440SELECT ID FROM PKTEST_SQL_DW?;”

    Has anybody else experienced this? Based on the errors it seems to be introducing a question mark (?) to the sql statement for some reason.

    • Kathy Larson Reply

      April 11, 2019 at 9:53 am

      For the U2 Toolkit UniData native connection, it does not support the SQL statement directly. When you run the same “SELECT * FROM PKTEST” command on UniData shell, it will return the same syntax error.

      There are several options for the U2 Toolkit UCI connection. It might change the U2 Toolkit client parser to return the different message. Any SQL statement can be verified on UniData SQL prompt. In the U2 Toolkit client log, it provides with more logging information for the issue.

  • Jared S. Reply

    December 10, 2019 at 2:48 pm

    I’m pretty new to U2 but as i was following along with the article after i registered the assemblies in the GAC, the U2 toolkit for .Net still didn’t show up as an ADO.NET provider in visual studio (i.e. the dropdown menu). Do we have to manually configure the connection via a config file?
    Any thoughts or suggestions would be greatly appreciated.


  • Kathy Larson Reply

    January 2, 2020 at 8:58 am

    Hi Jared, you need to request a U2 Developer license and license it. Once the U2 Toolkit driver has been licensed, you will be able to see the ADO.NET provider in visual studio server explorer.

    • Jason C. Reply

      March 10, 2020 at 1:48 pm

      Hi Kathy. How and where can we request a U2 Developer license? Thank you.

  • Michael Greenberg Reply

    March 2, 2020 at 6:53 am

    I would like to be able to run a Unidata Paragraph from SSIS. A Unidata Paragraph is essentially a collection of Unidata commands that can be run as a batch. Using SSIS Visual Studio 2017 and the “U2 Toolkit for .NET Provider 4.6” I am able to create an ADO.NET connection to a Unidata database and set up an ADO.NET data source in SSIS to read a Unidata table. However, when I try to create an Execute SQL task the Unidata connection is not available. I was hoping to be able to execute a Unidata Paragraph similar to how a SQL Server Stored Procedure is executed.

    • Kathy Larson Reply

      March 2, 2020 at 10:34 am

      Hi Michael, here’s a reply from the blog post’s author, Paul Chang: For SQL Server SSIS solution, it requires the SQL schema and result set output. We only support the “SELECT” statement against the UniData server with a U2 Toolkit native connection. It is not easy to get SQL result & schema set using the UniData Paragraph.

Leave a Comment

Your email address will not be published.