Skip to main content

Extract Data from Sage Sotamas 90 into SQL Server with Biml

Why This Pattern Matters​

Sage's ProvideX data store is awkward to query directly. Moving the data into SQL Server makes downstream analysis far easier, but the volume of tables involved makes hand-built SSIS packages impractical. The end-to-end process here uses the vendor XSD to create matching destination tables, then drives one SSIS package per table from Biml, then loops over the resulting packages to load all the data.

The high-level steps are:

  1. Create matching tables in the destination SQL Server database.
  2. Use Biml to create one SSIS package per table.
  3. Build a loop package that runs every generated package.
  4. Run the loop package to load the data.

Create Matching Destination Tables​

Sage ships an XSD describing its schema, found under the install directory at a path like 'C:\Sage\Sage 100 Standard ERP\MAS90\SData\MasContract.xsd'. A small console application can read the XSD and create the corresponding tables in SQL Server through SQLXML Bulk Load. The application needs a COM reference to Microsoft XML Bulkload for SQL Server 4.0 Type Library, which arrives with the SqlXml 4.0 redistributable.

using SQLXMLBULKLOADLib;
using System;

namespace XsdToTable
{
  class Program
  {
    [STAThread]
    static void Main(string[] args)
    {
      try
      {
        SQLXMLBulkLoad4 loader = new SQLXMLBulkLoad4();
        loader.ConnectionString = "<destination connection string>";
        loader.ErrorLogFile = "error.xml";
        loader.BulkLoad = false;
        loader.SchemaGen = true;
        loader.KeepIdentity = false;
        loader.Execute("path\\schema.xsd");
      }
      catch (Exception e)
      {
        Console.WriteLine(e.ToString());
        Console.WriteLine(e.Message);
        Console.ReadLine();
      }
    }
  }
}

The first run typically fails because the vendor XSD contains identifiers that are reserved words in SQL Server: 'Key', 'Table', 'View', 'Description', 'Column', 'External', 'Lookup', 'No', 'Order', 'Group', 'Print', 'Row', 'Collate', 'Error', 'Identity', 'Length', 'DateFormat'. Editing each occurrence in the XSD to a non-reserved spelling (for example, 'Tble' for 'Table') lets the table generation succeed. Keep a list of every renamed identifier; the Biml step needs to map them back.

Capture Working Connection Strings​

Build a single SSIS package by hand that moves data from one Sage table to its SQL Server destination. Use an ODBC connection to Sage and an OLE DB connection to SQL Server. Once the package runs, open the dtsx file in a text editor and copy the connection element XML. Those exact strings become the templates for the connections inside the Biml script.

Generate One Package per Table​

The Biml script below loops over a stored procedure that returns the destination table list and emits one SSIS package per table. Each package contains a dataflow with an ADO.NET source against Sage, a Data Conversion transformation, and an OLE DB destination against SQL Server.

<#@ template language="C#" hostspecific="true" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.IO" #>
<#@ import namespace="System.Data.Odbc" #>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <AdoNetConnection Name="SAGE_ODBC"
      ConnectionString="uid=<sage user id>;Dsn=SAGE_ODBC"
      Provider="System.Data.Odbc.OdbcConnection, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=<copied from dtsx>" />
    <OleDbConnection Name="SQL_TARGET" ConnectionString="<connection string from dtsx>" />
  </Connections>
  <Packages>
    <#
      string conn = "<oledb connection string for c#>";
      DataTable tbllist = ExternalDataAccess.GetDataTable(conn, "Exec dbo.BuildPackageMetadata");
      foreach (DataRow table in tbllist.Rows)
      {
    #>
    <Package Name="<#=table[0]#>">
      <Tasks>
        <Dataflow Name="DataFlowTransfer">
          <Transformations>
            <AdoNetSource Name="SageSource" ConnectionName="SAGE_ODBC">
              <DirectInput>
                SELECT * FROM <#=table[1]#>
              </DirectInput>
            </AdoNetSource>
            <DataConversion Name="Converter">
              <Columns>
                <#=table[2]#>
              </Columns>
            </DataConversion>
            <OleDbDestination Name="DestTable" ConnectionName="SQL_TARGET">
              <ExternalTableOutput Table="<#=table[0]#>" />
            </OleDbDestination>
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
    <# } #>
  </Packages>
</Biml>

The stored procedure returns three values per row: the SQL Server table name, the original Sage source table name, and a chunk of pre-built Biml describing the column mappings for the Data Conversion. The mapping chunk is built in T-SQL and reverses the renames applied to the XSD so SSIS sees the original Sage column and table names on the source side.

A simplified version of the helper stored procedure looks like this:

SELECT
  name,
  REPLACE(name, 'Tble', 'Table') AS SourceTableName,
  MapColumns = REPLACE(REPLACE(STUFF(
    (SELECT DISTINCT CHAR(10) +
       '<Column SourceColumn="' +
       REPLACE(c.name, 'Colmn', 'Column') +
       CASE
         WHEN RIGHT(t.name, 3) = 'int'      THEN ' DataType="Decimal" Precision="25"'
         WHEN t.name = 'nvarchar'           THEN ' DataType="String" Length="1000"'
         WHEN t.name = 'datetime'           THEN ' DataType="DateTime"'
         WHEN t.name = 'decimal'            THEN ' DataType="Double" Precision="10"'
         ELSE t.name
       END + ' />'
     FROM sys.columns c
     INNER JOIN sys.types t ON c.user_type_id = t.user_type_id
     WHERE object_id = a.object_id
     FOR XML PATH('')), 1, 1, ''), '&lt;', '<'), '&gt;', '>')
FROM sys.tables a;

Add one extra REPLACE per renamed identifier so every reserved word gets restored to its Sage spelling. Map SQL types to Biml types in the CASE expression; extend the mapping for any data types the schema contains.

Loop Package to Run Every Generated Package​

After expanding the Biml, the project contains one dtsx per Sage table. A second package wraps the run with a Foreach Loop Container configured as a file enumerator pointing at the folder of generated packages.

The container drives a single Execute Package Task. A package variable named 'FileName' holds the fully qualified file name of the package being executed; map it to index 0 of the Foreach Loop's variable mappings. Then create a File Connection Manager whose connection string is bound to 'FileName' through an expression. The Execute Package Task uses that File Connection Manager as its package source.

Running the loop package executes every generated package in turn and lands all the Sage data in the SQL Server destination tables. From there, querying becomes a normal SQL exercise.