I recently received extensive On the Job Training in the Extract, Transform and Load of ASCII text files, SQL Server 2000 and Informix Databases, Data Warehouses and Data Marts using the Business Objects Data Integrator XI software package. I have found that this software is very dynamic and powerful. People who have a moderate level of Database and SQL Experience should be able to start coding ETL processes rather quickly.
The source code for all of the ETL Components are maintained in a Proprietary SQL Server 2000 Database Server Repository. The basic structure of the Data Integrator ETL Components are: Projects, Batch Jobs, Work Flows, Data Flows, Transformation Queries/Scripts/Mappings and Data Stores. The Data Stores use native SQL Server Data Server Names or ODBC DSNs for connecting to Input Source and Target Destination Database Tables.
The executable code can be run via the Data Integrator Designer IDE or by Exporting Execution Command Line Arguments to either Unix Shell Scripts or Windows NT Batch Command Files. These batch jobs are then run by an Apache Web Service Application Job Server called the Business Objects Data Integrator Web Administrator.
The original idea of the Example ETL Process Job_SalesOrg was taken from the Data Integrator XI Tutorial. The data in the sales_org.txt file and the local Microsoft SQL Server database server SHOWES(Windows NT) Target Database Table salesorg_dim were generated specifically for this example and contain bogus data fields.
I can provide copies of the Exported Job_SalesOrg.ATL file which is a Business Objects Proprietary formatted XML type of ASCII Text file, the Job_SalesOrg.BAT and Job_SalesOrg.TXT files to show how the executables work(upon request).
Notice below, that I created a local machine SQL Server database server showes DIRepo Repository and granted a ditest userid full dbo permissions. You can log into the software with a trusted userid or with Windows Authentication. After logging into the new showes DIRepo, I then created a Project Job_SalesOrg.
Notice below, that I created a sales_org.txt(input source format file), Query and a Target SALESORG_DIM table(destination table).
Notice below, that the sales_org.txt(input source format file) does not contain any data yet. There are four columns of data: SalesOffice, Region, DateOpen and Country.
Notice below, that we mapped SalesOffice, Region, DateOpen and NOT Country. This was intentional to show differences in mapped fields.