Business Intelligence Development Studio (SSIS) and U2 Toolkit for .NET v.2.1.0
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
U2 | Version |
UniVerse | 10.3 or later |
UniData | 7.1 or later |
Microsoft | Version |
Visual Studio | – 2010 SP1– 2012 Update 4
– 2013 Update 2 RC
|
Business Intelligence Development Studio (BIDS)(SSIS and SSRS) | – BIDS 2010– BIDS 2012
|
Configure ProviderDescriptors.xml file |
|
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
- On the Start menu, navigate to All Programs > Microsoft SQL Server > SQL Server SQL Server Data Tools.
- On the File menu, select New > Project to create a new Integration Services project.
- In the New Project dialog box, select Integration Services Project template.
- In the Name box, change the default name to SSIS Tutorial. Optionally, clear the Create directory for solution check box.
- 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.
- Click OK.
- 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
- Right-click anywhere in the Connection Managers area and then click New ADO.NET Connection.
- In the Configure ADO.NET Connection Manager dialog box, click New.
- Select “SqlClient Data Provider” from Provider combo box.
- For the Server name, enter .\SQLEXPRESS.
- 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
- Click the Control Flow tab.
- In the Toolbox, expand Control Flow Items and drag a Data Flow Task onto the design surface of the Control Flow tab.
- 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
- 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.
- In the Toolbox, expand Data Flow Sources, and then drag an ADO.NET Source onto the design surface of the Data Flow tab.
- On the Data Flow design surface, right-click the newly added ADO.NET Source, click Rename, and change the name to Extract Customer Table.
- Double-click the ADO.NET source to open the ADO.NET Source Editor dialog box.
- Set the Data Access Mode as SQL Command.
- 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
- 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.
- Click Extract Customer Table and drag the green arrow over to the newly added ADO.NET Destination to connect the two components together.
- 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.
- Double-click Sample ADO.NET Destination.
- In the ADO.NET Destination Editor dialog box, ensure that SQLEXPRESS is selected in the ADO.NET Connection manager box.
- Click New and then accept all defaults.
- Click Preview. You will see an empty table. Close it.
- Click Mapping. Verify that all columns are mapped correctly.
- Close the ADO.NET Destination Editor.
Step 7: Testing the Package
To run the package
- 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”.
- After the package has completed running, on the Debug menu click Stop Debugging. The results should look similar to the following image.
Resources
Source Code
Appendix A
<dtm:ProviderDescriptor SourceType=”U2.Data.Client.U2Connection”><dtm:SchemaNamesTablesSchemaName=”Tables”
ColumnsSchemaName=”Columns” ViewsSchemaName=”Views” />
<dtm:TableSchemaAttributes TableCatalogColumnName=”TABLE_CAT” TableSchemaColumnName=”TABLE_SCHEM” TableNameColumnName=”TABLE_NAME” TableTypeColumnName=”TABLE_TYPE” TableDescriptor=”TABLE” ViewDescriptor=”VIEW” SynonymDescriptor =”SYNONYM” NumberOfTableRestrictions=”3″ />
<dtm:ColumnSchemaAttributes NameColumnName = “COLUMN_NAME” OrdinalPositionColumnName=”ORDINAL_POSITION” DataTypeColumnName = “DATA_TYPE” MaximumLengthColumnName = “COLUMN_SIZE” NumericPrecisionColumnName = “PRECISION” NumericScaleColumnName = “SCALE” NullableColumnName=”IS_NULLABLE” NumberOfColumnRestrictions=”4″ />
<dtm:Literals PrefixQualifier=”"” SuffixQualifier=”"” CatalogSeparator=”.” SchemaSeparator=”.” /> </dtm:ProviderDescriptor> |
13 Comments
gbrunton
September 5, 2014 at 5:26 pmInteresting. How would this handle multivalue or sub-multivalue data if it existed in the Customer source table?
brindeymarine
September 9, 2014 at 10:22 amgbrunton
U2 Toolkit for .NET is multi-value ADO.NET Provider.
It supports Native Access.
You do not need to normalize the account.
When you specify “AccessMode=Native”, we use Native UO
protocol. Please read U2 Toolkit for .NET 2.1.0 documentation.
In your command prompt , you can specify
SELECT ID, SVField1, SVField2, MVField1, MVField2 FROM
MYFILE
SELECT ID, MVField1, MVField2 FROM MYFILE
When you specify “AccessMode=Uci”, we use UCI
protocol. Please read U2 Toolkit for .NET 2.1.0 documentation.
In this case , you need to normalize your account with VSG
(Unidata) or HS.ADMIN (UniVerse)
Please contact us if you
have more questions.
Regards,
Rajan Kumar
gbrunton
September 10, 2014 at 3:27 pmbrindeymarine gbrunton
Thanks for your reply. I’m a little confused though. Your saying that I can connect using AccessMode=Native and use Sql syntax?
brindeymarine
September 11, 2014 at 8:52 amThank you for reading the article and asking this question.
In March, 2013, BIDS is renamed to SQL Server Data Tools – Business Intelligence (SSDT-BI). So for VS2012 and VS2013 , you have to download SSDT-BI. This will allow you to develop and deploy SQL Server Business intelligence project using VS2012 and VS2013. If you have VS2010 Project, you can open with VS2012.
VS2010 supports BIDS. You need to install SQL Server 2010 with BIDS features (unlike SSDT-BI). Then you can see BIDS Template in VS2010. See the linked screen shot VS2010 and BIDS.

