Access Input

Description

This step reads directly from Microsoft Access "MDB" files. The following sections describe the available options for the Access input step.

Options

File Tab

Option

Description

Step name

Name of the step; the name has to be unique in a single transformation.

Filename is defined in a field

This option allows you to accept filenames from previous steps.

Get filename from field

Select the field that will contain the filenames at runtime from this drop down list.

File or directory

Specifies the location and/or name of the input text file.
  Note: Click Add to add the file/directory/wildcard combination to the list of selected files (grid) below.

Regular Expression

Specifies the regular expression you want to use to select the files in the directory specified in the previous option.

Selected files

Contains a list of selected files (or wildcard selections) and a property specifying if file is required or not. If a file is required and it is not found, an error is generated;otherwise, the file name is skipped.

Show Filename(s) button

Displays a list of all files that will be loaded based on the current selected file definitions

Preview rows button

Click Preview to examine the contents of the specified Access file

Content Tab

Option

Description

Table

The access table-name to read data from

Include filename in output?

Allows you to specify a field name to include the file name (String) in the output of this step.

Include table name in output?

Allows you to specify a field name to include the table name (String) in the output of this step.

Include rownum in output?

Allows you to specify a field name to include the row number (Integer) in the output of this step.

Reset rownum per file?

This check box will start to number at 1 in each file that is being read.

Limit

Specify an integer larger than zero here to limit the number of rows being read by this step.

Add filename to result?

Adds the Access filenames read to the result of this transformation.  A unique list is being kept in memory that can be used in the next job entry in a job, for example in another transformation.

Fields Tab

Option

Description

Name

The name of the output field

Column

The name of the column in the Access table

Type

The data type to convert to

Format

The format or conversion mask to use in the data type conversion

Length

The length of the output data type

Precision

The precision of the output data type

Currency

The currency symbol to use during data type conversion

Decimal

The numeric decimal symbol to use during data type conversion

Group

The numeric grouping symbol to use during data type conversion

Trim type

The type of trimming to use during data type conversion

Repeat

Repeat the column value of the previous row if the column value is empty (null)

Metadata Injection Support

You can use the Metadata Injection supported fields with ETL Metadata Injection step to pass metadata to your transformation at runtime. The following Option and Value fields of the Microsoft Access step support metadata injection:

  • Options: Filename is Defined in a Field, Get Filename from Field, File and Directory, Regular Expression, Exclude Regular Expression, Selected Files, Show Filenames, Additional Fields, and Add to Result Filename
  • Values: Name, Column, Type, Format, Length, Precision, Currency, Decimal, Group, Trim Type, Short Filename, Extension, Path, Size, Is Hidden, and Last Modification