How to read data from NetSuite in SSIS (SimpleTalk SOAP API)

SSIS designer installed. Sometimes it is referred to as BIDS or SSDT (download it from the Microsoft site).
Basic knowledge of SSIS package development using Microsoft SQL Server Integration Services.
Make sure ZappySys SSIS PowerPack is installed (download it, if you haven't already).
(Optional step). Read this article, if you are planning to deploy packages to a server and schedule their execution later.

What is NetSuite CRM?

NetSuite is a CRM / ERP product. It gives you scalable cloud CRM / ERP solution targeted at high-growing, mid-sized businesses and large enterprises. It automates front- and back-office processes including: financial management, revenue management, fixed assets, order management, billing, and inventory management.

Preparing for NetSuite WebService Call in SSIS

For NetSuite API call we have to make sure certain settings and obtain Application ID and AccountID. So let’s get started.

How to enable NetSuite Web Service Feature

Before you call any NetSuite SOAP API (i.e. SuiteTask Web Service), First thing to get started with NetSuite SOAP API (i.e. ) is to enable Web Service Feature. Once you enable Web Service, you can programmatically access NetSuite data in your App (e.g. Power BI, Tableau, Informatica). Click this link to see steps to enable Web Service Feature

Enable NetSuite Web Service Feature (SuiteTalk API)

Obtain NetSuite Account ID

NetSuite Account ID is needed by Login API call. So perform following steps if you dont know account id.

Login to your NetSuite Portal
Click the Setup Tab
In the drop-down click Integration > Web services preferences
On that page you will find your Account ID (example account ID = TSTDRV1234567 )

Obtain NetSuite Account ID (Needed for Web Service Login)

Obtain NetSuite Application ID

Once you have account ID now we need to get Application ID. For that follow these steps.

Login to your NetSuite Portal
Click the Setup Tab
In the drop-down click Integration > Manage Integrations
On that page you will find your existing Applications along with Account ID / Status (example Application ID = AE2EACFD-1234-1222-1111-12345544555 )
If you don’t have any application created yet then just click on New.

Create new App / Get NetSuite Application ID

Understanding how NetSuite SOAP API Call works

In this section we will briefly understand how NetSuite SOAP API call works. When you use ZappySys Driver / Component it will take care many details for you but still you have to configure correct XML Body. There are two steps involved before you can call any SOAP API in NetSuite.

Call Login method and extract JSESSIONID from response Cookie (This token expires after some time)
Call any other API (Pass obtained JSESSIONID token in Cookie header)

NetSuite Login Call

Request

POST https://webservices.netsuite.com/services/NetSuitePort_2017_1
Content-Type: text/xml;charset=UTF-8
SOAPAction: "login"
Accept-Encoding: gzip, deflate

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Header>
    <applicationInfo xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <applicationId>xxxxxxxxxxxxxx</applicationId>
    </applicationInfo>
  </soap:Header>
  <soap:Body>
    <login xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <passport>
        <email xmlns="urn:core_2017_1.platform.webservices.netsuite.com">xxxxxxxxxxxxx</email>
        <password xmlns="urn:core_2017_1.platform.webservices.netsuite.com">xxxxxxxxxxxx</password>
        <account xmlns="urn:core_2017_1.platform.webservices.netsuite.com">TSTxxxxxxxxxxxxxxx</account>
        <role internalId="3" xmlns="urn:core_2017_1.platform.webservices.netsuite.com" />
      </passport>
    </login>
  </soap:Body>
</soap:Envelope>

Response Notice JSESSIONID=6xI6xxxxxxxxx in the login call response below. This Session ID can be used to call next call. ZappySys HTTP Connection Manager configured with Dynamic Token option can extract Cookie and reuse it until its expired (1 hour by default).

HTTP/1.1 200 OK
Date: Wed, 03 Oct 2018 16:50:41 GMT
Content-Length: 1676
Content-Type: text/xml; charset=utf-8
X-N-OperationId: 777274f6-49c6-4883-bcd1-1d70df90d78e
Strict-Transport-Security: max-age=31536000
NS_RTIMER_COMPOSITE: 13633xxxxxxxxx:70617xxxxxxxxxxxxx36F6D:80
Set-Cookie: JSESSIONID=6xI61rdFt26pxxxxxxxxxxxxxxxqvJIMlu!-17981123445; path=/; secure; HttpOnly
Set-Cookie: NS_VER=2018.1.0; domain=webservices.netsuite.com; path=/
Set-Cookie: NS_ROUTING_VERSION=LAGGING; path=/
P3P: CP="CAO PSAa OUR BUS PUR"
Vary: User-Agent
Keep-Alive: timeout=10, max=990
Connection: Keep-Alive

<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Header>
    <platformMsgs:documentInfo xmlns:platformMsgs="urn:messages_2017_1.platform.webservices.netsuite.com">
      <platformMsgs:nsId>WEBSERVICES_xxxxxxxxxxxxxxxxx_9fc719</platformMsgs:nsId>
    </platformMsgs:documentInfo>
  </soapenv:Header>
  <soapenv:Body>
    <loginResponse xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <sessionResponse xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
        <ns1:status isSuccess="true" xmlns:ns1="urn:core_2017_1.platform.webservices.netsuite.com"/>
        <ns2:wsRoleList xmlns:ns2="urn:core_2017_1.platform.webservices.netsuite.com">
          <ns2:wsRole>
            <ns2:role internalId="3">
              <ns2:name>Administrator</ns2:name>
            </ns2:role>
            <ns2:isDefault>false</ns2:isDefault>
            <ns2:isInactive>false</ns2:isInactive>
          </ns2:wsRole>
          <ns2:wsRole>
            <ns2:role internalId="15">
              <ns2:name>Employee Center</ns2:name>
            </ns2:role>
            <ns2:isDefault>false</ns2:isDefault>
            <ns2:isInactive>false</ns2:isInactive>
          </ns2:wsRole>
      </sessionResponse>
    </loginResponse>
  </soapenv:Body>
</soapenv:Envelope>

NetSuite API Call ( getAll method )

Now once you have JSESSIONID extracted you can pass it to call next API call as below. For example here is how to call getAll method to obtain currency list and exchange rates. If you are not sure how to craft correct Request XML then use tool like SoapUI. You can use NetSuite WSDL file from here and import it in SoapUI.

Request See how Cookie is passed for authentication purpose. Again you can pass this manually or use HTTP connection with Dynamic Token to pass it automatically along with each API call.

POST https://webservices.netsuite.com/services/NetSuitePort_2017_1
Content-Type: text/xml;charset=UTF-8
SOAPAction: "getAll"
Cookie: JSESSIONID=6xI61rxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxMlu!-1798141234
Accept-Encoding: gzip, deflate

<soapenv:Envelope 
   xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
   xmlns:urn="urn:messages_2018_1.platform.webservices.netsuite.com" 
   xmlns:urn1="urn:core_2018_1.platform.webservices.netsuite.com">
   <soapenv:Body>
      <urn:getAll>
         <record recordType="currency"/> 
      </urn:getAll>
   </soapenv:Body>
</soapenv:Envelope>

Response

HTTP/1.1 200 OK
Date: Wed, 03 Oct 2018 16:50:42 GMT
Content-Length: 9648
Content-Type: text/xml; charset=utf-8
X-N-OperationId: 1b2989e4-b930-4a72-b471-00a8e131dfd4
NS_RTIMER_COMPOSITE: 21403xxxxxx:706172746E65xxxxxxxxxxxx6F6D:80
Strict-Transport-Security: max-age=31536000
P3P: CP="CAO PSAa OUR BUS PUR"
Vary: User-Agent

<?xml version="1.0" encoding="UTF-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Header>
    <platformMsgs:documentInfo xmlns:platformMsgs="urn:messages_2017_1.platform.webservices.netsuite.com">
      <platformMsgs:nsId>WEBSERVICES_TSTDRV1439151_100320189871507121000128715_6ae8e8</platformMsgs:nsId>
    </platformMsgs:documentInfo>
  </soapenv:Header>
  <soapenv:Body>
    <getAllResponse xmlns="urn:messages_2018_1.platform.webservices.netsuite.com">
      <platformCore:getAllResult xmlns:platformCore="urn:core_2017_1.platform.webservices.netsuite.com">
        <platformCore:status isSuccess="true" />
        <platformCore:totalRecords>11</platformCore:totalRecords>
        <platformCore:recordList>
          <platformCore:record internalId="1" xsi:type="listAcct:Currency" xmlns:listAcct="urn:accounting_2017_1.lists.webservices.netsuite.com">
            <listAcct:name>USD</listAcct:name>
            <listAcct:symbol>USD</listAcct:symbol>
            <listAcct:isBaseCurrency>true</listAcct:isBaseCurrency>
            <listAcct:isInactive>false</listAcct:isInactive>
            <listAcct:overrideCurrencyFormat>false</listAcct:overrideCurrencyFormat>
            <listAcct:displaySymbol>$</listAcct:displaySymbol>
            <listAcct:symbolPlacement>_beforeNumber</listAcct:symbolPlacement>
            <listAcct:locale>_unitedStatesEnglish</listAcct:locale>
            <listAcct:formatSample>$1,234.56</listAcct:formatSample>
            <listAcct:exchangeRate>1.0</listAcct:exchangeRate>
            <listAcct:fxRateUpdateTimezone>_americaNewYork</listAcct:fxRateUpdateTimezone>
            <listAcct:currencyPrecision>_two</listAcct:currencyPrecision>
          </platformCore:record>
          <platformCore:record internalId="2" xsi:type="listAcct:Currency" xmlns:listAcct="urn:accounting_2017_1.lists.webservices.netsuite.com">
            <listAcct:name>GBP</listAcct:name>
            <listAcct:symbol>GBP</listAcct:symbol>
            <listAcct:isBaseCurrency>true</listAcct:isBaseCurrency>
            <listAcct:isInactive>false</listAcct:isInactive>
            <listAcct:overrideCurrencyFormat>false</listAcct:overrideCurrencyFormat>
            <listAcct:displaySymbol>£</listAcct:displaySymbol>
            <listAcct:symbolPlacement>_beforeNumber</listAcct:symbolPlacement>
            <listAcct:locale>_unitedKingdomEnglish</listAcct:locale>
            <listAcct:formatSample>£1,234.56</listAcct:formatSample>
            <listAcct:exchangeRate>1.33165497</listAcct:exchangeRate>
            <listAcct:fxRateUpdateTimezone>_americaNewYork</listAcct:fxRateUpdateTimezone>
            <listAcct:currencyPrecision>_two</listAcct:currencyPrecision>
          </platformCore:record>
          <platformCore:record internalId="3" xsi:type="listAcct:Currency" xmlns:listAcct="urn:accounting_2017_1.lists.webservices.netsuite.com">
            <listAcct:name>CAD</listAcct:name>
            <listAcct:symbol>CAD</listAcct:symbol>
            <listAcct:isBaseCurrency>true</listAcct:isBaseCurrency>
            <listAcct:isInactive>false</listAcct:isInactive>
            <listAcct:overrideCurrencyFormat>false</listAcct:overrideCurrencyFormat>
            <listAcct:displaySymbol>$</listAcct:displaySymbol>
            <listAcct:symbolPlacement>_beforeNumber</listAcct:symbolPlacement>
            <listAcct:locale>_canadaEnglish</listAcct:locale>
            <listAcct:formatSample>$1,234.56</listAcct:formatSample>
            <listAcct:exchangeRate>0.76457273</listAcct:exchangeRate>
            <listAcct:fxRateUpdateTimezone>_americaNewYork</listAcct:fxRateUpdateTimezone>
            <listAcct:currencyPrecision>_two</listAcct:currencyPrecision>
          </platformCore:record>
        </platformCore:recordList>
      </platformCore:getAllResult>
    </getAllResponse>
  </soapenv:Body>
</soapenv:Envelope>

NetSuite Search API (Pagination Example)

Now let's look at more complex API calls where Pagination is needed. For example when you call search API to pull data from specific table (e.g. Account) you need to loop through multiple responses until all records are consumed. Request (Page-1)

POST https://webservices.netsuite.com/services/NetSuitePort_2017_1
Content-Type: text/xml; charset=utf-8
SOAPAction: "search"
Cookie: JSESSIONID=BKQ7xxxxxxxxxxxx4Ug!-995xxxxxx
Accept-Encoding: gzip, deflate

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Header>
    <applicationInfo xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <applicationId>xxxxxxxxxxxxxxx</applicationId>
    </applicationInfo>
    <searchPreferences xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <pageSize>50</pageSize>
    </searchPreferences>
  </soap:Header>
  <soap:Body>
    <search xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <searchRecord xmlns:q1="urn:accounting_2017_1.lists.webservices.netsuite.com" xsi:type="q1:AccountSearch">
        <q1:basic>
          <description operator="doesNotContain" xmlns="urn:common_2017_1.platform.webservices.netsuite.com">
            <searchValue xmlns="urn:core_2017_1.platform.webservices.netsuite.com">x</searchValue>
          </description>
          <name operator="contains" xmlns="urn:common_2017_1.platform.webservices.netsuite.com">
            <searchValue xmlns="urn:core_2017_1.platform.webservices.netsuite.com">a</searchValue>
          </name>
        </q1:basic>
      </searchRecord>
    </search>
  </soap:Body>
</soap:Envelope>

Response (Page-1) In below response notice 2 things. SearchId is like a cursor id which you have to pass for each page you request after first page. And you can stop looping after 4th page based on totalPages count. platformCore:searchId = WEBSERVICES_ABCD_12345-xxxxxxxxx platformCore:totalPages = 4

HTTP/1.1 200 OK
Date: Wed, 03 Oct 2018 18:40:33 GMT
Content-Length: 49266
Content-Type: text/xml; charset=utf-8
X-N-OperationId: c1e79336-63b7-4eb7-989e-5c3d24c56891
NS_RTIMER_COMPOSITE: 118xxxxxxxx:70617xxxxxxxxxxxxxxxxxxxxxD:80
Strict-Transport-Security: max-age=31536000
P3P: CP="CAO PSAa OUR BUS PUR"
Vary: User-Agent

<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Header>
    <platformMsgs:documentInfo xmlns:platformMsgs="urn:messages_2017_1.platform.webservices.netsuite.com">
      <platformMsgs:nsId>WEBSERVICES_ABCD_12345-xxxxxxxxx</platformMsgs:nsId>
    </platformMsgs:documentInfo>
  </soapenv:Header>
  <soapenv:Body>
    <searchResponse xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <platformCore:searchResult xmlns:platformCore="urn:core_2017_1.platform.webservices.netsuite.com">
        <platformCore:status isSuccess="true" />
        <platformCore:totalRecords>193</platformCore:totalRecords>
        <platformCore:pageSize>50</platformCore:pageSize>
        <platformCore:totalPages>4</platformCore:totalPages>
        <platformCore:pageIndex>1</platformCore:pageIndex>
        <platformCore:searchId>WEBSERVICES_ABCD_12345-xxxxxxxxx</platformCore:searchId>
        <platformCore:recordList>
          <platformCore:record internalId="1" xsi:type="listAcct:Account" xmlns:listAcct="urn:accounting_2017_1.lists.webservices.netsuite.com">
            <listAcct:acctType>_bank</listAcct:acctType>
            <listAcct:acctNumber>1111</listAcct:acctNumber>
            <listAcct:acctName>Checking</listAcct:acctName>
            <listAcct:includeChildren>false</listAcct:includeChildren>
            <listAcct:currency internalId="1">
              <platformCore:name>USD</platformCore:name>
            </listAcct:currency>
            <listAcct:generalRate>_current</listAcct:generalRate>
            <listAcct:parent internalId="246">
              <platformCore:name>xxxxxxxxxxxxxxxxxxx</platformCore:name>
            </listAcct:parent>
            <listAcct:cashFlowRate>_average</listAcct:cashFlowRate>
            <listAcct:description>Checking</listAcct:description>
            <listAcct:isInactive>false</listAcct:isInactive>
            <listAcct:inventory>false</listAcct:inventory>
            <listAcct:eliminate>false</listAcct:eliminate>
            <listAcct:revalue>true</listAcct:revalue>
          </platformCore:record>
          <platformCore:record internalId="2" xsi:type="listAcct:Account" xmlns:listAcct="urn:accounting_2017_1.lists.webservices.netsuite.com">
            <listAcct:acctType>_bank</listAcct:acctType>
            <listAcct:acctNumber>22222</listAcct:acctNumber>
            <listAcct:acctName>Savings</listAcct:acctName>
            <listAcct:includeChildren>false</listAcct:includeChildren>
            <listAcct:currency internalId="1">
              <platformCore:name>USD</platformCore:name>
            </listAcct:currency>
            <listAcct:generalRate>_current</listAcct:generalRate>
            <listAcct:parent internalId="246">
              <platformCore:name>yyyyyyyyy</platformCore:name>
            </listAcct:parent>
            <listAcct:cashFlowRate>_average</listAcct:cashFlowRate>
            <listAcct:description>Savings</listAcct:description>
            <listAcct:isInactive>false</listAcct:isInactive>
            <listAcct:inventory>false</listAcct:inventory>
            <listAcct:eliminate>false</listAcct:eliminate>
            <listAcct:revalue>true</listAcct:revalue>
          </platformCore:record>
          .........
          .........
          .........
          .........		  
        </platformCore:recordList>
      </platformCore:searchResult>
    </searchResponse>
  </soapenv:Body>
</soapenv:Envelope>

Request (Page-2 and onwards) Here is the second page request. Notice few things here. In heraders we are now sending SOAPAction: "searchMoreWithId" rather than "search". Also our SOAP Body is also different compared to first page request. Now we only sending pageIndex we want to fetch and searchId. We have also change <search> tag with <searchMoreWithId> under Body.

POST https://webservices.netsuite.com/services/NetSuitePort_2017_1
Content-Type: text/xml; charset=utf-8
SOAPAction: "searchMoreWithId"
Cookie: JSESSIONID=BKQ7xxxxxxxxxxxx4Ug!-995xxxxxx
Accept-Encoding: gzip, deflate

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Header>
    <applicationInfo xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <applicationId>xxxxxxxxxxxxxxxxxx</applicationId>
    </applicationInfo>
    <searchPreferences xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <pageSize>50</pageSize>
    </searchPreferences>
  </soap:Header>
  <soap:Body>
    <searchMoreWithId xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <searchId>WEBSERVICES_ABCD_12345-xxxxxxxxx</searchId>
      <pageIndex>2</pageIndex>
    </searchMoreWithId>
  </soap:Body>
</soap:Envelope>

Response (Page-2 and onwards)

HTTP/1.1 200 OK
Date: Wed, 03 Oct 2018 18:40:33 GMT
Content-Length: 49266
Content-Type: text/xml; charset=utf-8
X-N-OperationId: c1e79336-63b7-4eb7-989e-5c3d24c56891
NS_RTIMER_COMPOSITE: 118xxxxxxxx:70617xxxxxxxxxxxxxxxxxxxxxD:80
Strict-Transport-Security: max-age=31536000
P3P: CP="CAO PSAa OUR BUS PUR"
Vary: User-Agent

<?xml version="1.0" encoding="utf-8"?>
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <soapenv:Header>
    <platformMsgs:documentInfo xmlns:platformMsgs="urn:messages_2017_1.platform.webservices.netsuite.com">
      <platformMsgs:nsId>WEBSERVICES_ABCD_12345-xxxxxxxxx</platformMsgs:nsId>
    </platformMsgs:documentInfo>
  </soapenv:Header>
  <soapenv:Body>
    <searchMoreWithIdResponse xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <platformCore:searchResult xmlns:platformCore="urn:core_2017_1.platform.webservices.netsuite.com">
        <platformCore:status isSuccess="true" />
        <platformCore:totalRecords>193</platformCore:totalRecords>
        <platformCore:pageSize>50</platformCore:pageSize>
        <platformCore:totalPages>4</platformCore:totalPages>
        <platformCore:pageIndex>2</platformCore:pageIndex>
        <platformCore:searchId>WEBSERVICES_ABCD_12345-xxxxxxxxx</platformCore:searchId>
        <platformCore:recordList>
          <platformCore:record internalId="1" xsi:type="listAcct:Account" xmlns:listAcct="urn:accounting_2017_1.lists.webservices.netsuite.com">
            <listAcct:acctType>_bank</listAcct:acctType>
            <listAcct:acctNumber>8000</listAcct:acctNumber>
            <listAcct:acctName>Checking</listAcct:acctName>
            <listAcct:includeChildren>false</listAcct:includeChildren>
            <listAcct:currency internalId="1">
              <platformCore:name>USD</platformCore:name>
            </listAcct:currency>
            <listAcct:generalRate>_current</listAcct:generalRate>
            <listAcct:parent internalId="246">
              <platformCore:name>xxxxxxxxxxxxxxxxxxx</platformCore:name>
            </listAcct:parent>
            <listAcct:cashFlowRate>_average</listAcct:cashFlowRate>
            <listAcct:description>Checking</listAcct:description>
            <listAcct:isInactive>false</listAcct:isInactive>
            <listAcct:inventory>false</listAcct:inventory>
            <listAcct:eliminate>false</listAcct:eliminate>
            <listAcct:revalue>true</listAcct:revalue>
          </platformCore:record>
          <platformCore:record internalId="2" xsi:type="listAcct:Account" xmlns:listAcct="urn:accounting_2017_1.lists.webservices.netsuite.com">
            <listAcct:acctType>_bank</listAcct:acctType>
            <listAcct:acctNumber>8001</listAcct:acctNumber>
            <listAcct:acctName>Savings</listAcct:acctName>
            <listAcct:includeChildren>false</listAcct:includeChildren>
            <listAcct:currency internalId="1">
              <platformCore:name>USD</platformCore:name>
            </listAcct:currency>
            <listAcct:generalRate>_current</listAcct:generalRate>
            <listAcct:parent internalId="246">
              <platformCore:name>yyyyyyyyy</platformCore:name>
            </listAcct:parent>
            <listAcct:cashFlowRate>_average</listAcct:cashFlowRate>
            <listAcct:description>Savings</listAcct:description>
            <listAcct:isInactive>false</listAcct:isInactive>
            <listAcct:inventory>false</listAcct:inventory>
            <listAcct:eliminate>false</listAcct:eliminate>
            <listAcct:revalue>true</listAcct:revalue>
          </platformCore:record>
          .........
          .........
          .........
          .........		  
        </platformCore:recordList>
      </platformCore:searchResult>
    </searchMoreWithIdResponse>
  </soapenv:Body>
</soapenv:Envelope>

Getting Started

Now let’s check step by step instructions on how to load NetSuite data in SSIS. In this exercise we will focus on calling search API but you can call virtually any API using the same technique.

For detailed help on NetSuite SOAP API and other useful information check this documentation. To browse schema you can use this link. From schema browser we will use Search Type name later on in this article. For example later on in this article to search accounts we will use AccountSearch as below example. This name can be found from here(click search tab) You can change it to something else. For now dont worry too much until we get to that point.

<searchRecord xmlns:q1="urn:accounting_2017_1.lists.webservices.netsuite.com" xsi:type="q1:AccountSearch">

Configure SSIS HTTP Connection for NetSuite

Now let’s look at how to configure SSIS HTTP Connection for NetSuite API login. We will use Dynamic Token Approach.

Open SSIS Package
Drag Data flow from SSIS Toolbox and double click it
Inside data flow SSIS toolbox, find ZS XML Source and drag it on the surface.
Double click it to configure. First enter NetSuite API URL. If you need to use latest WebService version e.g. NetSuitePort_2019_1 then you must use company specific endpoint.

https://webservices.netsuite.com/services/NetSuitePort_2017_1

1

https://webservices.netsuite.com/services/NetSuitePort_2017_1

–OR–
To use NetSuitePort_2019_1 or higher use company specific endpoint like below otherwise you will get error. NOTE: You must change Body in coming section to match Version if you use below way.

https://111222333444.suitetalk.api.netsuite.com/services/NetSuitePort_2019_1

1

https://111222333444.suitetalk.api.netsuite.com/services/NetSuitePort_2019_1
Check Use Credentials option and Click New HTTP connection.
Configure general properties of connections as below.
1. Select Credentials Type as Dynamic Token
2. Use any URL (It will be ignored)
3. Enter your NetSuite UserID, Password
4. Enter Header as Cookie
5. Enter AUth Scheme as JSESSIONID=
First Screen will look like as below. Click Configure.
Configure SSIS NetSuite Connection

On Dynamic Token > Request Tab we have to set Token URL, Method, Body, Header.

In the URL enter same URL again

https://webservices.netsuite.com/services/NetSuitePort_2017_1

1

https://webservices.netsuite.com/services/NetSuitePort_2017_1

In the Body enter as below. Change APPLICATION-ID and ACCOUNT-ID with correct ID we obtained earlier. [$userid$] and [$password$] is special placeholders will be replaced automatically at runtime.

NOTE: Try to match your URL version in the Body Request. If you used For example if you used 2019_1 in URL then change below body to match that version

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Header>
    <applicationInfo xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <applicationId>xxxxxxYOUR-APPLICATION-IDxxxxxx</applicationId>
    </applicationInfo>
  </soap:Header>
  <soap:Body>
    <login xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <passport>
        <email xmlns="urn:core_2017_1.platform.webservices.netsuite.com">[$userid$]</email>
        <password xmlns="urn:core_2017_1.platform.webservices.netsuite.com">[$password$]</password>
        <account xmlns="urn:core_2017_1.platform.webservices.netsuite.com">xxxxxxYOUR-ACCOUNT-IDxxxxxx</account>
        <role internalId="3" xmlns="urn:core_2017_1.platform.webservices.netsuite.com" />
      </passport>
    </login>
  </soap:Body>
</soap:Envelope>

<?xml version="1.0" encoding="utf-8"?>

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<soap:Header>

<applicationId>xxxxxxYOUR-APPLICATION-IDxxxxxx</applicationId>

</applicationInfo>

</soap:Header>

<soap:Body>

<email xmlns="urn:core_2017_1.platform.webservices.netsuite.com">[$userid$]</email>

<password xmlns="urn:core_2017_1.platform.webservices.netsuite.com">[$password$]</password>

<account xmlns="urn:core_2017_1.platform.webservices.netsuite.com">xxxxxxYOUR-ACCOUNT-IDxxxxxx</account>

</passport>

</login>

</soap:Body>

</soap:Envelope>

NOTE: Remove below line from above Body if you get INVALID_ROLE error later in this tutorial. If if you get INVALID_LOGIN_CREDENTIALS error then refer here.

<role internalId="3" xmlns="urn:core_2017_1.platform.webservices.netsuite.com" />

1	<role internalId="3" xmlns="urn:core_2017_1.platform.webservices.netsuite.com" />

Select Content Type as text/xml
In the Headers click on Raw Edit Button and enter like below.

SOAPAction: "login"

1

SOAPAction: "login"

Once all settings done your Request Tab will look like below
SSIS NetSuite Connection – Configure Token Request
On Response Tab configure like below. You have to enter Cookie name as JSESSIONID
SSIS NetSuite Connection – Configure Token Response
Click OK to Save Connection.
We will configure XML Source in next section.

Configure SSIS XML Source to read NetSuite data

Now let’s continue configuration of XML Source. In this section we will show you how to get data without pagination. In next section we will see how to modify XML Source Settings to pagination request to read many records (e.g. 1000+ rows).

On your XML Source enter NetSuite Webservice URL like below.

https://webservices.netsuite.com/services/NetSuitePort_2017_1

1

https://webservices.netsuite.com/services/NetSuitePort_2017_1
Now check Use Credentials and select previously created HTTP connection.
Select Method as POST and content type as text/xml

Edit Request Body and enter like below (or any other XML body for API you like to call. Make sure SOAPAction in next step updated correctly too for other action)

NOTE: Try to match your URL version in the Body Request. If you used For example if you used 2019_1 in URL then change below body to match that version

<soapenv:Envelope 
   xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
   xmlns:urn="urn:messages_2018_1.platform.webservices.netsuite.com" 
   xmlns:urn1="urn:core_2018_1.platform.webservices.netsuite.com">
   <soapenv:Body>
      <urn:getAll>
         <record recordType="currency"/> 
      </urn:getAll>
   </soapenv:Body>
</soapenv:Envelope>

<soapenv:Envelope

xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"

xmlns:urn="urn:messages_2018_1.platform.webservices.netsuite.com"

xmlns:urn1="urn:core_2018_1.platform.webservices.netsuite.com">

<soapenv:Body>

<urn:getAll>

</urn:getAll>

</soapenv:Body>

</soapenv:Envelope>

NOTE: If you used NetSuitePort_2019_1 or higher version in URL then make sure you changed version 2017_1 to 2019_1 in below body
Now you click Raw Edit above headers grid and enter method name in SOAPAction (e.g. getAll in our case)

SOAPAction: "getAll"

1

SOAPAction: "getAll"

NOTE: SOAPAction Header is very important so make sure you change it to match Action you like to call. You can Use Correct WebService WSDL File (Metadata) in Tool like SOAPUI as explained here
Version 2017_1 WSDL Link
Version 2018_1 WSDL Link
Version 2019_1 WSDL Link
For Other Versions Just Change URL Version Part to different Number e.g. 2019_2_0
Now enter Filter like below or navigate by clicking Select Filter button.

$.soapenv:Envelope.soapenv:Body.getAllResponse.platformCore:getAllResult.platformCore:recordList.platformCore:record[*]

1

$.soapenv:Envelope.soapenv:Body.getAllResponse.platformCore:getAllResult.platformCore:recordList.platformCore:record[*]

Configure XML Source – NetSuite SOAP API getAll (Body, Header, ContentType, Method, Filter)
Now click preview data.
Preview NetSuite data in XML Source
Click OK to save XML Source UI
Now you can connect XML Source to target like OLEDB Destination and load NetSuite data into SQL Server.

Configure SSIS XML Source with Pagination

So in previous section we saw how to configure XML Source for basic API calls (without pagination). In this section we will see how to call more complex API (e.g. search ) which requires pagination. Some ideas about NetSuite Pagination discussed here too.

Foe pagination setup using XML Source we have to configure following properties via Properties Grid or Advanced Edit rather than Normal UI. May of these properties not available via UI as of now.

Create new data flow and drag new ZS XML Source.
Right click on XML Source and click Properties in Data flow designer. Find property by name in the Grid and set correct value.
NOTE: In newer version v3.1.2 or higher you can use XML Source UI to enter some multi line properties for ease of use (e.g. FirstPageBodyPart and NextPageBodyPart can be entered on Advanced Pagination Tab, all other properties are easier to enter from Grid Mode if you sort alphabetically)

ElementsToTreatAsArray

platformCore:record

EnablePageTokenForBody

True

Filter

$.soapenv:Envelope.soapenv:Body.search[$tag$]Response.platformCore:searchResult.platformCore:recordList.platformCore:record[*]

FirstPageBodyPart

    <search xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <searchRecord xmlns:q1="urn:accounting_2017_1.lists.webservices.netsuite.com" xsi:type="q1:AccountSearch">
        <q1:basic>
          <description operator="doesNotContain" xmlns="urn:common_2017_1.platform.webservices.netsuite.com">
            <searchValue xmlns="urn:core_2017_1.platform.webservices.netsuite.com">x</searchValue>
          </description>
          <name operator="contains" xmlns="urn:common_2017_1.platform.webservices.netsuite.com">
            <searchValue xmlns="urn:core_2017_1.platform.webservices.netsuite.com">a</searchValue>
          </name>
        </q1:basic>
      </searchRecord>
    </search>

<q1:basic>

</description>

</name>

</q1:basic>

</searchRecord>

</search>

Change as per your need but we used above example search

HasDifferentNextPageInfo

True

NextPageBodyPart

    <searchMoreWithId xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <searchId>[$pagetoken$]</searchId>
      <pageIndex>[$pagenumber$]</pageIndex>
    </searchMoreWithId>

<searchId>[$pagetoken$]</searchId>

<pageIndex>[$pagenumber$]</pageIndex>

</searchMoreWithId>

NextUrlAttribute

$.soapenv:Envelope.soapenv:Body.search[$tag$]Response.platformCore:searchResult.platformCore:searchId

PagePlaceholders

header=|MoreWithId;filter=|MoreWithId

PagesMaxPagesExpr

$.soapenv:Envelope.soapenv:Body.searchResponse.platformCore:searchResult.platformCore:totalPages

Once above items are set in property grid. Double click XML source to edit few more items from UI.

Enter URL as below

https://webservices.netsuite.com/services/NetSuitePort_2017_1

1

https://webservices.netsuite.com/services/NetSuitePort_2017_1
Check Use Credentials and select HTTP connection we created in previous section.
Click Raw Edit button (above headers grid) and enter below

SOAPAction: "search[$tag$]"

1

SOAPAction: "search[$tag$]"

Click on Edit button next to Body. Enter following XML fragment for search request. Change ApplicationId and adjust PageSize if needed.

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <soap:Header>
    <applicationInfo xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <applicationId>ABCD-1234-XXXXXXXXXXXXXXX</applicationId>
    </applicationInfo>
    <searchPreferences xmlns="urn:messages_2017_1.platform.webservices.netsuite.com">
      <pageSize>100</pageSize>
    </searchPreferences>
  </soap:Header>
  <soap:Body>
[$tag$]
  </soap:Body>
</soap:Envelope>

<?xml version="1.0" encoding="utf-8"?>

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

<soap:Header>

<applicationId>ABCD-1234-XXXXXXXXXXXXXXX</applicationId>

</applicationInfo>

</searchPreferences>

</soap:Header>

<soap:Body>

[$tag$]

</soap:Body>

</soap:Envelope>

Select POST method, change content type to XML (text / xml, charset=UTF-8)
That’s it. Click ‘Preview to confirm.
Click ok to save XML Source.
Read NetSuite data in SSIS with Pagination ( search, searchMoreWithId API call)

Configure SQL Server Destination to load NetSuite data

Once you have XML Source configured you can connect XML Source to Target (OLEDB Destination) and load data into SQL Server.

Conclusion

In this post we explored many advanced options of HTTP Connection and XML Source to enable NetSuite API integration in SSIS. Download SSIS PowerPack today for FREE to uncover many other data integration scenarios not discussed in this article.