Types Of Sources

In Source Analyzer, we define the source definitions that we will use in a mapping. We can either import a source definition or manually create the definition.

We can import or create the following types of source definitions in the Source Analyzer:

• Relational tables, views, and synonyms
• Fixed-width and delimited flat files that do not contain binary data.
• COBOL files
• XML files
• Data models using certain data modeling tools through Metadata Exchange for Data Models

1.Working with Relational Sources

Special Character Handling:

We can import, create, or edit source definitions with table and column names containing special characters, such as the slash (/) character through the Designer.

When we use the Source Analyzer to import a source definition, the Designer retains special characters in table and field names.

However, when we add a source definition with special characters to a mapping, the Designer either retains or replaces the special character. Also, when we generate the default SQL statement in a Source Qualifier transformation for a relational source, the Designer uses quotation marks around some special characters. The Designer handles special characters differently for relational and non-relational sources.

Importing a Relational Source Definition

1. Connect to repository.
2. Right click the folder where you want to import source definition and  click open. The folder which is connected gets bold. We can work in only one folder at a time.
3. In the Source Analyzer, click Sources > Import from Database.
4. Select the ODBC data source used to connect to the source database. If you need to create or modify an ODBC data source, click the Browse button to open the ODBC Administrator. Create the data source, and click OK. Select the new ODBC data source.

5.Enter a database user name and password to connect to the database.

6.Click Connect. Table names will appear.

7.Select the relational object or objects you want to import.

8.Click OK.

9.Click Repository > Save.

Updating a Relational Source Definition

We can update a source definition to add business names or to reflect new column names, data types, or other changes. We can update a source definition in the following ways:

Edit the definition: Manually edit the source definition if we need to configure properties that we cannot import or if we want to make minor changes to the source definition.

Re import the definition: If the source changes are significant, we may need to Re import the source definition. This overwrites or renames the existing source Definition. We can retain existing primary key-foreign key relationships and descriptions in the source definition being replaced.

Editing Relational Source Definitions

1. Select Tools -> Source Analyzer
2. Drag the table you want to edit in workspace.
3. In the Source Analyzer, double-click the title bar of the source definition. Or Right click the table and click edit.
4. In table tab, we can rename, add owner name, business description or edit database type.
5. Click the Columns Tab. Edit column names, data types, and restrictions. Click OK.

2. Working with Flat Files

To use flat files as sources, targets, and lookups in a mapping we must import or create the definitions in the repository. We can import or create flat file source definitions in the Source Analyzer.

We can import fixed-width and delimited flat file definitions that do not contain binary data. When importing the definition, the file must be in a directory local to the client machine. In addition, the Integration Service must be able to access all source files during the session.

Special Character Handling:

When we import a flat file in the Designer, the Flat File Wizard uses the file name as the name of the flat file definition by default. We can import a flat file with any valid file name through the Flat File Wizard. However, the Designer does not recognize some special characters in flat file source and target names.

When we import a flat file, the Flat File Wizard changes invalid characters and spaces into underscores ( _ ). For example, you have the source file "sample prices+items.dat". When we import this flat file in the Designer, the Flat File Wizard names the file definition sample_prices_items by default.

To import a fixed-width flat file definition:

1. Open the Source Analyzer and click Sources > Import from File. The Open Flat File dialog box appears.
2. Browse and Select the file you want to use.
3. Select a code page.
4. Click OK.
5. Edit the following settings:

6) Click Next. Follow the directions in the wizard to manipulate the column breaks in the file preview window. Move existing column breaks by Dragging them. Double-click a column break to delete it.

7) Click next and Enter column information for each column in the file.

8) Click Finish.

9) Click Repository > Save.

To import a delimited flat file definition:

Delimited flat files are always character-oriented and line sequential. The column precision is always measured in characters for string columns and in significant digits for numeric columns. Each row ends with a newline character. We can import a delimited file that does not contain binary data or multi byte character data greater than two bytes per character.

Steps:

1. Repeat Steps 1-5 as in case of fixed width.
2. Click Next.
3. Enter the following settings:

4) Enter column information for each column in the file.

5) Click Finish. 6) Click Repository > Save. 

Editing Flat File Definitions

1) Select Tools -> Source Analyzer

2) Drag the file you want to edit in workspace.

3) In the Source Analyzer, double-click the title bar of the source definition.

• We can edit source or target flat file definitions using the following definition tabs:
•  Table tab: Edit properties such as table name, business name, and flat file properties.
•  Columns tab: Edit column information such as column names, data types, precision, and formats.
• Properties tab: View the default numeric and date time format properties in the Source Analyzer and the Target Designer. You can edit these properties for each source and target instance in a mapping in the Mapping Designer.
•  Metadata Extensions tab: Extend the metadata stored in the repository by associating information with repository objects, such as flat file definitions.

4) Click the Advanced button to edit the flat file properties. A different dialog box appears for fixed-width and delimited files.

5) Do the changes as needed.

6) Click OK.

7) Click Repository > Save.

The way to handle target flat files is also same as described in the above sections. Just make sure that instead of Source Analyzer,Select Tools -> Target Designer. Rest is same.