Data Transfer from Flat File to Database

[Former Member]

Data Transfer from flat file to Database

Introduction:-

     In this scenario i am demonstrating how to transfer the data from flat file to database in a step by step manner.

As data store is required to connect to any database we will start with creating the datastore for the database

Here we are using SQL server 2008 as a database.

I) Creating DataStore for SQL database.

1. Logon to the SAP Business Object Data Designer.

        2.  In Local Object Library click on Datastore Tab.

3. In Local Object Library click on Datastore Tab.

     Window for creating new datastore will open as shown below.

     Give all the details as shown below.

     Here database name "RAHUL_MORE" that i am using is already created before creating the datastore.

4. Click "OK" & the Datastore will be created & can be seen in the Local Object Library as shown below.

Now we will create File Format to process the source file.

II) Creating File Format

     1. Click on the "Formats" tab in the Local Object Library in Data Service Designer.

     2. Select "Flat Files", right click & click on "New". Flat File Editor will open as shown below.

We are using following file as the source.

   

3. Give all the details in the File Format Editor  as follows.

When you select the File Name it will ask to overwrite the schema. I have selected "Yes" option in this option.

As a result of this you can see the altered schema on top of the RHS.

4. After giving all the details press "Save & Close". File Format will be created & can be seen in the Local Object Library.

Now after creating the Datastore & File Format we will design a job in the Data Service Designer.

III) Creating a job in Data Service Designer.

1. Create a project.

• Go to the "Project Area" in the Data Service Designer & click the icon for creating the project from menu bar.

• Window for creating the new project will open. Give project name & click "Create"

• New project will be created & can be seen in the Project Area.

2. Create a Batch Job.

• Right click on the project & click on "New Batch job".
• Give appropriate name to the job.

3. Add a Dataflow into the job.

• Select the job, drag dataflow from palette into it & name it.
• 
  

4.Build the scenario in the dataflow.

• Double click on the dataflow.
• Drag a file format created by us earlier into the dataflow.
• 
  

• Drag a Query into the dataflow & connect it to the file format.
• 

5. Mapping

     Open the query & do mapping.

    

• Select all the fields on LHS,  right click & select "Map to Output".

    

• Now you can see the fields mapped to RHS as shown below.

    

• To make "ID" as the key field , Right click on the "ID" field & mark it as a Primary Key.

    

6.Insert Target Table.

   
     We can either import the already created table or we can use template table which afterwards will actually be created in the database.

     We are using template table here.

• Drag a template table from palette into the dataflow.
• Give name to the table & select appropriate datastore.

    

• Click on "OK".

         Template table can be seen in the dataflow.

• Connect Template table to Query.

    

7.Save & Validate the Job.

• Click "Validate All" icon from the tool bar.

    

• Following message is displayed if no error found in the job.

     "Validate : No Errors Found (BODI 1220017)"

    

8.Execute the Job.

• Select the job, right click & press "Execute".

9.Check the output in the table.

     After execution table will be created in the database & you can check the output.

     For checking the output you can click on the Magnifying Glass icon of the table in the DS Designer.