Loading Attachments in to Siebel through EIM (Enterprise Integration Manager)

This article is written to demonstrate how attachments can be migrated through EIM from source into target Siebel system. This document gives you an overview of loading attachments in Siebel, various entities in which attachments can be tied, processes involved in loading through eim, etc, Also, at the end of this document there is a case study available in loading attachments against Accounts through EIM. The pre-requisite for reading this document is to have basic knowledge in using Siebel, Enterprise Integration Manager (EIM) and IM knowledge and familiarity in Siebel data model.  

Attachment Entities in Siebel

  • Account
  • Activities
  • Agreements
  • Assets
  • Contacts
  • Employee
  • Fund Request
  • Households
  • Invoices
  • Messages
  • Offers
  • Opportunities
  • Product
  • Quotes
  • Sales Orders
  • Service Orders
  • Service Request        
  • Territory Management

Attachments in Siebel - An Overview

The major benefit of the Attachments view is that it allows others who may be working with the same record to access the attachment. This provides team members with the latest information .If you edit files attached to Siebel records, your changes are saved back to the Siebel file system when you step off the attachment record in the Siebel application.

 

            When you try to open an attached file, you will get either a Siebel download dialog box, or an Internet Explorer download dialog box. This depends on whether or not the record to which the attached file belongs is read-only or not. If the record is read-only, the Internet Explorer dialog box appears. If the record is not read-only, the Siebel dialog box appears.

 

You can attach a file created in another application to a record wherever the Attachments view is available within a screen. A variety of file types, such as Microsoft Outlook email messages, Microsoft Word documents, or image files, can be attached.

 

The File System Manager (FSM) server component manages the File System and handles all requests for files that reside within the Siebel File System. FSM interacts directly with the Siebel File System to invoke requests for access to files. Most server components use FSM to access files by submitting requests to Server Request Manager. However, Siebel Remote components do not use FSM to access the File System. Instead, Siebel Remote components use Synchronization Manager to access the File System.

Attachment Import Process through EIM

In Siebel applications, external files are stored in the Siebel file system. These files are compressed and stored using a specialized naming convention. Connected users have direct access to the Siebel file system. Mobile users have a local file system in the Siebel client installation directory. Copies of files retrieved from the Siebel file system during synchronization are stored in the local system for access while the client is offline. These files are accessible and readable by the Siebel client.

Siebel 7 uses the File System Manager for file attachments; therefore the file attaching operation is different from previous releases of Siebel applications. For the Siebel Web Client, the web browser will get the file from the user's machine and send it to the Siebel Web Server. The Siebel Web Server Extension will then send the file to a temp folder that can be accessed by the Siebel Server component. The Siebel Server then compresses the file and adds it to the Siebel File System.

Prerequisites

The following are the prerequisites to be checked for proper migration of attachments:

 

  • EnterpriseIntegration manager component is up and running.
  • Source file system should be in data could be extracted and could be transferred in flat file and physical files can be moved via folders
  • Make sure the file system has sufficient space for new attachments.
  • Check the user access privileges on the file system.
  • Check the file system parameters on the client .cfg file to make sure that the correct file system is being accessed and also that the path to the file system is correct.
  • Check that the file system is shared
  • The File System Manager is Online in target system

 

Identifying an EIM table for a given entity

 

To identify the EIM table for a given entity we have to move to that particular view in the Application UI and find the Business components for the entities in that view from Help->About View->Business Component

 

From tools ,the table related to Business components could be found [Hint : All Attachment related base tables end with _ATT].To find the EIM Table used during migration ,move to the flat tab->EIM Interface Table->Target Table with the parent Entity base table. You will find a list of tables which can be used to load the data.[Hint: EIM tables for Attachments loading will start with EIM_ and  end with _DTL].

 

From Types tab->EIM Interface Table->EIM Table Mapping->Attribute mapping we can find the mappings for the EIM table to Base tables for migrating the data.

 

Note: The columns whose names begin with FILE_ are required columns

File Attachment Columns

Three EIM table columns must be populated in order to import file attachments. The below table describes these columns and uses the attachment file budget99.doc as an example.

 

Table:  File Attachment Columns

Column

Description

Example

FILE_NAME

This column requires the root filename of the file attachment.

FILE_NAME="budget99"

