Use Biml to Build SSIS Tasks to Replicate HubDB Data to SQL Server

Using SQL Server as a backup for critical business data provides an essential safety net against loss and enables business users to more easily connect that data with features like reporting, analytics, and more. Biml is an XML dialect that can be used to create Microsoft SQL Server BI objects, like SSIS packages. Pairing CData SSIS Components with Biml empowers you to easily build SSIS packages with access to HubDB data. Key benefits include:

• Built-in metadata discovery — CData SSIS components expose metadata just like working with SQL Server, even dynamically generating schema for schema-less data sources
• Dynamic SSIS task generation — Use code nuggets in Biml to build SSIS tasks by iterating over discovered metadata
• Read from and write to HubDB — Native source and destination components make HubDB look just like a database

This article demonstrates how to use Biml with the CData SSIS Components for HubDB to dynamically build SSIS tasks (one for each HubDB entity) to replicate HubDB data to a Microsoft SQL Server database. We step through the Biml file one section at a time but have included the complete Biml file at the end of the article.

In order to use Biml in an SSIS Project in Visual Studio, install BimlExpress. Once you install BimlExpress, open Visual Studio, create a new Integration Services project, and add a new Biml file.

Building the Biml File

With Biml, you can write scripting to dynamically generate SSIS projects, packages, and tasks. To see the Biml file for an existing project (and gain insights on using Biml with CData SSIS Tasks), simply create your tasks and then right-click the project and select Convert SSIS Packages to Biml.

C# Code

1. Use directives <#@ .. #> to import necessary namespaces and the assembly for the CData SSIS Components for HubDB.

   <#@ template language="C#" hostspecific="true"#>
   <#@ import namespace="System.Data"#>
   <#@ import namespace="System.IO"#>
   <#@ import namespace="System.Collections"#>
   <#@ import namespace="System.Data.CData.HubDB"#>
   <#@ assembly name="C:\Program Files\CData[product_name] 2018\lib\CData.SSIS2017.HubDB.dll"#>
   

2. In a new control nugget <# ... #>, create variables for values that will be used throughout the Biml script, including a connection string for HubDB and structures to store the HubDB metadata.

   There are two authentication methods available for connecting to HubDB data source: OAuth Authentication with a public HubSpot application and authentication with a Private application token.

   Using a Custom OAuth App

   AuthScheme must be set to "OAuth" in all OAuth flows. Be sure to review the Help documentation for the required connection properties for you specific authentication needs (desktop applications, web applications, and headless machines).

   Follow the steps below to register an application and obtain the OAuth client credentials:

   1. Log into your HubSpot app developer account.
      • Note that it must be an app developer account. Standard HubSpot accounts cannot create public apps.
   2. On the developer account home page, click the Apps tab.
   3. Click Create app.
   4. On the App info tab, enter and optionally modify values that are displayed to users when they connect. These values include the public application name, application logo, and a description of the application.
   5. On the Auth tab, supply a callback URL in the "Redirect URLs" box.
      • If you're creating a desktop application, set this to a locally accessible URL like http://localhost:33333.
      • If you are creating a Web application, set this to a trusted URL where you want users to be redirected to when they authorize your application.
   6. Click Create App. HubSpot then generates the application, along with its associated credentials.
   7. On the Auth tab, note the Client ID and Client secret. You will use these later to configure the driver.

   8. Under Scopes, select any scopes you need for your application's intended functionality.

      A minimum of the following scopes is required to access tables:

      • hubdb
      • oauth
      • crm.objects.owners.read
   9. Click Save changes.
   10. Install the application into a production portal with access to the features that are required by the integration.
       • Under "Install URL (OAuth)", click Copy full URL to copy the installation URL for your application.
       • Navigate to the copied link in your browser. Select a standard account in which to install the application.
       • Click Connect app. You can close the resulting tab.

   Using a Private App

   To connect using a HubSpot private application token, set the AuthScheme property to "PrivateApp."

   You can generate a private application token by following the steps below:

   1. In your HubDB account, click the settings icon (the gear) in the main navigation bar.
   2. In the left sidebar menu, navigate to Integrations > Private Apps.
   3. Click Create private app.
   4. On the Basic Info tab, configure the details of your application (name, logo, and description).
   5. On the Scopes tab, select Read or Write for each scope you want your private application to be able to access.
   6. A minimum of hubdb and crm.objects.owners.read is required to access tables.
   7. After you are done configuring your application, click Create app in the top right.
   8. Review the info about your application's access token, click Continue creating, and then Show token.
   9. Click Copy to copy the private application token.

   To connect, set PrivateAppToken to the private application token you retrieved.

   var hubdbConnectionString = "AuthScheme=OAuth;OAuthClientID=MyOAuthClientID;OAuthClientSecret=MyOAuthClientSecret;CallbackURL=http://localhost:33333;InitiateOAuth=GETANDREFRESH;";
   var replicationServer = "SERVER";
   var replicationCatalog = "CATALOG";
   var replicationUserID = "sqluser";
   var replicationPassword = "sqlpassword";
   
   List<string> allEntityNames = new List<string>();
   Hashtable entitySchema = new Hashtable();
   

