Sunday, June 2, 2013

Integration Services

Overview

Assume that the Automart Web site records new registered customers into an xml file and each day they need to import the new customers into the database. (Unlikely for a company but not unusual for a non-profit or home run operation.)

We are going to make an integration Services package in SQL Server so that we can import the data automatically each day.

We must take the data from the XML file and import it into three tables, PERSON, CUSTOMER.VEHICLE, and CUSTOMER.REGISTEREDUSTOMER.

Here is the XML file and its Schema.


newcustomer.xml

<?xml version="1.0" encoding="utf-8"?>
<newcustomer>
  <customer>
    <firstname>Sally</firstname>
    <lastname>King</lastname>
    <email>sking@msn.com</email>
    <password>royalpass</password>
    <license>FMN2033</license>
    <make>Toyota corolla</make>
    <year>2011</year>
  </customer>
  <customer>
    <firstname>Edwin</firstname>
    <lastname>Laurence</lastname>
    <email>elaurence@gmail.com</email>
    <password>edpass</password>
    <license>WEB3445</license>
    <make>Volkswagen Beetle</make>
    <year>2012</year>
  </customer>
  <customer>
    <firstname>Sharon</firstname>
    <lastname>Malin</lastname>
    <email>sandm@gmail.com</email>
    <password>smpass</password>
    <license>TEN6545</license>
    <make>Nisson Leaf</make>
    <year>2013</year>
  </customer>
  <customer>
    <firstname>Bob</firstname>
    <lastname>Brown</lastname>
    <email>bbrown@gmail.com</email>
    <password>bbpass</password>
    <license>LTE0034</license>
    <make>Ford Mustang</make>
    <year>2002</year>
  </customer>
  <customer>
    <firstname>Melissa</firstname>
    <lastname>Bee</lastname>
    <email>melissa@gmail.com</email>
    <password>beepass</password>
    <license>MLN9007</license>
    <make>Kia Rio</make>
    <year>2010</year>
  </customer>
</newcustomer>

Copy this file into Notepad. Choose SAVE AS. Set the type to All Files(*.*) and change the encoding to UTF-8

Here is the schema for the above XML

newcustomer.xsd

<?xml version="1.0" encoding="utf-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="newcustomer">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="customer">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="firstname" type="xs:string" />
              <xs:element name="lastname" type="xs:string" />
              <xs:element name="email" type="xs:string" />
              <xs:element name="password" type="xs:string" />
              <xs:element name="license" type="xs:string" />
              <xs:element name="make" type="xs:string" />
              <xs:element name="year" type="xs:string" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Save this as newcustomer.xsd with the UTF-8 encoding


Creating the Integration Package

1. Open SQL Services Data Tools as Administrator. If you do not open it as administrator it will fail when you try to run it.
2. Start a new Project
3. Choose Integration Services Project
4. Name it AutomartNewCustomer

5. In the Designer's Control Flow tab add a new DataFlow Task and name it "Get Person Task"

6. Double click the DataFlow task to open the Data Flow tab
7. Add a new XML Source control. Rename it "Person Source" and double click it to set the file and schema properties

8.Click on COLUMNS, click through the warnings and view the columns

9. Click OK
10. Now we will add a Data Conversion control. This control lets us change the properties of the data to make it conform more with the database
11. Connect the arrow from the source to the Conversion

12. Double Click the Data Conversion control to open the properties dialog.
13. For now you can just select firstname, lastName

14. Click ok.
15. Now we will add a Destination control
16. Select a SQL Server Destination name it "Person Destination" and connect it to the Data Conversion Control

17. Double Click the Person Destination control to open the properties
18. First we add a new Connection manager and a new connection. We connect to the server and the automart database

19. Then we select the person table

20. Next we go to the table mappings and match our columns. Use the copy of the xml fields for the match

21. Return to the Control Flow tab
22. Add another Data Task control and name it "Get Vehicle Task".
23. Hook it to the Person Task Control

24. Double Click the data flow task to get the data flow designer
25. We will start with an xml source. It is configured exactly the same as the last one.
26. Next we will add another Data configuration Control and configure the fields as in the picture below

27. It might also be a good idea to turn off the error controls

28. Now we are going to add a lookup control. We need to look up the person keys from our previous inserts

29. In the connection tab of the dialog select the Person table

30. Now in the mapping tab. Drag firstname to firstname and lastname to lastname and check the PersonKey box. this will enable the program to look up the person key that matches the first and last names

31. Next add a SQL Server Destination. When you connect it to the lookup this dialog will pop up

32. Select "Lookup Match Output" and OK
33. In the connection manager use the same manager and choose the table Vehicle
34. then go to mappings and set the mappings as in the picture below

35. No go back to the Control Flow Tab
36. We are going to add one more Data Flow task to populate the RegisteredCustomer table

37. Double click it
38. We will need an xml source configured just like the other two.
39. We will need a Data Conversion object in which we select email and password

40. And we will also need a lookup control configured exactly like the last one.
41. Finally we will need a SQL Server Destination Control, using the Customer.RegisteredCustomer table.

Running and Debugging

With luck now we can run the package. Just click the Green triangle to start the package. If all goes well the controls should turn green

If they fail. Check in the Data Flow for where the error occurred. Try turning off all the errors in the various controls. Make sure you are running as admin.

No comments:

Post a Comment