FILE_EXT

This column requires the extension type of the file attachment (DOC, XLS, or TXT).

FILE_EXT="doc"

FILE_SRC_TYPE

This column requires the value "FILE" or the rows cannot be imported.

FILE_SRC_TYPE="FILE"

File Attachment Folder

As of Siebel 7.0, Siebel Anywhere requires that file system attachments be located in the \att subdirectory of the Siebel File System. If you are upgrading from Siebel 6.x, you must create this subdirectory in the Siebel File System and copy file system attachments to it.

To make file attachments accessible to Siebel Anywhere

  1. In the existing file system structure (for example C:\siebfile), create an att subdirectory if it does not already exist: (example: C:\siebfile\att )

Creating the att subdirectory does not adversely affect the installation of your Siebel Server. (You install the Siebel Server at a later point.)

  1. Copy (do not move) all files located under the \siebfile directory to the \siebfile\att directory so that all file attachments are accessible by Siebel Business Applications.
  2. Verify that files have copied correctly to the \siebfile\att directory. After this has been verified, clean up the file system.

Writing an IFB for Attachments

  • Find the base tables and EIM tables for attachments  and Prepare the mapping document for the base table and EIM tables for the chosen entity.
  • Prepare the IFB file for the EIM Process with the below parameters.

USER NAME : Database User name

  PASSWORD : Database password

  PROCESS : Initial/main section to Run

  TYPE : Values like IMPORT, EXPORT, DELETE, MERGE, SHELL

  BATCH : IF_ROW_BATCH_NUM to run against

  TABLE : Interface table for Process

  ONLY BASE TABLES : Process only these tables

  DEFAULT COLUMN(For import) : Default value for an interface column

IGNORE BASE TABLES : Do not process these tables

  IGNORE BASE COLUMNS : Do not process these columns

  INCLUDE : Sub Process to include

  FIXED COLUMN(For Import) : Set column to specified value

  LOG TRANSACTIONS : Default value depends on system preference

ATTACHMENT DIRECTORY: any customized folder that customer’s userid has read/write access to place /read the file

In the next section, let’s take a case study about loading Attachments against accounts which provides the detailed steps of all the processes involved in this.

Case Study - Loading Account Attachment through EIM

                       

This section describes about loading Accounts and associating attachments to account through EIM

 

Siebel Base Tables Involved:

 

 

ACCOUNT

S_PARTY

S_ORG_EXT

S_ORG_BU

S_ACCNT_BU

S_ADDR_ORG

ATTACHMENTS

S_ACCNT_ATT

 

Siebel EIM Tables:

 

 

ACCOUNT

EIM_ACCOUNT

ATTACHMENTS

EIM_ACCNT_DTL

 

 

Note:  PAR_ROW_ID is which is resolved using ACCNT_BI, LOC, and NAME.

 

Extract data from the Siebel source system:

                       

                        We need to analyze the system and find the tables where the entity is stored. Next step is to move the data to the source system ‘s base table to the EIM tables using the Configuration file (.ifb).[Various parameters which can be used according to the business need is been described below]. From the EIM tables data is moved to the flat files using SQL Loader.

 

  • To export data to EIM tables, EIM performs the following steps:
  • EIM initializes EIM tables for export .If CLEAR INTERFACE TABLE in the configuration file is TRUE, all rows with the specified batch number are deleted. Otherwise, a warning is issued if rows already exist with the specified batch number.
  •  It uses export parameter expressions in the configuration file to locate and export table rows:     
  • If EXPORT ALL ROWS is TRUE, ignore any EXPORT MATCHES    parameters and export all rows.
  • If EXPORT ALL ROWS is FALSE, use EXPORT MATCHES parameters to locate specific rows.
  • Set IF_ROW_STAT to EXPORTED for rows that are successfully exported.
  • For parent tables, EIM locates child table rows and exports them to their corresponding EIM tables

 

  • EXPORT ALL ROWS - Specifies that all rows in the target base table and secondary tables are to be exported. The default value is FALSE. Existing values in the EIM table and export matches expressions are ignored. For all columns to export using an EIM table (both data from the base table and data from related child tables), you need to make sure this parameter is set to TRUE (you may need to add this line if it does not currently exist) in the .IFB file.
  • The EXPORT MATCHES parameter specifies a WHERE clause expression for filtering base table rows. The value is in two parts: the Siebel EIM table name and the filter expression that goes against the target base table. The expression is applied against the target base table for the EIM table.
  • The expression is a self-contained WHERE clause expression (without the WHERE) and should use only literal values or unqualified column names from the base table. There must also be a space separating the operator from the operand.
  • EXPORT MATCHES can be used only against a target base table, or against a non-target base table that is an extension table of S_PARTY when the target table is S_PARTY.

 

  • To export all rows from the tables that are mapped in an EIM table, set the EXPORT ALL ROWS parameter for the file to TRUE in the specific export batch section of the EIM configuration file. The following example contains lines that may be used in the EIM configuration file to export all data rows from the Account attachment table.

 

