How to Import Data from Excel?

Scenario

Importing data from a comma-separated flat file to a List called Employee List.

The Employee List is mapped with two tables, EmployeeMaster and EmployeeDetails. The comma-separated flat file contains employee information. The employee first name and last name needs to be inserted in the Employee Main Table. The employee details (Address, Phone, Email, Department, etc) needs to be inserted in the EmployeeDetails Table.

To accomplish the above-mentioned requirement, create an SSIS Package with two Dataflow tasks - one for EmployeeMaster and the second for EmployeeDetails. The steps to create the package:

1. Open SQL Server Business Intelligence Development Studio (BIDS).

2. Add the dataflow task from the Toolbox.

3. Drag and drop the flat file source component to the first dataflow task.

4. Configure the flat file data source.

5. Add the List Destination component and configure it in the second data flow task.

6. Map the column.

7. Open SQL Server Business Intelligence Development Studio.

   

8. Drag and drop the flat file source component.

9. Go to Control Flow.Drag and drop the dataflow task from the Toolbox.

   

10. Go to Dataflow tab of the Employeemaster data flow task. The Employee.txt has the following data:

    

11. Drag and drop the Flat file Source component, and configure the Flat file source as shown below.

    

    

12. Next, you need to add List Destination component. Drag and drop the List Destination data flow component. Double click on the List destination to open the Destination property window.

    

13. Next, you need to configure the General and settings properties. Select General Tab, Repository name, List name Employee, and Employee Main Table from the List Structure tree.

    

14. Next, you need to set the Export settings. Click the Settings tab.

15. Duplicate Check

    • Enable: Select this box to enable checking of duplicates.

    • Insert: Select this radio button if you need to insert the item to the list even if it is a duplicate item.

    • Update: Select this radio button if you need to update the item.

    • Ignore: Select this radio button for checking duplicate records. If a duplicate record exists, it will be ignored.

16. Click OK to save.

17. Next, you need to do Column Mapping. Right-click the List Destination component and select the advanced editor. Go to column mapping and map the corresponding columns from Flat file to List Destination columns.

    

18. Go to control flow design dataflow for EmployeeDetails.

19. Drag and drop Dataflow task and rename it as Employee Details.

    Note: For the Child Table entry, you need to map the RecordParentItemId field in the Listdestination column.

    EmployeeId is mapped to the Destination column RecordParentItemId. To get the EmployeeId Lookup, the Dataflow Transformation component needs to be used. In the Lookup component, map the number with the Employeemaster table number field and take the Id of the Employee from the Employee Maser Table.

Lookup dataflow Transformation component configuration

20. Drag and drop lookup component in the Dataflow task. Link the Flat file source to Lookup component.

21. In the columns tab map the Number with the EmployeeMaster Column Number, take the Lookup Column as Id.Give Output Alias as EmployeeId. This column can be used to map to RecordParentItemid in the Listdestination Columns mapping.

    

22. Drag and drop the ListDestination.

General Configuration

23. Repeat 14th Step. 1. Next, you need to set the Export settings. Click the Settings tab.

24. Select EmployeeDetails table.

    

25. Next, you need to set the Settings configuration. If the insertion is to Child table Enable ListItemLevel.

    

26. Next, you need to set the Column Mapping.

    

27. The Package diagram is shown below.

    

28. To Execute the package, Click the Start Debug option in BID.

    

29. After execution of the Package, the Employee data from Employee.txt has been imported in the Employee list.