Accumulating Data from Third-Party Sources via a Linked SQL Server

[intapiuser]

This document describes how to use a linked SQL Server to execute commands to external data sources (in this case - Netsuite), and create accumulated Transactions Tables with SQL JOBS.

Before you Begin

1. Make sure to install the ODBC for your database on the server.
2. SQL Server Express supports up to 10GB for the DB size, 1GB of RAM usage, and does not allow for agents (job scheduler)​. ​Using this solutionrequires a standard SQL Server license​. This documentation from Microsoft outlines differences between SQL server versions.
3. To create a job, a user must be a member of one of the SQL Server Agent fixed database roles or the sysadmin fixed server role. A job can be edited only by its owner or members of the sysadmin role. For more information about the SQL Server Agent fixed database roles, see SQL Server Agent Fixed Database Roles. Assigning a job to another user does not guarantee that the new owner will have sufficient permission to run the job successfully.
4. Local jobs are cached by the local SQL Server Agent. Therefore, any modifications force the SQL Server Agent to re-cache the job. Because SQL Server Agent does not cache the job until sp_add_jobserver is called, it is recommended to call sp_add_jobserverlast.

Implementation Steps

1 - Create a History table with all the transactions with the following syntax:

Select CAST(TRANSACTION_ID AS bigint)*100000000+year(MODIFIED)*10000+month(MODIFIED)*100+day(MODIFI ED) as TransKey, ACCOUNTING_PERIOD_ID ,

BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID ,RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED

into TransHistory from openquery([NETSUITE], 'select

ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER ,LAST_MODIFIED_DATE as MODIFIED from [ | Reporting View Only (ODBC)].[TRANSACTIONS]')

2 - Create a “New Transactions” table with the following syntax (should be empty in the beginning):

select CAST(TRANSACTION_ID AS bigint)*100000000+year(MODIFIED)*10000+month(MODIFIED)*100+day(MODIFI ED) as TransKey,

ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE ,TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED

into NewTrans

from

( select * from ( Select CAST(TRANSACTION_ID AS bigint)*100000000+year(MODIFIED)*10000+month(MODIFIED)*100+day(MODIFI ED) as TransKey,

ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED

from openquery([NETSUITE], 'select

ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , LAST_MODIFIED_DATE as MODIFIED

from [ | Reporting View Only (ODBC)].[TRANSACTIONS]')) t except Select

TransKey, ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED

from [dbo].[TransHistory] )t

3 - Create a new job with the following configurations:

4 - Set the desired schedule:

5 - Define the Job steps:

Step 1 - TRUNCATE [NewTrans] TRUNCATE TABLE [dbo].[NewTrans]

Step 2- Create a New Transactions Table

TRUNCATE TABLE [dbo].[NewTrans]

insert into NewTrans

(

TransKey,

ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED )

select * from (Select

CAST(TRANSACTION_ID AS bigint)*100000000+year(MODIFIED)*10000+month(MODIFIED)*100+day(MODIFI ED) as TransKey,

ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED

from openquery([NETSUITE], 'select

ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , LAST_MODIFIED_DATE as MODIFIED

from [ | Reporting View Only (ODBC)].[TRANSACTIONS]')) t except Select

TransKey, ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED

from [dbo].[TransHistory]

Step 3 - Append new table to History table

insert into [dbo].[TransHistory] (TransKey, ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED)

select TransKey,

ACCOUNTING_PERIOD_ID , BILLING_DATE , COGS_RECLASS , CONDITIONAL_ACCEPTANCE , CREATED_FROM_ID , END_CUSTOMER_ID , ENTITY_ID , EXPEDITE_COMMIT_DATE , IS_NON_POSTING , ITEM_FULFILLMENT_ID , MEMO , ORDER_TYPE_ID , RECLASS_COGS_TO_ID , SALES_REP_ID , STATUS , TOTAL_AMOUNT , TRANDATE , TRANID , TRANSACTION_ID , TRANSACTION_NUMBER , MODIFIED

from [dbo].[NewTrans]

6 - That’s it, you’re done!

The attached document describes how to use a linked SQL Server to execute commands to external data sources (in this case - Netsuite) and create accumulated Transactions Tables with SQL JOBS.