Sample export process:

[Siebel Interface Manager]
      USER NAME = "SADMIN"
      PASSWORD = “SADMIN”
      PROCESS = Export Account  Atachment

[Export SR ATT]
TYPE = EXPORT
BATCH = 1000
TABLE = EIM_ACCNT_DTL
EXPORT ALL ROWS= TRUE

 

Place the Ifb into <SiebelServer>\Siebsrvr\Admin folder and run the EIM job inserver Administration - Server Management->Jobs to extract the data from the base tables to EIM tables.

This will import the data to the EIM_SR_DTL table and exports the actual files to SIEBEL HOME\OUTPUT directory.

 

You can specify the other directory if you wish and make sure the directory exists on a Siebel Server machine and you have read and write access to it. Example:

ATTACHMENT DIRECTORY = SIEBEL_HOME\TEMP.

 

There is no parameter in EIM to separate the process of populating EIM table and actual file exports. By default, Siebel uses SIEBEL_HOME\OUTPUT folder as an attachment directory and export the actual files to this directory in order for users to migrate files from one location to the other.

 

 

Steps followed to Load data into the target system:

 

 

 

Steps

Step Description

 

Step 1

Prepare the mapping document for the entities received say, Account and attachment. [EIM uses interface table mappings to map columns from interface tables to Siebel base tables]

 

Step 2

Prepare the ctl file to load the given /prepared data. Place the file in the path specified in the batch file say, D:\sea_78\EIM_Training\POC_ACNT_ATTACHMENT

 

Step 3

Place the data files in appropriate folders. Place the file in the path specified in the ctl file say, D:\sea_78\EIM_Training\POC_ACNT_ATTACHMENT

 

Step 4

Run the BAT file toload data from flat file into the EIM tables.

 

 

Step 5

Place the corresponding attachment files in folder <SiebelServer>\Siebsrvr\Input for which you have loaded the attachment records in EIM table

 

Step 6

Place the IFB files for Account and Attachment in <SiebelServer>\Siebsrvr\Admin.

 

Step 7

Run the EIM task for loading account data in server Administration - Server Management->Jobs first as account in the parent data.

 

 

Step 8

Run the EIM task for loading attachment data in server Administration - Server Management->Jobs next to load the attachment data and associate it to the account loaded

 

Step 9

Check the IF_ROW_STAT from the EIM tables are IMPORTED.

 

Step 10

Check in account attachment view for the loaded data. Refer figure 1 for the records loaded by EIM

 

Attaching URL's as attachment records

In addition to attaching files to records, you can also attach URLs to records to provide team members with the latest information.

You can also use these attachment columns to define hyperlinks. Below table explains the sample values to be populated in the columns.

 

Table 4.  Defining Hyperlinks With File Attachment Columns

Column

Setting

FILE_NAME

Set to actual URL

FILE_EXT

NULL

FILE_SRC_TYPE

'URL'

To view a URL record

  1. Drill down on the record to which you want view the URL.
  2. Click the Attachments view tab.

3.         The Attachments list appears.

The URL appears as a hyperlink in the Attachments list. You can click it to access the corresponding Web site.

NOTE:  If you cannot access the Web site after adding it as an attachment, it may be because you are not connected to the network.

Updating & Deleting Attachments

Updating File Attachments:

You can also update file attachments that have already been imported into the Siebel database.

In order to update file attachments, EIM deletes the old row pointing to the existing file attachment and then imports the new file attachment. After all file attachments have been updated, use the Siebel File System Maintenance Utility named sfscleanup.exe (during hours when the network is least laden) to clean the file attachment directory of any unused file attachments.

