Import and Export Data

In this section, you will learn to use the List integration components in an SSIS package. The examples refer to Excel as a data source and data destination. You can also use other data sources such as a DBF file, CSV, flat file, to name a few.

Export date to Excel

Scenario

You need to export vendor list data from the List. The SSIS package consists of two dataflow tasks, one for the vendor and one for VendorAddress. The vendor list is mapped to two tables - Vendor and Address. The data from these tables is exported to an Excel file called VendorDetails.xls. 

1. Open SQL Server Business Intelligence Development Studio.

2. In Control Flow, drag and drop the Dataflow task from the Toolbox.

   

3. Click the Dataflow tab of the Vendor data flow task.

   

4. Drag and drop the List Source data flow component.

5. Double-click the List source to open the List Source property window.

   

6. Next you need to set the properties. Select the Repository name.

7. Select owner from the drop-down list.

8. Enter the Password.

9. Select Vendor from the list names.

10. Select the Vendor Table from the List Structure tree.

    Note: Checking Export all columns option will check all the columns. 

11. Click OK to save.

12. Drag the Excel Destination component. Link the List source with the Excel destination.

13. Next, you need to map the columns. 

14. Right-click the List Source component and select the advanced editor. 

15. Go to column mapping and map the corresponding columns from List source column to Excel sheet destination columns.

    

16. Go to control flow design dataflow for Address. 

17. Drag and drop the Dataflow task and rename it as Address.

18. Go to dataflow task. Drag and Drop List Source component and configure the properties as explained in the above steps.

    

    

    

19. In the Package diagram, click on the Start Debug option in the BID to execute the package.

    

20. You have successfully exported data from the vendor list to VendorExport.xls.

    

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:

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

22. Add the dataflow task from the Toolbox.

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

24. Configure the flat file data source.

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

26. Map the column.

27. Open SQL Server Business Intelligence Development Studio.

    

28. Drag and drop the flat file source component.

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

    

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

    

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

    

    

32. 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.

    

33. 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.

    

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

35. 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.

36. Click OK to save.

37. 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.

    

38. Go to control flow design dataflow for EmployeeDetails.

39. 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

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

41. 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.

    

42. Drag and drop the ListDestination.

General Configuration

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

44. Select EmployeeDetails table.

    

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

    

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

    

47. The Package diagram is shown below.

    

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

    

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