• August 21, 2018

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
  1. In VS2012, open C:\Program Files\Microsoft SQL Server\110\DTS\ProviderDescriptors\ ProviderDescriptors.xml file.
  2. Add U2Connection entry. For example (See Appendix A)

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.
NewProject
NewProject

 

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
Project_Layout
Project_Layout

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.

New_ADONET_CM

In Connection Manager, select U2 Toolkit for .NET Provider

U2ProviderDS

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.

EnterConnParams

Test Connection

TestConnection

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

ConnManagerPane1

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’.

DestinationEditor

Test the connection

TestConnection

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

ConnManagerPane2

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”.

DataFlow

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.

 

ADONET_Source

 

SourceEditor

Click Preview to preview the data.

SourceEditor_Preview

 

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.

ADONET_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.

DestinationEditor

 

 

CreateTable

 

  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.

Source_Destination

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.

Test_Run

 

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=”&quot;”

SuffixQualifier=”&quot;”

CatalogSeparator=”.”

SchemaSeparator=”.”

/>

</dtm:ProviderDescriptor>

Contact us to learn more about Rocket MultiValue Solutions.
Rocket Software

Rocket Software 107 Posts

Thousands of companies around the world depend on Rocket to solve their most challenging business problems by helping them run their critical infrastructure, business processes, and data, as well as extending the value of these assets to take advantage of cloud and mobile computing, advanced analytics, and other future innovations.

13 Comments

  • gbrunton Reply

    September 5, 2014 at 5:26 pm

    Interesting.  How would this handle multivalue or sub-multivalue data if it existed in the Customer source table?

  • brindeymarine Reply

    September 9, 2014 at 10:22 am

    gbrunton 
    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 Reply

    September 10, 2014 at 3:27 pm

    brindeymarine 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 Reply

    September 11, 2014 at 8:52 am

    Thank 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.
    screenshot

    raj

  • Rajan Kumar Reply

    September 15, 2014 at 11:49 am

    gbrunton 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)... Reply

    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 Reply

    May 21, 2015 at 5:10 am

    I 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 Reply

      May 21, 2015 at 11:06 am

      Hi 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 Reply

      October 26, 2015 at 3:36 pm

      Hi,

      Do you know how to extract multivalue data from universe using the ado.net connection as above?

  • Rajan Kumar Reply

    May 21, 2015 at 11:30 pm

    Hi 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 Reply

    May 26, 2015 at 11:27 am

    Hi 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 Reply

    May 26, 2015 at 1:03 pm

    Thanks 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 Reply

    November 6, 2015 at 9:13 am

    How would one execute a BASIC program from SSIS?

Leave a Comment

Your email address will not be published. Required fields are marked *