{"id":2549,"date":"2018-01-29T23:47:59","date_gmt":"2018-01-29T23:47:59","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=2549"},"modified":"2020-02-20T16:00:06","modified_gmt":"2020-02-20T16:00:06","slug":"load-sql-server-data-workday-using-ssis-soap-api","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/load-sql-server-data-workday-using-ssis-soap-api\/","title":{"rendered":"Load SQL Server data to Workday using SSIS \/ SOAP API"},"content":{"rendered":"

Introduction<\/span><\/h2>\n

\"\"<\/a>In our previous article, we saw step-by-step approach to read data from workday using SSIS<\/a>. In this article, we will focus on how to load SQL Server data to Workday (e.g. POST, Create, Update). We will use\u00a0SSIS Web API Destination<\/a>\u00a0and the combination of other Transforms such as SSIS Template Transform and SSIS XML Generator Transform<\/a> .<\/p>\n

The main thing in this article how to create SOAP Request correctly inside your request. This article explains how to use Free tools like\u00a0SoapUI to create Workday Request<\/a>. Once you do that you can call virtually any Workday API using the same technique. If you need more help contact our\u00a0Support<\/a>\u00a0and we will be happy to help you step by step.<\/p>\n

Note:<\/b> Template Transform mentioned in this article is only available in v2.6.4 or Higher<\/strong><\/div><\/div>\n

Prerequisites<\/span><\/h2>\r\nBefore we perform the steps listed in this article, you will need to make sure the following prerequisites are met:\r\n
    \r\n \t
  1. SSIS<\/abbr> designer installed.\u00a0Sometimes it is referred to as BIDS<\/abbr> or SSDT<\/abbr> (download it from the Microsoft site<\/a>).<\/li>\r\n \t
  2. Basic knowledge of SSIS package\u00a0development using\u00a0Microsoft SQL Server Integration Services<\/em>.<\/li>\r\n \t
  3. Make sure\u00a0ZappySys SSIS PowerPack<\/a><\/span>\u00a0is installed (download it<\/a>, if you haven't already).<\/li>\r\n \t
  4. (Optional step<\/em>).<\/em>\u00a0Read this article<\/a>, if you are planning to deploy packages to a server and schedule their execution later.<\/li>\r\n<\/ol><\/div>\n

    Step-By-Step : Import SQL Server data to Workday using SSIS<\/h2>\n

    Lets build our SSIS Package to load data from SQL Server or any Source (e.g. Oracle, DB2) to Workday using SOAP API calls. Using below approach you can Create new records or Update existing records in Workday. You can also delete records with same concept.<\/p>\n

    Basic steps outlined below.<\/p>\n

      \n
    1. Fetch records from SQL Source and build XML Request for each Row (e.g. Create Account)<\/li>\n
    2. Build XML Request (SOAP Body) using Template Transform or\u00a0 SSIS XML Generator Transform<\/a>. If you have Array nodes (e.g. One to Many) then you have to use\u00a0SSIS XML Generator Transform<\/a>\u00a0else use Template Transform for ease of use.<\/li>\n
    3. Pass input record (e.g. SOAP Body) to Web API destination to call Workday API call (CREATE, UPDATE, DELETE requests)<\/li>\n
    4. Parse XML Response (i.e. output) using SSIS XML Parser Transform<\/a> or save raw XML to SQL Server database.<\/li>\n
    5. Redirect Bad rows or failed requests to log file for review<\/li>\n<\/ol>\n

      Now let’s look at each step in detail<\/p>\n

      NOTE:<\/strong>\u00a0If you are trying to get data from Workday report<\/span> <\/em>instead (your Workday admin created a report and gave you a link) then skip Soap UI part, use the URL with GET method and Basic authentication<\/em> instead of SOAP WSS.<\/div><\/div>\n

      Obtain Workday API URL<\/span><\/h3>\n

      Once you have WSDL file, next step is craft correct URL for API service you like to call. The service name can be obtained from\u00a0here<\/a>\u00a0(Check service column)<\/p>\n

      Syntax:<\/strong>\u00a0https:\/\/<workday host name>.workday.com\/ccx\/service\/<tenant name>\/<service-name><\/span><\/span>
      \nExample:<\/strong>\u00a0https:\/\/MY-INSTANCE.workday.com\/ccx\/service\/MY-TenantID\/Human_Resources<\/span><\/span><\/p>\n

      Craft SOAP Body (XML API Request) using SoapUI<\/span><\/h3>\n

      Now its time to craft some SOAP Request. Check steps\u00a0outlined here (Use SoapUI tool)<\/a>\u00a0. Once you have Request Body XML you can change parameters as per your need.<\/p>\n

      Here is sample\u00a0 SOAP XML Body for Get Employee call from Human_Resopurces service.<\/p>\n

      \n
      \"Create<\/a><\/div>\n<\/div>\n
      \n

      Create SOAP Request Body from WSDL (Using SoapUI tool)<\/p>\n<\/div>\n

      Creating SSIS Connection for Workday SOAP API call using WSS Security<\/span><\/h3>\n
      \n

      To create a new connection for workday perform the following steps.<\/p>\n

        \n
      1. Two ways you can create HTTP connection for the workday service
        \nFirst approach:<\/strong>\u00a0Right click in the connection managers panel and click \u201cNew Connection\u2026\u201d and\u00a0Select\u00a0ZS-HTTP<\/strong>connection\u00a0from the connection type list and click OK.
        \n\u2014 OR \u2014<\/strong>
        \nSecond approach:<\/strong>\u00a0If you are already on\u00a0        SSIS XML Source<\/a>\u00a0or\u00a0SSIS REST API TASK<\/a>\u00a0or\u00a0SSIS Web API Destination<\/a>\u00a0UI then click [New] next to the Connection Dropdown.<\/li>\n
      2. Once HTTP Connection UI is visible configure following way.\n
          \n
        1. Enter API URL for Workday (Make sure you don\u2019t enter WSDL URL\u00a0found here<\/a>\u00a0). Your API URL will be something like below.
          \nSyntax:<\/strong>\u00a0https:\/\/<workday host name>.workday.com\/ccx\/service\/<tenant name>\/<service-name><\/span>
          \nExample:<\/strong>\u00a0https:\/\/wd1-impl-services1.workday.com\/ccx\/service\/MyTenantID\/Human_Resources<\/span><\/li>\n
        2. Select credential type as SOAP WSS (This setting is only found in\u00a0v2.6.4<\/strong>\u00a0or Higher)<\/li>\n
        3. Enter your workday userid and password<\/li>\n
        4. For WSS password type setting you leave it default (Not set) or change to PasswordHash for more secure communication.<\/li>\n
        5. Click OK to save.<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
          \n

          \"SSIS<\/a><\/p>\n

          SSIS Workday Integration \u2013 Create New SAOP Service Connection for Workday API Service (SOAP WSS)<\/p>\n<\/div>\n

           <\/p>\n<\/div>\n

          Loading SQL Server data to Workday using SSIS<\/span><\/h3>\n

          Let’s look at the real-world scenario. You have Accounts table stored in SQL Server and you like to create same accounts in Workday by calling appropriate API calls.<\/p>\n

            \n
          1. Drag Data flow task from SSIS Toolbox. Double click to edit.<\/li>\n
          2. Drag OLEDB Source configure to read SQL Table (e.g. Accounts)<\/li>\n
          3. Drag ZS Template Transform from the toolbox. Connect OLEDB Source to Template Transform. If you need flexible XML Generation then use XML Generator Transform<\/a> but it may require some learning curve so for simplicity we are skipping that from this article.<\/li>\n
          4. Enter your SOAP request in the template text (like below) you like to call (This is obtained from the previous section – using tool like SoapUI<\/a>)
            \nFor Example:<\/strong> To create a new account you can use enter like below. Replace\u00a0xxxxxxxxxx with Columns placeholder.
            \nTo insert Column name as Placeholder click <<Insert Placeholder>> and then Select [Columns] node. Template Transform outputs column name TemplateOutput<\/strong>. You can use this as Body to feed next step (i.e. Call Workday API using Web API Destination )When you insert placeholder to make sure you use XML Encoded Columns if you expecting a Long text or special characters part of your data.
            \nSyntax for encoded value is\u00a0\u00a0\u00a0<%CustomerName,FUN_XMLENC%><\/span>\u00a0 . You don’t need FUN_XMLENC<\/strong> for numeric fields. For normal placeholder without encoding use just name with column placeholder indicators e.g.\u00a0\u00a0<%Amount%><\/span><\/p>\n
            <soapenv:Envelope xmlns:soapenv=\"http:\/\/schemas.xmlsoap.org\/soap\/envelope\/\" xmlns:bsvc=\"urn:com.workday\/bsvc\">\r\n   <soapenv:Header\/>\r\n   <soapenv:Body>\r\n      <bsvc:Workday_Account_for_Worker_Add>\r\n         <bsvc:Worker_Reference>\r\n            <!--You have a CHOICE of the next 2 items at this level-->\r\n            <bsvc:Employee_Reference>\r\n               <bsvc:Integration_ID_Reference>\r\n                  <bsvc:ID>xxxxxxxxxx<\/bsvc:ID>\r\n               <\/bsvc:Integration_ID_Reference>\r\n            <\/bsvc:Employee_Reference>\r\n            <bsvc:Contingent_Worker_Reference>\r\n               <bsvc:Integration_ID_Reference>\r\n                  <bsvc:ID>xxxxxxxxxxxx<\/bsvc:ID>\r\n               <\/bsvc:Integration_ID_Reference>\r\n            <\/bsvc:Contingent_Worker_Reference>\r\n         <\/bsvc:Worker_Reference>\r\n         <bsvc:Workday_Account_for_Worker_Data>\r\n            <!--type: string-->\r\n            <bsvc:User_Name>xxxxxxxxxxxxxx<\/bsvc:User_Name>\r\n         <\/bsvc:Workday_Account_for_Worker_Data>\r\n      <\/bsvc:Workday_Account_for_Worker_Add>\r\n   <\/soapenv:Body>\r\n<\/soapenv:Envelope><\/pre>\n
            \"Template<\/a>
            Template Transform –
            Create Workday SOAP Request – Create new records using SSIS<\/p><\/div><\/li>\n
          5. Drag\u00a0ZS Web API Destination<\/a>\u00a0from SSIS Toolbox. Connect Template Transform to Web API Destination.<\/li>\n
          6. Configure Web API Destination as below (See we used same HTTP connection created in the previous section)<\/li>\n
          7. Select HTTP Connection<\/li>\n
          8. Select Input Column for Body (select TemplateOutput<\/strong>)<\/li>\n
          9. Select Body Content Type as\u00a0XML (text\/xml;charset=UTF-8)<\/strong><\/li>\n
          10. Click Raw Edit above Headers grid. Enter following text (We just need one header)\n
            SOAPAction: \"\"<\/pre>\n
            \"SSIS<\/a>
            SSIS Web API Destination –
            Loading SQL Server data to Workday (SOAP Call – Create, Insert, Update, Delete Records)<\/p><\/div><\/li>\n
          11. Now you can connect Web API Destination Output (Blue Arrow to Either XML Parser or some other Destination). Web API destination gives you a response in Raw XML Format. This article explains how to use JSON or XML Parser to parse API response<\/a> coming from Web API destination.<\/li>\n<\/ol>\n
            Video Tutorial (See Part#4) – Create XML and POST data to SOAP Web Service or REST API<\/h2>\n
            Now lets look at examples of creating XML from multiple data sources and POST XML request to SOAP Web Service URL or any other XML Based REST API URL. This video has 5 parts to cover full length tutorial but if you want to fast forward to see XML Generator Transform and Web API Destination then see part#4.
            \n
            <\/div><\/div>
            <\/div>
            <\/div>
            <\/div><\/div><\/div>