{"id":1081,"date":"2017-03-13T19:40:38","date_gmt":"2017-03-13T19:40:38","guid":{"rendered":"http:\/\/zappysys.com\/blog\/?p=1081"},"modified":"2025-03-19T07:04:16","modified_gmt":"2025-03-19T07:04:16","slug":"how-to-read-data-from-servicenow-rest-api-ssis","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/","title":{"rendered":"How to read data from ServiceNow REST API using SSIS"},"content":{"rendered":"<h2>Introduction<\/h2>\n<div class=\"su-note\"  style=\"border-color:#e5de9d;border-radius:3px;-moz-border-radius:3px;-webkit-border-radius:3px;\"><div class=\"su-note-inner su-u-clearfix su-u-trim\" style=\"background-color:#FFF8B7;border-color:#ffffff;color:#333333;border-radius:3px;-moz-border-radius:3px;-webkit-border-radius:3px;\"><strong>UPDATE:<\/strong>\u00a0ZappySys has released a brand new <a href=\"https:\/\/zappysys.com\/api\/integration-hub\/servicenow-connector\/\">API Connector for ServiceNow<\/a> which makes it much simpler to\u00a0<a href=\"https:\/\/zappysys.com\/api\/integration-hub\/servicenow-connector\/ssis\"><strong>Read\/Write ServiceNow Data in SSIS<\/strong><\/a> compared to the steps listed in this article. You can still use steps from this article but if you are new to API or want to avoid learning curve with API then use newer approach.<\/p>\n<p>Please visit <a href=\"https:\/\/zappysys.com\/api\/integration-hub\/\">this page to see all<\/a>\u00a0Pre-Configured ready to use API connectors which you can use in <a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-api-source\/\">SSIS API Source<\/a> \/ <a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/ssis-api-destination\/\">SSIS API Destination<\/a> OR\u00a0<a href=\"https:\/\/zappysys.com\/products\/odbc-powerpack\/odbc-api-driver\/\">API ODBC Driver<\/a> (for non-SSIS Apps such as Excel, Power BI, Informatica).<br \/>\n<\/div><\/div>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-1744 alignleft\" src=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/servicenow-api-integration.png\" alt=\"\" width=\"116\" height=\"116\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/servicenow-api-integration.png 301w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/servicenow-api-integration-150x150.png 150w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/servicenow-api-integration-300x300.png 300w\" sizes=\"(max-width: 116px) 100vw, 116px\" \/><\/p>\n<p>ServiceNow one of the most popular Incident management platform used by many. In this blog post you will learn how to\u00a0get data from ServiceNow REST API\u00a0using <a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/ssis-json-file-source\/\" target=\"_blank\" rel=\"noopener\">SSIS JSON Source<\/a> (No coding required). You will also learn other API integration scenarios such as POST, DELETE, PUT API calls.<\/p>\n<p>You will also learn how to paginate response if you have large amount of data (E.g. more than 10000 rows)<\/p>\n<p>&nbsp;<\/p>\n<h2><span id=\"Prerequisites\">Prerequisites<\/span><\/h2>\n<p>Before we look into Step-By-Step section to calling ServiceNow API in SSIS let\u2019s make sure you met following requirements.<\/p>\n<ol>\n<li>SSIS designer installed.\u00a0Sometimes it is referred as BIDS or SSDT (<a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/ssdt\/download-sql-server-data-tools-ssdt\" target=\"_blank\" rel=\"noopener\">download it from Microsoft site<\/a>).<\/li>\n<li>Basic knowledge of SSIS package\u00a0development using\u00a0<em>Microsoft SQL Server Integration Services<\/em>.<\/li>\n<li><a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/\" target=\"_blank\" rel=\"noopener\"><em>ZappySys SSIS PowerPack<\/em>\u00a0installed<\/a>. Click on the link to download FREE trial.<\/li>\n<li>You have basic familiarity with ServiceNow API and you have UserID and Password handy.<\/li>\n<\/ol>\n<h3><span id=\"Prerequisites\">Step-By-Step : Accessing ServiceNow data using SSIS<\/span><\/h3>\n<p>Now lets look at how to fetch data from ServiceNow API using SSIS JSON Source.<\/p>\n<h3>Configure SSIS HTTP Connection for ServiceNow REST API<\/h3>\n<p>First step to access ServiceNow data is configure HTTP connection on JSON Source.\u00a0In below example we will use <a href=\"https:\/\/docs.servicenow.com\/bundle\/geneva-servicenow-platform\/page\/integrate\/inbound_rest\/concept\/c_RESTAPI.html\" target=\"_blank\" rel=\"noopener\"><strong>ServiceNow REST API<\/strong><\/a>. There are many endpoints for ServiceNow REST API but we will use <a href=\"http:\/\/wiki.servicenow.com\/index.php?title=Table_API\" target=\"_blank\" rel=\"noopener\">Table API Service<\/a> to query table.\u00a0Now lets look at steps to configure ServiceNow API connection<\/p>\n<ol>\n<li>Open SSIS Package and go to Data Flow designer (Click new data flow if missing)<\/li>\n<li>Drag and drop\u00a0 <a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/ssis-json-file-source\/\" target=\"_blank\" rel=\"noopener\">ZS JSON Source<\/a> from SSIS Data Flow Toolbox<\/li>\n<li>Double click JSON Source to configure it as below\n<ol>\n<li>First Enter Full URL to access desired API. For example you can enter URL like below (We entered\u00a0sysparm_limit=2000 to fetch 2000 records in each response. Change this to higher limit to speed up data extract. This number must match on Pagination tab &#8211; see next section). Change <strong>instance001<\/strong> to your own instance name.\n<pre class=\"lang:default highlight:0 decode:true\">https:\/\/instance001.service-now.com\/api\/now\/v2\/table\/incident?sysparm_limit=2000<\/pre>\n<\/li>\n<li>Check Use Credentials option<\/li>\n<li>Click New Connection and configure HTTP connection like below. Once done click OK\n<div id=\"attachment_11188\" style=\"width: 770px\" class=\"wp-caption alignnone\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/ssis-servicenow-rest-api-with-basic-auth.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-11188\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/ssis-servicenow-rest-api-with-basic-auth.png\" alt=\"ssis-servicenow-rest-api-with-basic-auth\" width=\"760\" height=\"803\" class=\"size-full wp-image-11188\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/ssis-servicenow-rest-api-with-basic-auth.png 760w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/ssis-servicenow-rest-api-with-basic-auth-284x300.png 284w\" sizes=\"(max-width: 760px) 100vw, 760px\" \/><\/a><p id=\"caption-attachment-11188\" class=\"wp-caption-text\">Configure ServiceNow REST API Connection in SSIS (Basic Auth using UserID, Password)<\/p><\/div><\/li>\n<li>Click on Select Filter or enter\u00a0<strong>$.result[*]<\/strong> in the filter textbox\n<div id=\"attachment_1738\" style=\"width: 1074px\" class=\"wp-caption alignnone\"><a href=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/configure-ssis-json-source-servicenow-rest-api.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-1738\" class=\"size-full wp-image-1738\" src=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/configure-ssis-json-source-servicenow-rest-api.png\" alt=\"Configure SSIS JSON for ServiceNow REST API - Read data from Table\" width=\"1064\" height=\"741\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/configure-ssis-json-source-servicenow-rest-api.png 1064w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/configure-ssis-json-source-servicenow-rest-api-300x209.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/configure-ssis-json-source-servicenow-rest-api-768x535.png 768w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/configure-ssis-json-source-servicenow-rest-api-1024x713.png 1024w\" sizes=\"(max-width: 1064px) 100vw, 1064px\" \/><\/a><p id=\"caption-attachment-1738\" class=\"wp-caption-text\">Configure SSIS JSON for ServiceNow REST API &#8211; Read data from Table<\/p><\/div><\/li>\n<\/ol>\n<\/li>\n<li>Click Preview to see some data. In next section we will configure Pagination to fetch many records.<\/li>\n<li>If you like to load data to SQL Server then Simply connect your JSON Source to OLEDB Destination and Create New Table &gt; Click Mappings and Click OK<\/li>\n<li>Execute Package<\/li>\n<\/ol>\n<p>&nbsp;<\/p>\n<h3>Configure JSON Source for ServiceNow API Pagination<\/h3>\n<p>By default ServiceNow API doesn&#8217;t return all records from your table. So if you wish to get all records then you must configure Pagination settings like below. For more information on pagination in SSIS <a href=\"\/\/zappysys.com\/blog\/ssis-rest-api-looping-until-no-more-pages-found\/\" target=\"_blank\" rel=\"noopener\">read this article<\/a>.<\/p>\n<div id=\"attachment_11189\" style=\"width: 770px\" class=\"wp-caption alignnone\"><a href=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/ssis-servicenow-rest-api-RFC5988-pagination.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-11189\" src=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/ssis-servicenow-rest-api-RFC5988-pagination.png\" alt=\"ServiceNow REST API Pagination in SSIS\" width=\"760\" height=\"727\" class=\"size-full wp-image-11189\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/ssis-servicenow-rest-api-RFC5988-pagination.png 760w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/ssis-servicenow-rest-api-RFC5988-pagination-300x287.png 300w\" sizes=\"(max-width: 760px) 100vw, 760px\" \/><\/a><p id=\"caption-attachment-11189\" class=\"wp-caption-text\">ServiceNow REST API RFC-5988 Pagination in SSIS<\/p><\/div>\n<p>Once you configure API Pagination it will keep fetching data from new URL automatically until no more data found or it hit Max Row limit you specify on UI (In our case we did max 100 rows). Use <a href=\"https:\/\/zappysys.com\/blog\/how-to-use-fiddler-to-analyze-http-web-requests\/\" target=\"_blank\" rel=\"noopener\">tool like Fiddler<\/a> to view HTTP requests made by JSON Source.<\/p>\n<div id=\"attachment_1742\" style=\"width: 782px\" class=\"wp-caption alignnone\"><a href=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/fiddler-log-api-pagination.png\"><img loading=\"lazy\" decoding=\"async\" aria-describedby=\"caption-attachment-1742\" class=\"size-full wp-image-1742\" src=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/fiddler-log-api-pagination.png\" alt=\"Debugging Web API requests using Fiddler\" width=\"772\" height=\"189\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/fiddler-log-api-pagination.png 772w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/fiddler-log-api-pagination-300x73.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/fiddler-log-api-pagination-768x188.png 768w\" sizes=\"(max-width: 772px) 100vw, 772px\" \/><\/a><p id=\"caption-attachment-1742\" class=\"wp-caption-text\">Debugging Web API requests using Fiddler<\/p><\/div>\n<h3>Configure JSON Source for ServiceNow JSONv2 Pagination (older version API)<\/h3>\n<p>In this section you will learn how to use older version of API known as <a href=\"https:\/\/docs.servicenow.com\/bundle\/jakarta-servicenow-platform\/page\/integrate\/inbound-other-web-services\/concept\/c_JSONv2WebService.html\" target=\"_blank\" rel=\"noopener\">ServiceNow JSONv2 Service<\/a>\u00a0(another <a href=\"http:\/\/wiki.servicenow.com\/index.php?title=JSONv2_Web_Service#gsc.tab=0\" target=\"_blank\" rel=\"noopener\">help link here<\/a>). Syntax for ServiceNow JSONv2 API is below to access table data.<\/p>\n<pre class=\"lang:default highlight:0 decode:true\">https:\/\/&lt;instance name&gt;.service-now.com\/mytable.do?JSONv2\r\nOr\r\nhttps:\/\/&lt;instance name&gt;.service-now.com\/mytable.do?JSONv2&amp;param1=val1&amp;param2=val2....<\/pre>\n<p>Now lets look at steps to configure ServiceNow API connection<\/p>\n<p>To configure JSONv2 API use above steps for most of the things except following settings<\/p>\n<ol>\n<li>Configure URL like below (assuming you want to query\u00a0sys_db_object table, with 15 record max in each response)\n<pre class=\"lang:default highlight:0 decode:true\">https:\/\/instance001.service-now.com\/sys_db_object.do?JSONv2&amp;sysparm_record_count=15&amp;sysparm_query=ws_access=true^sys_mod_count=1^ORDERBYsys_id<\/pre>\n<\/li>\n<li>Configure Pagination Settings like below\n<ol>\n<li>Select Mode = Response Attribute Mode<\/li>\n<li>For NextLink Enter\u00a0<strong>$.records[-1:].sys_id<\/strong><\/li>\n<li>For Suffix for Next URL enter\u00a0<strong>&amp;sysparm_query=sys_id&gt;&lt;%nextlink%&gt;^ws_access=true^sys_mod_count=1^ORDERBYsys_id<\/strong><\/li>\n<li>For Filter enter <strong>$.records[*]<\/strong><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<p><a href=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/ssis-servicenow-jsonv2-rest-api-pagination.png\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1743\" src=\"\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/ssis-servicenow-jsonv2-rest-api-pagination.png\" alt=\"\" width=\"903\" height=\"819\" srcset=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/ssis-servicenow-jsonv2-rest-api-pagination.png 903w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/ssis-servicenow-jsonv2-rest-api-pagination-300x272.png 300w, https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/ssis-servicenow-jsonv2-rest-api-pagination-768x697.png 768w\" sizes=\"(max-width: 903px) 100vw, 903px\" \/><\/a><\/p>\n<h2><span id=\"Loading_Graph_API_Office_365_data_to_SQL_Server\">Loading ServiceNow data to SQL Server<\/span><\/h2>\n<p>Once you done configuring JSON Source you can \u00a0load data to SQL Server. For example purpose we will load data into SQL Server but you can load into any Target (e.g. Flat file, Oracle, Excel) using Microsoft or ZappySys Destination connectors<\/p>\n<p>To load ServiceNow data into SQL Server perform the following steps (Screenshot is used for demonstration for purpose, your values can be different than below)<\/p>\n<ol>\n<li>Drag OLEDB destination on Data Flow surface<\/li>\n<li>Connect ZS JSON Source to Destination<\/li>\n<li>On OLEDB destination select \/ create new SQL Connection and then Click \u201cNew Table\u201d<\/li>\n<li>Click on Mappings tab and click OK to save<\/li>\n<li>Execute Package<\/li>\n<\/ol>\n<div id=\"attachment_1617\" class=\"wp-caption alignnone\">\n<p><a href=\"https:\/\/i0.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-xero-to-sql-server-data-load.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1617\" style=\"border: 0px; max-width: 100%; height: auto; box-shadow: rgba(0, 0, 0, 0.176) 0px 1px 2px;\" src=\"https:\/\/i0.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-xero-to-sql-server-data-load.png?resize=720%2C546\" sizes=\"(max-width: 720px) 100vw, 720px\" srcset=\"https:\/\/i0.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-xero-to-sql-server-data-load.png?w=782 782w, https:\/\/i0.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-xero-to-sql-server-data-load.png?resize=300%2C227 300w, https:\/\/i0.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-xero-to-sql-server-data-load.png?resize=768%2C582 768w\" alt=\"Configure SSIS OLEDB Destination - Loading Xero Data into SQL Server Table\" width=\"702\" height=\"532\" data-attachment-id=\"1617\" data-permalink=\"\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/ssis-xero-to-sql-server-data-load\/#main\" data-orig-file=\"https:\/\/i0.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-xero-to-sql-server-data-load.png?fit=782%2C593\" data-orig-size=\"782,593\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"ssis-xero-to-sql-server-data-load\" data-image-description=\"&lt;p&gt;Configure SSIS OLEDB Destination \u2013 Loading Xero Data into SQL Server Table&lt;\/p&gt; \" data-medium-file=\"https:\/\/i0.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-xero-to-sql-server-data-load.png?fit=300%2C227\" data-large-file=\"https:\/\/i0.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-xero-to-sql-server-data-load.png?fit=720%2C546\" \/><\/a><\/p>\n<p class=\"wp-caption-text\">Configure SSIS OLEDB Destination \u2013 Loading ServiceNow Data into SQL Server Table<\/p>\n<\/div>\n<div id=\"attachment_1618\" class=\"wp-caption alignnone\">\n<p><a href=\"https:\/\/i1.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-xero-sql-server-load-mappings.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1618\" style=\"border: 0px; max-width: 100%; height: auto; box-shadow: rgba(0, 0, 0, 0.176) 0px 1px 2px;\" src=\"https:\/\/i1.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-xero-sql-server-load-mappings.png?resize=720%2C533\" sizes=\"(max-width: 720px) 100vw, 720px\" srcset=\"https:\/\/i1.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-xero-sql-server-load-mappings.png?w=734 734w, https:\/\/i1.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-xero-sql-server-load-mappings.png?resize=300%2C222 300w\" alt=\"Xero to SQL Server Column Mappings for OLEDB Destination\" width=\"702\" height=\"519\" data-attachment-id=\"1618\" data-permalink=\"\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/ssis-xero-sql-server-load-mappings\/#main\" data-orig-file=\"https:\/\/i1.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-xero-sql-server-load-mappings.png?fit=734%2C543\" data-orig-size=\"734,543\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"ssis-xero-sql-server-load-mappings\" data-image-description=\"&lt;p&gt;Xero to SQL Server Column Mappings for OLEDB Destination&lt;\/p&gt; \" data-medium-file=\"https:\/\/i1.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-xero-sql-server-load-mappings.png?fit=300%2C222\" data-large-file=\"https:\/\/i1.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-xero-sql-server-load-mappings.png?fit=720%2C533\" \/><\/a><\/p>\n<p class=\"wp-caption-text\">ServiceNow API to SQL Server Column Mappings for OLEDB Destination<\/p>\n<\/div>\n<div id=\"attachment_1619\" class=\"wp-caption alignnone\">\n<p><a href=\"https:\/\/i2.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-loading-data-from-xero-to-sql-server.png\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1619\" style=\"border: 0px; max-width: 100%; height: auto; box-shadow: rgba(0, 0, 0, 0.176) 0px 1px 2px;\" src=\"https:\/\/i2.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-loading-data-from-xero-to-sql-server.png?resize=283%2C391\" sizes=\"(max-width: 283px) 100vw, 283px\" srcset=\"https:\/\/i2.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-loading-data-from-xero-to-sql-server.png?w=283 283w, https:\/\/i2.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-loading-data-from-xero-to-sql-server.png?resize=217%2C300 217w\" alt=\"Loading Xero data to SQL Server in SSIS\" width=\"275\" height=\"380\" data-attachment-id=\"1619\" data-permalink=\"\/\/zappysys.com\/blog\/reading-loading-data-in-xero-sql-server-ssis\/ssis-loading-data-from-xero-to-sql-server\/#main\" data-orig-file=\"https:\/\/i2.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-loading-data-from-xero-to-sql-server.png?fit=283%2C391\" data-orig-size=\"283,391\" data-comments-opened=\"0\" data-image-meta=\"{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}\" data-image-title=\"ssis-loading-data-from-xero-to-sql-server\" data-image-description=\"&lt;p&gt;Loading Xero data to SQL Server in SSIS&lt;\/p&gt; \" data-medium-file=\"https:\/\/i2.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-loading-data-from-xero-to-sql-server.png?fit=217%2C300\" data-large-file=\"https:\/\/i2.wp.com\/zappysys.com\/blog\/wp-content\/uploads\/2017\/07\/ssis-loading-data-from-xero-to-sql-server.png?fit=283%2C391\" \/><\/a><\/p>\n<p class=\"wp-caption-text\">Loading ServiceNow REST API to SQL Server in SSIS<\/p>\n<\/div>\n<h2>POST Data to ServiceNow API (Insert, Update or Delete)<\/h2>\n<p>There will be a time you like to automate POST actions (e.g. create new incident via API call). <a href=\"\/\/zappysys.com\/blog\/http-post-in-ssis-send-data-to-web-api-url-json-xml\/\" target=\"_blank\" rel=\"noopener\">Check this article<\/a> to learn more on how to POST , DELETE, PUT data using API calls<\/p>\n<h2>Common Errors<\/h2>\n<div class=\"content_block\" id=\"custom_post_widget-1887\"><h3>Truncation related error<\/h3>\r\n<p style=\"text-align: justify;\">The most common error you may face when you run an SSIS package is truncation error. During the design time only 300 rows are scanned from a source (a file or a REST API call response) to detect datatypes but at runtime, it is likely you will retrieve far more records. So it is possible that you will get longer strings than initially expected.\u00a0For detailed instructions on how to fix common metadata related errors read an article \"<a href=\"\/\/zappysys.com\/blog\/handling-ssis-component-metadata-issues\/\" target=\"_blank\" rel=\"noopener\">How to handle SSIS errors (truncation, metadata issues)<\/a>\".<\/p>\r\n\r\n<h3>Authentication related error<\/h3>\r\nAnother frequent error you may get is an authentication error, which happens when you deploy\/copy a package to another machine and run it there. Check <a href=\"#Deployment_to_Production\">the paragraph below<\/a>\u00a0to see why it happens and how to solve this problem.<\/div>\n<h2>Deployment to Production<\/h2>\n<div class=\"content_block\" id=\"custom_post_widget-1932\"><p style=\"text-align: justify;\">In SSIS package <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/integration-services\/security\/access-control-for-sensitive-data-in-packages\" target=\"_blank\" rel=\"noopener\">sensitive data such as tokens and passwords are by default encrypted by SSIS<\/a> with your Windows account which you use to create a package.\u00a0So SSIS will fail to decrypt tokens\/passwords when you run it from another machine using another Windows account. To circumvent this when you are creating an SSIS package which uses authentication components (e.g. an <a href=\"https:\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/ssis-oauth-connection-manager.htm\" target=\"_blank\" rel=\"noopener\">OAuth Connection Manager<\/a>\u00a0or an <a href=\"https:\/\/zappysys.com\/onlinehelp\/ssis-powerpack\/scr\/ssis-http-connection-manager.htm\" target=\"_blank\" rel=\"noopener\">HTTP Connection Manager<\/a> with credentials, etc.), consider using parameters\/variables to pass tokens\/passwords. In this way, you won\u2019t face authentication related errors when a package is deployed to a production server.<\/p>\r\n<p style=\"text-align: justify;\">Check our article on <a href=\"https:\/\/zappysys.com\/blog\/how-to-run-an-ssis-package-with-sensitive-data-on-sql-server\/\" target=\"_blank\" rel=\"noopener\">how to configure packages with sensitive data on your production or development server<\/a>.<\/p><\/div>\n<h2><span id=\"ConclusionWhats_next\"><span id=\"ConclusionWhat8217s_next\">Conclusion.\u00a0What\u2019s next?<\/span><\/span><\/h2>\n<p>In this article we have learned how to load data from ServiceNow API to SQL Server using SSIS ( drag and drop approach without coding). We used\u00a0<a href=\"https:\/\/zappysys.com\/products\/ssis-powerpack\/\" target=\"_blank\" rel=\"noopener\">SSIS JSON \/ REST API Connector<\/a>\u00a0to extract data from ServiceNow table. JSON Source Connector makes it super simple to parsing complex \/ large JSON Files or any Web API Response into rows and column so you can load into database like SQL Server.\u00a0<a href=\"\/\/zappysys.com\/products\/ssis-powerpack\/\">Download SSIS PowerPack<\/a>\u00a0to try many other automation scenarios not discussed in this article.<\/p>\n<p><strong>Keywords:<\/strong><\/p>\n<p>ServiceNow Integration with SQL Server | How to extract ServiceNow data in SSIS? | How to read ServiceNow table? | Calling ServiceNow REST API using SSIS. | ServiceNow to SQL Server | SQL Server to ServiceNow | SSIS ServiceNow API Integration |\u00a0ServiceNow SOAP API Integration<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction ServiceNow one of the most popular Incident management platform used by many. In this blog post you will learn how to\u00a0get data from ServiceNow REST API\u00a0using SSIS JSON Source (No coding required). You will also learn other API integration scenarios such as POST, DELETE, PUT API calls. You will also learn how to paginate [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":1744,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[225,17],"tags":[232,231,12,148],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\r\n<title>How to read data from ServiceNow REST API using SSIS | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Learn how to read, write data using ServiceNow API in SSIS. In a few clicks without coding you can configure SSIS JSON Source to read large amount of data.\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"How to read data from ServiceNow REST API using SSIS | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Learn how to read, write data using ServiceNow API in SSIS. In a few clicks without coding you can configure SSIS JSON Source to read large amount of data.\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2017-03-13T19:40:38+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2025-03-19T07:04:16+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/servicenow-api-integration.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"301\" \/>\r\n\t<meta property=\"og:image:height\" content=\"301\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/\",\"url\":\"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/\",\"name\":\"How to read data from ServiceNow REST API using SSIS | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/servicenow-api-integration.png\",\"datePublished\":\"2017-03-13T19:40:38+00:00\",\"dateModified\":\"2025-03-19T07:04:16+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Learn how to read, write data using ServiceNow API in SSIS. In a few clicks without coding you can configure SSIS JSON Source to read large amount of data.\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/servicenow-api-integration.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/servicenow-api-integration.png\",\"width\":301,\"height\":301},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to read data from ServiceNow REST API using SSIS\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How to read data from ServiceNow REST API using SSIS | ZappySys Blog","description":"Learn how to read, write data using ServiceNow API in SSIS. In a few clicks without coding you can configure SSIS JSON Source to read large amount of data.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/","og_locale":"en_US","og_type":"article","og_title":"How to read data from ServiceNow REST API using SSIS | ZappySys Blog","og_description":"Learn how to read, write data using ServiceNow API in SSIS. In a few clicks without coding you can configure SSIS JSON Source to read large amount of data.","og_url":"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2017-03-13T19:40:38+00:00","article_modified_time":"2025-03-19T07:04:16+00:00","og_image":[{"width":301,"height":301,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/servicenow-api-integration.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/","url":"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/","name":"How to read data from ServiceNow REST API using SSIS | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/servicenow-api-integration.png","datePublished":"2017-03-13T19:40:38+00:00","dateModified":"2025-03-19T07:04:16+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Learn how to read, write data using ServiceNow API in SSIS. In a few clicks without coding you can configure SSIS JSON Source to read large amount of data.","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/servicenow-api-integration.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2017\/03\/servicenow-api-integration.png","width":301,"height":301},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/how-to-read-data-from-servicenow-rest-api-ssis\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"How to read data from ServiceNow REST API using SSIS"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/1081"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=1081"}],"version-history":[{"count":20,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/1081\/revisions"}],"predecessor-version":[{"id":11248,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/1081\/revisions\/11248"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/1744"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=1081"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=1081"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=1081"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}