3. In the same control nugget used to defined variables, use ADO.NET code to programmatically query the HubDB entities (tables) and fields (columns).

   using (HubDBConnection connection = new HubDBConnection(hubdbConnectionString)) {
    connection.Open();
    var entities = connection.GetSchema("Tables").Rows;
    foreach (DataRow entity in entities)
    {
    allEntityNames.Add(entity["TABLE_NAME"].ToString());
    }
    foreach (string entity in allEntityNames){
    var columns = connection.GetSchema("Columns", new string [] {entity}).Rows;
    entitySchema.Add(entity,columns);
    }
   }
   

Class Nugget

In our Biml script to create the replication tasks, there are several places where repeated XML elements are created dynamically (mostly for columns in SSIS tasks). Instead of repeating the code, add a class nugget <#+ ... #> and create a helper class with methods to consolidate repeated code (full code at the end of the article).

1. Add public static variables to determine which type of XML element to create.

   public static int OUTPUT_WITH_ERROR = 0;
   public static int EXTERNAL = 1;
   public static int OUTPUT = 2;
   public static int DATAOVERRIDE_COLUMN = 4;
   

2. Add a public method to build a SQL statement for use in the ExecuteSQL task used to drop existing tables and create a new table for the replicated data.

   // Dynamically builds a DROP TABLE and CREATE statement
   // for each entity (table) in HubDB using the table name and metadata.
   public static string GetDeleteAndCreateStatement(string tableName, DataRowCollection columns) {
    ...
   }
   

3. Add a public method to build the collection of column-based XML elements.

    
   // Dynamically build various column-based XML elements
   // for each entity (table) in HubDB based on the column 
   // metadata and the parent element
   public static string GetColumnDefs(DataRowCollection columns, int columnType){
    ...
   }
   

Biml Script

Now that you have the table metadata and a Helper class to reduce repeated code, write the Biml script to dynamically create your replication packages.

