To use SSIS loading, ensure that SSIS loads are enabled and that the SSIS version is set correctly in Tools > Options > Code Generation > General.
Refer to Loading Data from a Flat File using SSIS for details, if you are loading data from a Flat File source.

RED can extract and load data using SSIS from database tables or flat files from a Windows connection. As with any load into RED, a connection to the source data needs to be created to provide extraction details.
The SSIS Connection String is a valid SSIS connection string that can be used to connect to the data source or destination.

Loading Data via SSIS from a database

If the connection is a database load, then there is additional connection information that should be supplied to use SSIS as a loading option. 
This additional information needs to be supplied on both the source connection and the data warehouse connection.

Creating the SSIS Connection String

  1. Click the ellipsis button in the right side of the SSIS Connection String field of the relevant connection:
  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 on 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 checkbox 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 in the Data Link Properties window to save the SSIS connection string settings.
  6. The SSIS connection string is displayed.
  7. Click OK to save the connection.
    • Right-click Sales SSIS and select Browse Connection.
    • Accept the defaults and click OK.
  8. In SSIS terms, you have now defined your Source in SSIS Connection Manager.Using the same process, you need to add the SSIS Connection String to the data warehouse connection so that you can specify your Destination connection:
    • Double-click the DataWarehouse connection in the object explorer to open up the Properties screen.
    • Follow the process above to create the SSIS Connection String.
    • Click OK to save your connection.

    Note

    • If the connection string is already set, then the ellipsis button will open up an editor dialog. Edit the connection string and click OK.
    • For connections that require a username and password, the connection string can also be edited to replace the password with the $PASSWORD$ token that is substituted at runtime. If the $PASSWORD$ token is used, RED uses the contents of the masked "Extract User Password" property when making the connection.
      E.g. "Provider=SQLOLEDB.1;Password=$PASSWORD$ ;..."
              
  9. Once the connection is defined, a Load table must be created to hold data loaded into the data warehouse by dragging a source table or a flat file in the middle pane to create a Load table. (Refer to Loading data and Flat File Load for details.) The Load type can be set to Integration Services load from the Load table properties window. This creates and executes a SSIS package at run time, to load data into the data warehouse Load table.

The configuration options available on an SSIS load are available in the Source tab of the Load table's Properties. These options are detailed in the next topic SSIS Load Table Source Screen.

  • No labels