To update file attachments:

  1. Update the file attachment by completing the steps in Importing File Attachments.
  2. Once all file attachments have been updated, run the Siebel File System Maintenance Utility named sfscleanup.exe to clean up the file attachment directory.

NOTE:  EIM does not support merging of file attachments.

Deleting File Attachments

You can also delete file attachments that have previously been imported into the Siebel database.

In order to delete file attachments, EIM deletes the row pointing to the file attachment. After all file attachments have been deleted, use the Siebel File System Maintenance Utility named sfscleanup.exe during hours when the network is least laden to clean the file attachment directory of any unused file attachments.

To delete file attachments:

Run an EIM delete process for all file attachments that you want to delete.

After all file attachments have been deleted, run the Siebel File System Maintenance Utility named sfscleanup.exe to clean up the file attachment directory.  

Cleaning Up the Siebel File System

The task of cleaning up the Siebel File System by removing orphan records is done using sfscleanup.exe, a command-line utility. Orphan records are those that remain if a user deletes a parent record in the application that has associated child records; the child records are not deleted from the Siebel File System with the parent record and must be removed using sfscleanup.exe.

This utility is located in the binary (bin) subdirectory within the Siebel Server root directory. The sfscleanup.exe utility processes every file in the file attachment directory and performs one of several operations to each file depending on the file type and the parameters that are set. For descriptions of the run-time parameters that you can set when running sfscleanup.exe, see the following procedure. For descriptions of the file types and the associated operation performed by sfscleanup.exe during processing, see Table below[File Types and Associated Operation].

To clean up the file attachment directory using sfscleanup.exe:

  1. At the command prompt, change directory to the bin subdirectory within the Siebel Server root directory.
  2. Run sfscleanup.exe using the parameters listed in the following table as shown in the following example:

sfscleanup /u sadmin /p secret /f \\server1\files /x \\server1\logs\sfscleanup.log

 

Parameter

Value

Description

Required?

/u

Username

Username ID.

Y

/p

Password

Username password.

Y

/c

ODBC data source

Set this value to the ODBC data source. Default value is set to the environment variable, SIEBEL_DATA_SOURCE.

N

/d

Siebel table owner

Set this value to the Siebel tableowner. Default value is set to the environment variable, SIEBEL_TABLE_OWNER.

N

/f

Path for file directory

Set this value to the path for the file attachment directory. Do not append att to the file attachment directory path.

Y

/x

Path for output file

Set this value to the path for the output file.

N

/m

Path for move directory

Set this value to the path for the directory where discarded files will be moved.

N

/n

Remove old revisions

Determines if old versions of file attachments will be removed. To remove old versions, set this value to Y. Default value is N.

N

/r

Generate report file only

Set this value to Y to generate only a report file. If set to Y, the report file contains only the columns File Name and File Type. Default value is N.

N

/g

Garbage files

Set this value to remove garbage or non-Siebel files. Default value is N.

N

Further details on some parameter settings:

  • /n. By default old file revisions are kept. Such files are marked ANCIENT in the log, and represent old revisions of an existing attachment record; that is, their row ID matches with the database record but not the file revision number. To delete such files, set the /n parameter to Y.
  • /g. If the file system contains files that were not created by the File System Manager component (alias FSMSrvr), then their deletion or move is controlled by the /g parameter. This parameter includes non-Siebel files or directories. By default these files are not deleted. The directories are not affected or moved by sfscleanup.exe.

If you specified an output file using the /x parameter, sfscleanup.exe generates a log file listing the operations that were performed. The output file is a tab-delimited text file that contains the following columns:

File Name

This column lists the name of each file that was processed.

File Type

This column lists the type of each file that was processed. Table[File Types and Associated Operation]lists the possible file types and the associated operation performed by sfscleanup.exe during processing.

 

Table . File Types and Associated Operation

File Type

Description

Operation

CURRENT

The file has a corresponding record in the file attachment database table.

KEPT

NEW

The file is less than one hour old. The sfscleanup.exe program does not check for the file in the file attachment database table.

KEPT

ORPHAN

The file does not have a corresponding record in the file attachment database table.

DELETED

INVALID

