This section comprises:

 

General information

The destination of data can be any one of:

  • Database table
  • SQL stored procedure
  • Excel worksheet
  • CSV/text file

The first two destination types are more commonly used.

When the destination is a database table or stored procedure, you need to create a connection to the database holding the appropriate table or procedure. 

When the destination is a Excel worksheet you need to create an connection to the appropriate workbook.

When the destination is a text file you need to create an connection to the appropriate directory.

Note: When connecting to an Excel Worksheet, the workbook can be thought of as a database, the worksheet as a table. When connecting to file (e.g. CSV) a folder can be thought of as a database, the file as a table.

It is recommended that connections are made using a native OLE DB provider. However, if no suitable native provider exists you may need to use a generic OLE DB provider to make the connection via an ODBC driver. The first step in creating the connection string is to check which providers are currently available.

 

Check the available OLE DB providers

To check the available OLE DB providers, click New on the Database / template tab to display the Data Link Properties dialog:

This lists the available OLE DB providers on your machine. Common providers are:

  • Microsoft OLE DB Provider for SQL Server– for connecting to Microsoft SQL Server databases
  • Microsoft Jet n.n OLE DB Provider – for connecting to Microsoft Access databases
  • Microsoft OLE DB Provider for ODBC Drivers – a generic provider, used for connecting via ODBC drivers.

If an appropriate native provider exists, you then:

If no suitable native provider exists, you then:

 

Set up a data source name (DSN)

If no suitable native provider exists, you then you need to set up a data source name (DSN) linked to the appropriate ODBC driver.

To set up a DSN:

  1. On the Database/template tab, click ODBC32 to display the ODBC Data Source Administrator dialog:

  2. Click Add to display the following:

  3. Select the driver to be linked to the DSN. For example, select ‘Microsoft Text Server’ when wishing to connect to a CSV/text file.

  4. Click Finish. You then see a setup dialog, similar to the following, its options depending on the driver you chose:

  5. Enter the name of the DSN and a description.

  6. Optionally, complete the remaining fields on the screen.

  7. Click OK to return the ODBC Data Source Administrator dialog. The new DSN now appears in the list on the dialog.

  8. Click OK to return to the Database/template tab.

 

Specify the connection string

You specify the connection string either manually by entering the connection string in the editable field in the Data destination panel or by using the Data Link Properties dialog.

NOTE: It is recommended that you use the Data Link Properties dialog whenever possible to avoid the possibility of mistyping the string.

To use the Data Link Properties dialog to create the connection string:

  1. On the Database / template tab, click New to display the Data Link Properties dialog:

  2. Select the provider matching your data destination, from the displayed list.

  3. Click Next to view the Connection tab. This appears similar to the following, its contents depending on the provider you chose.

  4. Specify the name of the server or database or data source as required in the first section of the dialog.

    5. Tip: If you use SQL Server Express the sting ‘\sqlexpress’ needs to be added to the end of the server name. If a Refresh button is displayed, it is recommended that you do not use it as it can take considerable time to search through a network for possible servers.

  5. Specify the login details in the second section.

  6. If visible, specify the database details on the third section.

  7. Optionally:
    1. click Test Connection to check you have specified the details correctly. You should see a confirmation dialog. If the connection fails, ensure that the settings are correct. For example, spelling errors and case sensitivity can cause failed connections.
    2. click OK on the confirmation dialog to return to the Data Link Properties dialog.

  8. Click OK to return to the Database/template tab.

The connection string now appears in the field within the Data destination panel and the Connection icon  is set to green to indicate that there is a connection.

 

Modify the connection string

If you wish to change the details of a connection you can either click Modify and repeat the process of setting up a connection or edit the field by typing within it. As you type the Connection icon changes to orange to indicate a change has been made.

If required, you can also include one or more task parameters in the connection string. 

 

Test the connection string

When you have finished editing the connection string, click Test. You should see a confirmation dialog and the Connection icon change to green. If the connection fails, ensure that the settings are correct. For example, spelling errors and case sensitivity can cause failed connections.

 Select the Task Method Template