Procedure - Aster Client

Teradata Aster® Client Guide

Product
Aster Client
Release Number
7.00
Published
May 2017
Language
English (United States)
Last Update
2018-04-13
dita:mapPath
hki1475000360386.ditamap
dita:ditavalPath
Generic_no_ie_no_tempfilter.ditaval
dita:id
B700-2005
lifecycle
previous
Product Category
Software
To set up SSIS to use .NET Data Provider for Aster, follow these steps:
  1. Run Microsoft SSIS
  2. Choose File: New > Project to create a new integration project.
  3. In the New Project dialog:
    1. Select Integration Services Project.
    2. Enter your project a name
    3. Click OK.


  4. In the Connection Manager tab at the bottom of the window, add a new Connection.

    To do this, right-click and select New ADO.NET Connection.



  5. In the Configure ADO.NET Connection Manager window, click New.

    In the next couple of steps we’ll create a widget (a database connection definition) that allows SSIS to connect to your Aster Database.

  6. In the Connection Manager dialog, in the Provider drop-down box, choose Aster Data Provider, and click OK.
  7. In the Connection Manager dialog, make the following settings:
    1. Set the Database to the name of the database in Aster Database you want to connect to.
    2. Set the Host to your Aster Database queen IP address.
    3. Set Port to the queen port, which is usually 2406.
    4. Set the User Id and Password to the credentials of a user with sufficient rights on your database in Aster Database.
    5. Click OK. Note the name of the connection definition you are saving. Click OK again.


  8. Choose View > Toolbox.
  9. Drag and drop the Data Flow Task from the Control Flow Items list into the Control Flow designer panel.


  10. Click the Data Flow tab.
  11. From the Data Flow Sources panel, drag an ADO NET Source object into the Data Flow panel.


  12. To make this source a connection to your database in Aster Database, 
double-click ADO NET Source to configure it.

    The ADO.NET Source Editor dialog box appears.

  13. In the ADO.NET Source Editor dialog box, configure the Connection Manager properties:
    1. Click Connection Managers.
    2. From the Data access mode drop-down menu, choose SQL command.
    3. In the SQL command text field, enter a SQL query.


  14. In the ADO.NET Source Editor dialog box, check the Columns property values:
    1. Click Columns.
    2. Check the External Column and Output Column values.


  15. In the ADO.NET Source Editor dialog box, configure the Error Output properties:
    1. Click Error Output.
    2. For each output column, set its Error property to Redirect row.


  16. In the ADO.NET Source Editor dialog box, click OK.
  17. From the Data Flow Destinations panel, drag a Flat File Destination object into the Data Flow panel.


  18. Connect the green arrow of the ADO NET Source object to the Flat File Destination object.


  19. Configure the Flat File Destination object.
    1. Double-click the Flat File Destination object.
    2. In the Flat File Destination Editor, click Connection Manager.
    3. Click New.
    4. In the Flat File Format dialog box, click Delimited.


    5. Click OK.
    6. In the Flat File Connection Manager Editor dialog box, in the Connection manager name field, enter the name of the output file of the connection manager.


    7. Click Browse.
    8. Select an output file and click Open.
    9. Click OK.
    10. In the Flat File Destination Editor, click Mappings.
    11. Set the correct column mappings for the Flat File Destination object.


    12. Click OK.
  20. From the Data Flow Destinations panel, drag a Flat File Destination object into the Data Flow panel.

    This object is the destination for all error records.

  21. Connect the error output (red arrow) of the ADO NET Source object to Flat File Destination you just added.


  22. Create another Flat File Connection Manager for the new Flat File Destination object that serves as the error destination.
    1. Double-click the Flat File Destination object.
    2. In the Flat File Destination Editor, click Connection Manager.
    3. Click New.
    4. In the Flat File Format dialog box, click Delimited.
    5. Click OK.
    6. In the Flat File Connection Manager Editor dialog box, in the Connection manager name field, enter the name of the output file to be used to store all error records.
    7. Click Browse.
    8. Select an output file and click Open.
    9. Click OK.
    10. In the Flat File Destination Editor, click Mappings.
    11. Set the correct column mappings for the error destination.


    12. Click OK.
  23. Save the project.
  24. Run the project with debugging (Debug > Start Debugging).


    You can check the progress of the workflow in the Progress panel.



    When the export is successful, the flat file source and destination objects in the Data Flow pane turn green.



    If the export is not successful, the ADO NET Source object turns red. For example, you might get an exception like “Error: 0xC0047062 at Data Flow Task, ADO NET Source [16]: System.ArgumentException: Error loading assembly: C:\Program Files (x86)\Teradata Aster\Aster .NET Data Provider (x86)\AsterDataC#DSII.dll.”



    In this case, configure the 32-bit Runtime in SSIS for the project or you install the 64-bit NClusterDNProvider.

    The Microsoft SQL Server Business Intelligence Studio (BIDS) is a Visual Studio plug in. For SQL Server 2008, this edition is 32-bit only. This requires using 32-bit drivers, including for Aster Database.

    However, in the 64-bit versions of Windows, there is a project flag that you must set to allow 32-bit drivers to operate. Select the project, right-click, and choose Properties. Then, set Run64BitRunTime to False. If not, you get an architecture mismatch and other connection errors.



    Similarly, to run the package outside of BIDS—such as running a SQL Server Agent job or running the package by itself—click the tab Execution options and check the check box Use 32 bit run time.