The file (or directory) is not a file attachment. If sfscleanup.exeis attempting to delete a subdirectory that is not empty, the operation errors out. This gives you an opportunity to review the files contained within the directory before deletion.

KEPT

ANCIENT

The file has an associated record in the database with a different revision number.

KEPT

 

 

1For descriptions of each operation, see Table Operations

2If you used the /m parameter to set a move directory, the operation performed is MOVED.

3If you set the /g parameter to Y, the operation performed is DELETED.

4If you set the /n parameter to Y, the operation performed is DELETED (or MOVED if you used the /m parameter to set a move directory).

Operation

This column lists the type of operation that was performed during processing. Table :Operations lists the types of operation that sfscleanup.exe may have performed during processing.

 

Table : Operations

Operation

Description

KEPT

The file was kept.

DELETED

The file was deleted.

MOVED

The file was moved to the directory specified by the /m parameter. Files will only be moved if you used the /m parameter.

KEPT_DIR

The item was kept because it was a directory and requires manual processing.

KEPT_ERROR

The file was kept because an error occurred while trying to move or delete the file.

 

Troubleshooting EIM Attachment load process

 

File System Problem

 

Problem:

 

The File System does not appear to be working correctly. Whenever you drill-down on a document such as a Literature item or a Correspondence Template, you get the error "The file <filename> could not be found on any specified file system. The File System Manager is Online.
The problem occurs for both the mobile web client (when connected to the server) as well as the high-interactivity client.

Solution:

 

 

1) For the mobile web client you have to set the FileSystem parameter in the CFG file as follows:

\\CAKNACADMLSD03\SiebFileSystem\att

The problem that appeared with the beta 7.0.1 seems to be resolved. Product defect #12-62JKWR which addressed this (File system doesn't work. User cannot launch any records, which should work with the file system) has been fixed and you should use the FileSystem parameter in the CFG as mentioned above.

2) The Server Parameter for "Siebel File System" should be set as follows:

    \\CAKNACADMLSD03\SiebFileSystem

and not E:\SiebFileSystem. You will need to specify the UNC sharename for the "Siebel File System" server parameter if you did not when installing the Siebel Server. This is mentioned in "Siebel Server Installation Guide for Microsoft Windows", chapter "Installing the Siebel Server", section "Creating a File System".

Note: CAKNACADMLSD03 is the server name.

The standard literature items or correspondence templates must be copied to the \att subdirectory of the Siebel File System as mentioned into the bookshelf.

 Moving Data from 6.X to 7.X

During the upgrade from Release 6.x, data from attachments in S_ORG_INT_ATT were migrated to S_ACCNT_ATT and attachments in S_EMPLOYEE_ATT were migrated to S_CONTACT_ATT.

Schema table names are included in the attachment file names.

The utility chng_file_sys.bat copies and renames all files named S_EMPLOYEE_ATT*.SAF to S_CONTACT_ATT*.SAF and all files named S_ORG_INT_ATT*.SAF to S_ACCNT_ATT*.SAF so that they correspond to new table names.

 Limitation On UNIX Platform

There is a limitation when Customer wanted uncompress all the file attachments to it's original names and format on UNIX platform.

On Unix, platform you have ssezip, sseunzip and ssemuzip available under Siebel 7.5.3 environment. Note: these utilities do not exists under Siebel 7.7 and Siebel 7.8 release, however you can use Siebel 7.5.3 utilities to uncompress file attachments.

Sample Account Attachment view

 

The file attachment applet (or attachment applet) provides access to external documents, such as spreadsheets, word processing documents, and slide presentations, from within Siebel applications. A file attachment applet provides the following capabilities:

  • Allows the user to open a document of any Windows-supported file type by clicking on its name in a list.
  • Allows the user to add document files to a list, edit them, or remove them.
  • Provides synchronization and shared access support for attached documents

In the  account attachment view the upper applet is the standard Account Form Applet. The lower applet is a file attachment applet called Account Attachment Applet. There is a master-detail relationship between the account and the list of account attachments, so that all file attachments for the current account are listed in the lower applet.

Each document is represented by a row in the attachments list. The document's filename, local/server status, file size, Windows file type (filename extension), and date of last update are displayed. Additionally, the name of each file appears in underlined, colored text, indicating that the file may be opened in the appropriate Windows application by clicking on the name.

Tags