Often times when building an integration with Scribe Insight, we insert data temporarily into a SQL table in order to manipulate it quickly. We then run a sequential job to insert/update this data into our target. An example of this is when we concatenate order comments from an A + data source.
In A +, Order and Order Detail comments are located in the Order Detail records. There can be numerous records for each comment. In order to concatenate them quickly, exporting them to a SQL table is the easiest way.
With an integration, we'll be running this process numerous times a day. So how do we remove the SQL table's data before the next run?
When you open a Scribe Insight DTS file in the Scribe Workbench, you have the ability to run a SQL script prior to running a DTS job. This can be found within your DTS Connection. Open your DTS and then open your Connection Manager. Choose your SQL Server connection, then select Edit. In the Connection Settings, click on the DTS Connection Settings tab.
Towards the bottom of the dialog box (Scribe version 7.9), you'll see a place to add your script before running the DTS (you can also choose to run it after your DTS job). Use the Browse button to select your saved script and click OK. Now each time your DTS runs, either manually or as a Scribe Console Integration Process, the SQL script will run prior to the job's start.
As mentioned above, I need to clear the table's data prior to each run. My SQL script is a simple Truncate statement on a specified table: "TRUNCATE TABLE CRMIntegration_Orders;"
Keep in mind that the SQL Script will run against the SQL Database associated to your Scribe Connection.
Beringer Associates, a Microsoft Gold Certified Partner, is always here to provide expert knowledge in topics like these. Please contact us with any questions you may have.