1. Start by adding a CustomSsisConnection element for the CData SSIS Tasks. Note that the ObjectData attribute must be XML encoded. A typical connecting string looks similar to the following (note the use of the hubdbConnectionString variable for the ConnectionString property:

   <HubDBConnectionManager>
    <Property Name="ConnectionString"><#=hubdbConnectionString#></Property>
   </HubDBConnectionManager>
   

   After configuring the connection to the CData SSIS Task, configure a connection to the replication database. The completed Connections element looks like the following (note the use of text nuggets <#= ... #> to add variables for connection string values):

   <Connections>
    <CustomSsisConnection Name="CData HubDB Connection Manager" CreationName = "CDATA_HUBDB" ObjectData = "&lt;HubDBConnectionManager&gt; &lt;Property Name=&quot;ConnectionString&quot;&gt; <#=hubdbConnectionString#>&lt;/Property&gt; &lt;/HubDBConnectionManager&gt;" />
    <Connection Name="Destination" ConnectionString="Data Source=<#=replicationServer#>;User ID=<#=replicationUserID#>;Password=<#=replicationPassword#>;Initial Catalog=<#=replicationCatalog#>;Provider=SQLNCLI11.1;"/>
   </Connections>
   

2. With the Connections element configured, you are ready to build our replication package. In the package, the Biml script create an ExecuteSQL task and a Dataflow task for each table to be replicated.

   To build each set of tasks, use a while loop in a control nugget to iterate through the entity (table) names:

   int entityCounter = 0; while(entityCounter < allEntityNames.Count){
   var tableName = allEntityNames[entityCounter].ToString();
   DataRowCollection columns = ((DataRowCollection)entitySchema[tableName]);
   

   • ExecuteSQL Task

     In the ExecuteSQL task, execute a SQL query to drop any existing tables that have the same name as our HubDB entity (table) and create a new table based on the metadata discovered using the CData SSIS Component.

     To create the query dynamically, use the Helper.GetDeleteAndCreateStatement() helper function.

   • Dataflow Task

     Within the Dataflow use a CustomComponent as the source component and an OleDbDestination as the destination.

     • CustomComponent Element

       The CustomComponent element uses the CData SSIS Source component to retrieve HubDB data. Start by configuring the component to use with the CData component.

       <CustomComponent Name="CData HubDB Source" ComponentTypeName="CData.SSIS.HubDB.HubDBSource" Version="18" ContactInfo="[email protected]" UsesDispositions="true">
       ...
       </CustomComponent>
       

       DataflowOverrides and OutputPaths Elements

       The next step after configuring the connection is to add Columns elements to the OutputPath child element of the DataflowOverrides element. To do so, call the Helper.GetColumnDefs() helper function.

       Use the same Helper class to add columns to the OutputColumns and ExternalColumns child elements of the various OutputPaths elements.

       The definitions created provide information about the input, output, and error information for the SSIS component.

       <DataflowOverrides>
        <OutputPath OutputPathName="CData HubDB Source Output">
        <Columns>
       <#=HelperClass.GetColumnDefs(columns,HelperClass.DATAOVERRIDE_COLUMN) #>
        </Columns>
        </OutputPath>
       </DataflowOverrides>
       ...
       <OutputPaths>
        <OutputPath Name="CData HubDB Source Output">
        <OutputColumns>
       <#=HelperClass.GetColumnDefs(columns,HelperClass.OUTPUT_WITH_ERROR) #>
        </OutputColumns>
        <ExternalColumns>
       <#=HelperClass.GetColumnDefs(columns,HelperClass.EXTERNAL) #>
        </ExternalColumns>
        </OutputPath>
        <OutputPath Name="CData HubDB Source Error Output" IsErrorOutput="true">
        <OutputColumns>
       <#=HelperClass.GetColumnDefs(columns,HelperClass.OUTPUT) # 
        </OutputColumns>
        </OutputPath>
       </OutputPaths>
       

       CustomProperties Element

       The CData SSIS tasks are surfaced in SSIS as custom components with a series of required CustomProperties:

       <CustomProperties>
        <CustomProperty Name="SQLStatement" DataType="Null" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version= 10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" SupportsExpression="true"></CustomProperty>
        <CustomProperty Name="AccessMode" DataType="Int32" TypeConverter="CData.SSIS.HubDB.AccessModeToStringConverter">0</CustomProperty>
        <CustomProperty Name="TableOrView" DataType="String" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version= 10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" SupportsExpression="true">[<#=tableName#>]</CustomProperty>
        <CustomProperty Name="ExecStoredProcedure" DataType="Boolean">false</CustomProperty>
       </CustomProperties>
       

       Connections Element

       The last element to add to the CustomComponent element is a Connections element, attaching the previously defined connection to the task:

       <Connections>
        <Connection Name="HubDB 2018 Connection" ConnectionName="CData HubDB Connection Manager" />
       </Connections>
       

     • OleDbDestination Element

       The final piece of the Dataflow task is the OleDbDestination element. Attach the previously defined OleDbConnection to the element, set the InputPath and ExternalTableOutput:

       <OleDbDestination Name="OLE DB Destination" ConnectionName="Destination" CheckConstraints="false">
        <InputPath OutputPathName="CData HubDB Source.CData HubDB Source Output" />
        <ExternalTableOutput Table="[<#=tableName#>]" />
       </OleDbDestination>
       

3. Use a control nugget to increment the counter used to iterate over the collection of entity (table) names. Do this within the Tasks element, after the end of the Dataflow element:

   ...
    </Dataflow> 
   <# entityCounter++;}#>
    </Tasks>
    </Package>
    </Packages>
   </Biml>
   

Build the SSIS Project

Once the Biml file is written, right-click on the Biml file in Server Explorer and select Generate SSIS Packages. At this point, Visual Studio and BimlExpress will translate the Biml file into SSIS package(s), ready to be run.

Run the package to begin replicating your HubDB data to a SQL Server database (or any other destination you choose).

Free Trial & More Information

With the CData SSIS Components for HubDB, you get SQL access to your HubDB data directly from SSIS packages. And with Biml, you can automatically generate those packages. For more information about the CData SSIS Components for HubDB, refer to the product page. You can always get started with a free, 30-day trial. As always, our world-class CData Support Team is available if you have any questions.

Complete Biml File

<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data"#>
<#@ import namespace="System.IO"#>
<#@ import namespace="System.Collections"#>
<#@ import namespace="System.Data.CData.HubDB"#>
<#@ assembly name="C:\Program Files\CData\CData SSIS Components for HubDB 2018\lib\CData.SSIS2017.HubDB.dll"#>
<#
var hubdbConnectionString = "AuthScheme=OAuth;OAuthClientID=MyOAuthClientID;OAuthClientSecret=MyOAuthClientSecret;CallbackURL=http://localhost:33333;InitiateOAuth=GETANDREFRESH;";
var replicationServer = "JDG";
var replicationCatalog = "BIML";
var replicationUserID = "sqltest";
var replicationPassword = "sqltest";

List<string> allEntityNames = new List<string>();
Hashtable entitySchema = new Hashtable();
using (HubDBConnection connection = new HubDBConnection(hubdbConnectionString)) {
 connection.Open();
 var entities = connection.GetSchema("Tables").Rows;
 foreach (DataRow entity in entities)
 {
 allEntityNames.Add(entity["TABLE_NAME"].ToString());
 }
 foreach (string entity in allEntityNames){
 var columns = connection.GetSchema("Columns", new string [] {entity}).Rows;
 entitySchema.Add(entity,columns);
 }
}#>
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
 <Connections>
 <CustomSsisConnection Name="CData HubDB Connection Manager" CreationName="CDATA_HUBDB" ObjectData="&lt;HubDBConnectionManager&gt;&lt;Property Name=&quot;ConnectionString"&gt;<#=hubdbConnectionString#>&lt;/Property&gt;&lt;/HubDBConnectionManager&gt;"/>
 <Connection Name="Destination" ConnectionString="Data Source=<#=replicationServer#>;User ID=<#=replicationUserID#>;Password=<#=replicationPassword#>;Initial Catalog=<#=replicationCatalog#>;Provider=SQLNCLI11.1;"/>
 </Connections>
 <Packages>
 <Package Name="Replicate HubDB Package" Language="None" ConstraintMode="LinearOnCompletion" ProtectionLevel="EncryptSensitiveWithUserKey">
 <Tasks>
<# int entityCounter = 0; while(entityCounter < allEntityNames.Count){
 var tableName = allEntityNames[entityCounter].ToString();
 if (tableName.Equals("IdpEventLog")) break;
 DataRowCollection columns = ((DataRowCollection)entitySchema[tableName]);#>
 <ExecuteSQL Name="Create <#=tableName#> Replication Table" ConnectionName="Destination">
 <DirectInput>
<#=HelperClass.GetDeleteAndCreateStatement(tableName,columns)#>
 </DirectInput>
 </ExecuteSQL>
 <Dataflow Name="Replicate <#=tableName#>">
 <Transformations>
 <CustomComponent Name="CData HubDB Source" ComponentTypeName="CData.SSIS.HubDB.HubDBSource" Version="18" ContactInfo="[email protected]" UsesDispositions="true">
 <DataflowOverrides>
 <OutputPath OutputPathName="CData HubDB Source Output">
 <Columns>
<#=HelperClass.GetColumnDefs(columns,HelperClass.DATAOVERRIDE_COLUMN) #>
 </Columns>
 </OutputPath>
 </DataflowOverrides>
 <CustomProperties>
 <CustomProperty Name="SQLStatement" DataType="Null" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version= 10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" SupportsExpression="true"></CustomProperty>
 <CustomProperty Name="AccessMode" DataType="Int32" TypeConverter="CData.SSIS.HubDB.AccessModeToStringConverter">0</CustomProperty>
 <CustomProperty Name="TableOrView" DataType="String" UITypeEditor="Microsoft.DataTransformationServices.Controls.ModalMultilineStringEditor, Microsoft.DataTransformationServices.Controls, Version= 10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" SupportsExpression="true">[<#=tableName#>]</CustomProperty>
 <CustomProperty Name="ExecStoredProcedure" DataType="Boolean">false</CustomProperty>
 </CustomProperties>
 <OutputPaths>
 <OutputPath Name="CData HubDB Source Output">
 <OutputColumns>
<#=HelperClass.GetColumnDefs(columns,HelperClass.OUTPUT_WITH_ERROR) #>
 </OutputColumns>
 <ExternalColumns>
<#=HelperClass.GetColumnDefs(columns,HelperClass.EXTERNAL) #>
 </ExternalColumns>
 </OutputPath>
 <OutputPath Name="CData HubDB Source Error Output" IsErrorOutput="true">
 <OutputColumns>
<#=HelperClass.GetColumnDefs(columns,HelperClass.OUTPUT) #> 
 </OutputColumns>
 </OutputPath>
 </OutputPaths>
 <Connections>
 <Connection Name="HubDB 2018 Connection" ConnectionName="CData HubDB Connection Manager" />
 </Connections>
 </CustomComponent>
 <OleDbDestination Name="OLE DB Destination" ConnectionName="Destination" CheckConstraints="false">
 <InputPath OutputPathName="CData HubDB Source.CData HubDB Source Output" />
 <ExternalTableOutput Table="[<#=tableName#>]" />
 </OleDbDestination>
 </Transformations>
 </Dataflow> 
<# entityCounter++;}#>
 </Tasks>
 </Package>
 </Packages>
</Biml>

<#+
public static class HelperClass {
 
 public static int OUTPUT_WITH_ERROR = 0;
 public static int EXTERNAL = 1;
 public static int OUTPUT = 2;
 public static int DATAOVERRIDE_COLUMN = 4;
 
 public static string GetDeleteAndCreateStatement(string tableName, DataRowCollection columns) {
 var dropAndCreateStatement = 
 "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}]') AND type IN (N'U'))\r\n" + 
 "DROP TABLE [{0}];\r\n" + 
 "CREATE TABLE [{0}]\r\n" + 
 "(\r\n" + 
 "{1}\r\n" + 
 ")\r\n" + 
 "ON \"default\";";
 string columnDefs = "";
 foreach (DataRow column in columns){
 string columnDef = " [{0}] {1}";
 string dataType = column["DATA_TYPE"].ToString();
 if (dataType.ToLower().StartsWith("bool")) {
 dataType = "bit";
 } else if (dataType.ToLower().Equals("real")) {
 dataType = "float";
 } else if (dataType.ToLower().Contains("varchar")) {
 var columnLength = column["CHARACTER_MAXIMUM_LENGTH"];
 dataType = "nvarchar(" + ((int)columnLength > 4000 ? "MAX" : columnLength) + ")"; 
 } 
 columnDefs += String.Format(columnDef,column["COLUMN_NAME"],dataType) + ",\r\n";
 
 }
 columnDefs = columnDefs.Remove(columnDefs.LastIndexOf(",\r\n"),",\r\n".Length);
 return String.Format(dropAndCreateStatement,tableName,columnDefs);
 }
 
 public static string GetColumnDefs(DataRowCollection columns, int columnType){
 var columnDefTemplate = "";
 var columnElements = "";
 
 if (columnType == DATAOVERRIDE_COLUMN) {
 columnDefTemplate = " <Column ErrorRowDisposition=\"FailComponent\" TruncationRowDisposition=\"FailComponent\" ColumnName=\"{0}\" />\r\n";
 foreach(DataRow column in columns) {
 var columnName = column["COLUMN_NAME"];
 columnElements += String.Format(columnDefTemplate,columnName);
 }
 return columnElements;
 } 
 if (columnType == OUTPUT_WITH_ERROR)
 columnDefTemplate = " <OutputColumn Name=\"{0}\" {1} ExternalMetadataColumnName=\"{0}\" ErrorRowDisposition=\"FailComponent\" TruncationRowDisposition=\"FailComponent\" />\r\n";
 else if (columnType == EXTERNAL)
 columnDefTemplate = " <ExternalColumn Name=\"{0}\" {1} />\r\n";
 else if (columnType == OUTPUT)
 columnDefTemplate = " <OutputColumn Name=\"{0}\" {1} />\r\n";
 
 foreach(DataRow column in columns){ 
 var columnName = column["COLUMN_NAME"];
 var dataTypeRaw = column["DATA_TYPE"].ToString().ToLower();
 var typeAndRelatedInfo = "";
 if (dataTypeRaw.Equals("bool")) {
 typeAndRelatedInfo = "DataType=\"Boolean\"";
 } else if (dataTypeRaw.Equals("date")) {
 typeAndRelatedInfo = "DataType=\"Date\" SsisDataTypeOverride=\"DT_DBDATE\"";
 } else if (dataTypeRaw.Equals("datetime")) {
 typeAndRelatedInfo = "DataType=\"DateTime\"";
 } else if (dataTypeRaw.Equals("real")) {
 typeAndRelatedInfo = ((int)column["NumericPrecision"] > 0 ? "Precision=\"18\" " : " ") + ((int)column["NumericScale"] > 0 ? "Scale=\"15\" " : " ") + "DataType=\"Decimal\"";
 } else if (dataTypeRaw.Equals("varchar")) {
 var columnLength = column["CHARACTER_MAXIMUM_LENGTH"];
 if ((int)columnLength > 4000) {
 typeAndRelatedInfo = "DataType=\"String\"";
 } else {
 typeAndRelatedInfo = "Length=\"" + columnLength + "\" DataType=\"String\" CodePage=\"1252\"";
 }
 }
 columnElements += String.Format(columnDefTemplate,columnName,typeAndRelatedInfo);
 }
 return columnElements;
 }
}
#>