Rajan Kumar
September 15, 2014 at 11:49 amgbrunton brindeymarine
Yes.
Underneath we have SQL Parser ( .NET Framework API ‘Microsoft.Data.Schema.ScriptDom’
and ‘Microsoft.Data.Schema.ScriptDom.Sql’) in U2 Toolkit for .NET v2.1.0. We
parse the SQL Syntax to extract File Name, Field Names, Where Clause, Sort
Clause etc. Then we call UniObjects’s Class UniFile for CRUD operations (read
and write). We generate internally RecordIDs based on your ‘Where Clause’ in
one server trip. You will see big performance improvement.
Please try these sample code:
UniVerse : C:Program Files (x86)\Rocket Software\U2 Toolkit
for .NET\U2 Database Provider\samples\C#\UniVerse\NativeAccess
UniData : C:Program Files (x86)Rocket SoftwareU2 Toolkit
for .NETU2 Database ProvidersamplesC#UniDataNativeAccess
You can download FREE U2 Toolkit for .NET Provider from
https://u2tc.rocketsoftware.com/matrix.asp
If you want Chargeable U2 Toolkit for .NET Developer , you need to contact
U2BC.
Regards,
Rajan
Business Intelligence Development Studio (SSIS)...
September 18, 2014 at 6:37 am[…] 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). […]
Paul
May 21, 2015 at 5:10 amI have downloaded and installed the U2 Toolkit for .NET Provider version 2.2.0 and am trying to use it with SSDT for Visual Studio 2013 with SQL Server 2014 on Windows Server 2008 R2.
When I run the Configuration File Editor it only displays the rsreportserver.config and rssrvpolicy.config files in the dropdown. I have tried to edit the ProviderDescriptors.xml file manually in both C:\Program Files\Microsoft SQL Server\120\DTS\ProviderDescriptors and C:\Program Files (x86)\Microsoft SQL Server\120\DTS\ProviderDescriptors with the code in Appendix A but I am still not getting the U2 Toolkit for .NET Provider to appear under .NET Providers in the connection manager.
Is the text in Appendix A suitable for version 2.2.0? Is there a reason why the Configuration File Editor is not picking up the files so that it can update them automatically?
Thanks for any assistance you can give.
Rajan Kumar
May 21, 2015 at 11:06 amHi Paul
It looks like you are trying SSIS (NOT SSRS).
For your information :
– ProviderDescriptors.xml file is not required for SSIS Connection Manager. It is required when you run the package.
– rsreportserver.config and rssrvpolicy.config files are required for SSRS.
We have not seen this issue before. Could you please send me
– Log File (https://github.com/RocketSoftware/multivalue-lab/tree/master/U2/Demos/U2-Toolkit/Log%20File)
– Output of GACUTIL.EXE /L U2.Data.Client
– Output of C:\Program Files (x86)\Rocket Software\U2 Toolkit for .NET\U2 Database Provider\version\version.xml
Please also contact u2support@rocketsoftware.com to create U2 Support Issue.
Regards,
Rajan Kumar
Kathleen
October 26, 2015 at 3:36 pmHi,
Do you know how to extract multivalue data from universe using the ado.net connection as above?
Rajan Kumar
May 21, 2015 at 11:30 pmHi Paul,
Make sure you install 32-bit U2 Toolkit for .NET Provider.
At Design Time, VS2013 IDE is 32-bit so it is looking for 32-bit U2 Toolkit for .NET Provider.
At Run Time, you can choose 64-bit or 32-bit.
Regards,
Rajan
Paul
May 26, 2015 at 11:27 amHi Rajan,
Yes you are correct I am using SSIS not SSRS.
I tried installing the previous version (v2.1.0) and that installed ok. I am pleased to say I have now also been successful with v2.2.0 after running the autorun.exe installer using the “Run as administrator” option (which I was not doing previously).
Fopr what it’s worth I am still only seeing the two SSRS files using the Configuration File Editor despite the fact it suggests it should pick up the SSIS one as well. This isn’t a problem as I have edited ProviderDescriptors.xml manually but thought I would mention it. The only thing non-standard about my installation I can think of is that ProviderDescriptors.xml is installed on the D drive and not the C drive.
I have previously used the UniOLEDB driver (now discontinued I believe) with VSG and SSIS 2005 and we are looking to upgrade to SQL Server 2014. I am currently using a test server and had found some problems with the UniOLEDB driver using an OLE DB source in SSDT 2013 (some join statements and date selections work in preview mode but strangely not when running the package) so am trying this as an alternative. It looks like there are some speed improvements although I have found some niggles so far as well (being forced to use an ADO.NET connection manager means everything comes out as a Unicode string rather than non-Unicode which means I will have to convert all strings, and when using “SELECT [columnname] as [alias]” syntax the column names always come through as “Column0”, “Column1” etc rather than picking up the alias name like the UniOLEDB driver did).
Rajan Kumar
May 26, 2015 at 1:03 pmThanks for the feedback.
So now you can see U2 Toolkit for .NET in ADO.NET Connection Manager.
If you need help with this question
““SELECT [columnname] as [alias]” syntax the column names always come through as “Column0″, “Column1″ etc rather than picking up the alias name like the UniOLEDB driver did”
Please create U2 Support Bug Issue.
Regards,
Rajan
Omid Golban
November 6, 2015 at 9:13 amHow would one execute a BASIC program from SSIS?