Flat files can be loaded into RED from a Windows Connection using SQL Server Integration Services (SSIS).
The instructions below detail how to add the SSIS connection string to the data warehouse connection and load flat files using the drag and drop functionality to create Load tables. To load files via SSIS, the SSIS connection string must be defined in the DataWarehouse connection.
To use SSIS loading, ensure that SSIS loads are enabled and that the SSIS version is set correctly in Tools > Options > Code Generation > General.

  1. To load files via SSIS, the SSIS connection string must be defined in the DataWarehouse connection for the Destination connection to be specified:
    • Double-click the DataWarehouse connection in the object explorer to open up the Properties window.
    • Click the ellipsis button to open the wizard and define the SSIS connection string.
  2. On the Provider tab, select the relevant OLE DB Provider and click Next.
  3. On the Connection tab, select the server name, enter the information to log in to the server and select the database on the server. Click Test Connection.

    Note

    When using a specific user name and password to connect to the server instead of using Windows integrated security, the Allow saving password check-box must be selected. It is also recommended that the password on the SSIS connection string field that is displayed in the connection properties is replaced with the $PASSWORD$ token that is substituted at runtime.


  4. Click OK.
  5. Click OK on the Data Link Properties dialog to save the SSIS connection string settings.
  6. Click OK on the Data Warehouse connection properties screen.
  7. Browse to the directory and file from the Windows connection.
  8. Double-click on the Load Table object in the left pane to create a drop target.
  9. Drag the file from the right pane and drop it into the middle pane. The window below appears.
     
  10. Click the ADD button, the following screen appears:
     
  11. Configure the settings and click OK to continue.  See the previous section for details about these settings.

    Note

    • DB2 databases do not support data import from files with header row.
    • The Decimal Code button shows the decimal value of each character in the lines retrieved from the source file. These decimal codes are shown in green below each line.
  12. The next screen enables you to breakdown the source data into columns. If no delimiter is entered then width based parsing is assumed and an addition width size is prompted.  The following screen is an example of the file parsing technique.
     
    Use the Back button to revert to the previous column if an incorrect width or delimiter is entered.
  13. On the Properties screen for the new load table, select Integration Services Load as the Load Type. Click OK.This will create and execute a SSIS package at run time to load data into the data warehouse load table.

    Note

    • If the table is changed to an Integration Services load and has been set up using the wizard for the "File load (columns parsed)" flow, some columns might have transformations set up that will not work.
    • In RED 6.8.3.4 date/time fields have transformations that are invalid for SSIS and will make the load fail. Since SSIS does not provide any configuration for the parsing of date/time fields, if users have any date/time field special requirements, file or script-based loads provide are a better load option instead.
  14. Click Yes to Create and Load the table.


  • No labels