Business Intelligence Markup Language

BIML stands for Business Intelligence Markup Language, offers us a solution. With BIML you can easily generate SSIS packages based on metadata, allowing us to effectively apply code reuse, templates and patterns in our ETL solutions.

BIML is a dialect of XML and can be used to specify business intelligence and data warehouse solutions. It is 100% compatible with SSIS and SSAS. When you generate for example an SSIS package, you can further edit it in Visual Studio, as if you created the package manually.

The first step for getting started with Biml is installing BIDS Helper.

Download and Install BIDS Helper using the following or other sites:
http://bidshelper.codeplex.com/releases/view/114725

Be sure to select the BIDS Helper version that matches your SQL Server installation (2005, 2008, 2012).
Then, install BIDS Helper

To create a Biml file:

1. Begin by opening BIDS / SSDT and creating a new SSIS project.
2. Name the project Getting Started with BimlScript.
3. Right-click on the SSIS Packages folder inside Solution Explorer, and then click Add New Biml File. 
4. Note that you can add a new Biml file by right-clicking the Data Sources, Data Source Views, or SSIS
5. Packages folders, as well as the project itself.
6. A file, named BimlScript.biml, will be added to the Miscellaneous folder in the project.
Rename the file to My Package.biml.

Now that a Biml file has been created, let’s start writing Biml:

Double-click My Package.biml to open it in the Visual Studio editor. You’ll see that opening and closing Biml tags have already been added.

Add the Biml script between the Biml tags

With our Biml added, we can now generate a SSIS package:

Right click the My Package.biml file. In its context menu, click Generate SSIS Packages.
After a few moments, you’ll see that a new package named MyPackage.dtsx has been added in the SSIS Packages folder. Double click MyPackage.dtsx to open it.

By clicking on Generate SSIS Packages, the Biml file was sent to the Biml engine, which is included in BIDS Helper. The Biml engine then compiled the Biml file to create the SSIS package. Finally, the newly generated package was added to the SSIS project.
-----------------------------------------------
BIML referrece site
----------------------
http://www.sqlservercentral.com/search/?q=biml
---------------------------------------------------

Retrieving Blog Post data from Radian6 API

After retrieved the Topic profile related tables, we can retrieve the blog related tables using the ID column
from the "Article" table.

Below is the step to retrieve Blog Post related data:

Create variables as shown below:

Create SQL task the get all IDs from Article table which will be passed along with Topic Profile Id to get data.

apply the following script in SQLStatement area in SQL Task Editor and go to Result Set to get the 
Variable name as User::PostIdResultSet.

SELECT CAST(ID AS VARCHAR(20)), TOPICPROFILEID FROM RAD.ARTICLE
WHERE media_provider <> 'TWITTER'

//here we need separate authentication to access Twitter account hence we don't use Twitter account ids.

Create For Each Loop Container as shown below:

now create Script Task and provide the auth_user, auth_pass and auth_appkey in ReadOnlyVariables
and click EditScript and apply the following script.

---------------------------------------------
#region Help:  Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
 * a .Net application within the context of an Integration Services control flow. 
 * 
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use
 * Integration Services features within this script task. */
#endregion


#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Linq;
using System.Web;
//using System.Web.UI;
//using System.Web.UI.WebControls;
using System.Net;
//using System.Web.Script.Serialization;
using System.Text.RegularExpressions;
#endregion

namespace ST_1adf35b5c0614ec086d2bffb896ab7c3
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
       // string auth_user = Dts.Variables["CustomerCount"].Value;
        #region Help:  Using Integration Services variables and parameters in a script
        ............
        #endregion


/// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
public void Main()
{
            string token = "";
            string Json = Call_Rest(token);
            //Response.Write(Json);
            token = Regex.Match(Json, @"\<token\b[^>]*\>\s*(?<token>[\s\S]*?)\</token\>", RegexOptions.IgnoreCase).Groups["token"].Value;
            // Response.Write(token);
            Json = Call_Rest(token);
            string topic = Call_RestFectchData(token);
            Dts.TaskResult = (int)ScriptResults.Success;
}
        public string auth_user
        {
            get
            {
                return Dts.Variables["User::auth_user"].Value.ToString();
            }
        }
        public string auth_pass
        {
            get
            {
                return Dts.Variables["User::auth_pass"].Value.ToString();
            }
        }
        public string auth_appkey
        {
            get
            {
                return Dts.Variables["User::auth_appkey"].Value.ToString();
            }
        }
        public string PostIds
        {
            get
            {
                return Dts.Variables["User::PostIds"].Value.ToString();
            }
        }
        public string TopicProfileId
        {
            get
            {
                return Dts.Variables["User::TopicProfileId"].Value.ToString();
            }
        }
        
         string Call_RestFectchData(string token)
        {
            string responseXml = string.Empty;
            string Url = "https://api.radian6.com/socialcloud/v1/post/workflow/" + PostIds + "/" + TopicProfileId + "";
            using (WebClient client = new WebClient())
            {

                if (token == null || token.Trim() == "")
                {
                }
                else
                {

                    client.Headers.Add("auth_token", token);
                    client.Headers.Add("auth_appkey", auth_appkey);

                }

                try
                {
                    responseXml = client.DownloadString(Url);

                }
                catch (Exception exc)
                {
                    responseXml = "ERROR: " + exc.Message;
                }
            }
            System.IO.File.WriteAllText(@"M:\SSIS\rad\Evangelism\Blogdetails.xml", responseXml);
            return responseXml;
        }
        string Call_Rest(string token)
        {
            //REST call:
            string auth_token = token;
            string responseXml = "";
            string url = "https://api.radian6.com/socialcloud/v1/auth/authenticate";

            ServicePointManager.SecurityProtocol = SecurityProtocolType.Ssl3;
            using (WebClient client = new WebClient())
            {
                //add HTTP headers (for auth)
                client.Headers.Add("auth_appkey", auth_appkey);
                if (auth_token == null || auth_token.Trim() == "")
                {
                    client.Headers.Add("auth_user", auth_user);
                    client.Headers.Add("auth_pass", auth_pass);
                }
                else
                {
                    url = "https://api.radian6.com/socialcloud/v1/topics";
                    client.Headers.Add("auth_token", auth_token);
                }

                //send web request; get web response as XML
                try
                {
                    responseXml = client.DownloadString(url);
                }
                catch (Exception exc)
                {
                    responseXml = "ERROR: " + exc.Message;
                }
            }

            return responseXml;
        }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

}
}

------------------------------------------------------
Get in to DFT, Using XML Source, select the XML file and Generate XSD. From that source, you can map to all the required tables.

Retrieving all Topic Profile data from Radian6 API

Once we get the Topic Profile detais, we can retrive all the tables related to the particular topicfilterid.

Below is the step to retrive topic related data

Create variables as shown below:

Create SQL task the get all Topicprofile ids which will be passed along with pages to get data.

apply the following script in SQLStatement area in SQL Task Editor and go to Result Set to get the 
Variable name as User::TopicProfileId.

 WITH cte AS(SELECT topicFilterId
, 1 AS PageNo
 FROM rad.TopicProfile
 UNION 
 SELECT topicFilterId
, 2 AS PageNo
 FROM rad.TopicProfile
 UNION 
 SELECT topicFilterId
, 3 AS PageNo
 FROM rad.TopicProfile)

 SELECT topicFilterId, PageNo FROM cte


Create For Each Loop Container as shown below

now create Script Task and provide the auth_user, auth_pass and auth_appkey in ReadOnlyVariables
and click EditScript and apply the following script.

--------------------------------------------------------

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Linq;
using System.Web;
//using System.Web.UI;
//using System.Web.UI.WebControls;
using System.Net;
//using System.Web.Script.Serialization;
using System.Text.RegularExpressions;
#endregion

namespace ST_1adf35b5c0614ec086d2bffb896ab7c3
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
       // string auth_user = Dts.Variables["CustomerCount"].Value;
        #region Help:  Using Integration Services variables and parameters in a script
        .............
        #endregion


/// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
public void Main()
{
            string token = "";
            string Json = Call_Rest(token);
            //Response.Write(Json);
            token = Regex.Match(Json, @"\<token\b[^>]*\>\s*(?<token>[\s\S]*?)\</token\>", RegexOptions.IgnoreCase).Groups["token"].Value;
            // Response.Write(token);
            Json = Call_Rest(token);
            string topic = Call_RestFectchData(token);
            Dts.TaskResult = (int)ScriptResults.Success;
}
        public string auth_user
        {
            get
            {
                return Dts.Variables["User::auth_user"].Value.ToString();
            }
        }
        public string auth_pass
        {
            get
            {
                return Dts.Variables["User::auth_pass"].Value.ToString();
            }
        }
        public string auth_appkey
        {
            get
            {
                return Dts.Variables["User::auth_appkey"].Value.ToString();
            }
        }
        public string topicprofile
        {
            get
            {
                return Dts.Variables["User::rowid"].Value.ToString();
            }
        }
        public string Pages
        {
            get
            {
                return Dts.Variables["User::Pages"].Value.ToString();
            }
        }
         string Call_RestFectchData(string token)
        {
            string responseXml = string.Empty;
            string Url = "https://api.radian6.com/socialcloud/v1/data/topicdata/realtime/24/"+topicprofile+"/1,2,3,4,5,6,7,8,10,9,11,12,13,14,16,15/"+ Pages +"/1000?includeWorkflow=1&includeSpam=0&merged=1&token=" + token + "0&extendedMediaTypes=2,3,4";
            using (WebClient client = new WebClient())
            {

                if (token == null || token.Trim() == "")
                {
                }
                else
                {

                    client.Headers.Add("auth_token", token);
                    client.Headers.Add("auth_appkey", auth_appkey);

                }

                try
                {
                    responseXml = client.DownloadString(Url);

                }
                catch (Exception exc)
                {
                    responseXml = "ERROR: " + exc.Message;
                }
            }
            //System.IO.File.WriteAllText(@"M:\SSIS\rad\profiles\topicProfile_"+topicprofile+"_"+ Pages +".xml", responseXml);
            System.IO.File.WriteAllText(@"M:\SSIS\rad\profiles\topicProfile.xml", responseXml);
            return responseXml;
        }
        string Call_Rest(string token)
        {
            //REST call:
            string auth_token = token;
            string responseXml = "";
            string url = "https://api.radian6.com/socialcloud/v1/auth/authenticate";

            ServicePointManager.SecurityProtocol = SecurityProtocolType.Ssl3;
            using (WebClient client = new WebClient())
            {
                //add HTTP headers (for auth)
                client.Headers.Add("auth_appkey", auth_appkey);
                if (auth_token == null || auth_token.Trim() == "")
                {
                    client.Headers.Add("auth_user", auth_user);
                    client.Headers.Add("auth_pass", auth_pass);
                }
                else
                {
                    url = "https://api.radian6.com/socialcloud/v1/topics";
                    client.Headers.Add("auth_token", auth_token);
                }

                //send web request; get web response as XML
                try
                {
                    responseXml = client.DownloadString(url);
                }
                catch (Exception exc)
                {
                    responseXml = "ERROR: " + exc.Message;
                }
            }

            return responseXml;
        }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

}
}

-------------------------------------------------------------------

Get in to DFT, Using XML Source, select the XML file and Generate XSD. From that source, you can map to all the required tables as shown below:

Retrieving Topic Profile list from Radian6 API

The first step in Radiant6 is to get topic profiles from API. To access the API, we need three important 
credentials those are UserName(auth_user), Password(auth_pass) and ApplicationKey(auth_appkey). We can get the appkey from the following site by providing the username and password. 

First, get the username and password for the "https://login.radian6.com/" site. Use this username and password as auth_user and auth_pass. Then get the username and password for the "https://secure.mashery.com/login/socialcloud.radian6.com/" site where in you can get the application key and use that as an auth_appkey in the authorisation part.

//When you have demo login credentials then use the demo site: https://demo-login.radian6.com/

Below is the steps to get topic profiles:

Create variables for the auth_user, auth_pass and auth_appkey as shown below

now create Script Task and provide the parameters in the ReadOnlyVariables as shown below
and click EditScript and applied the code as given below:

------------------------------------------
#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Collections.Generic;
using System.Linq;
using System.Web;
//using System.Web.UI;
//using System.Web.UI.WebControls;
using System.Net;
//using System.Web.Script.Serialization;
using System.Text.RegularExpressions;
#endregion

namespace ST_1adf35b5c0614ec086d2bffb896ab7c3
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
       // string auth_user = Dts.Variables["CustomerCount"].Value;
        #region Help:  Using Integration Services variables and parameters in a script
       ......
        #endregion


/// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
public void Main()
{
            string token = "";
            string Json = Call_Rest(token);
            //Response.Write(Json);
            token = Regex.Match(Json, @"\<token\b[^>]*\>\s*(?<token>[\s\S]*?)\</token\>", RegexOptions.IgnoreCase).Groups["token"].Value;
            // Response.Write(token);
            Json = Call_Rest(token);
            string topic = Call_RestFectchData(token);
            Dts.TaskResult = (int)ScriptResults.Success;
}
        public string auth_user
        {
            get
            {
                return Dts.Variables["User::auth_user"].Value.ToString();
            }
        }
        public string auth_pass
        {
            get
            {
                return Dts.Variables["User::auth_pass"].Value.ToString();
            }
        }
        public string auth_appkey
        {
            get
            {
                return Dts.Variables["User::auth_appkey"].Value.ToString();
            }
        }
         string Call_RestFectchData(string token)
        {
            string responseXml = string.Empty;
            string Url = "https://api.radian6.com/socialcloud/v1/topics?includeReactivation=true";
            using (WebClient client = new WebClient())
            {

                if (token == null || token.Trim() == "")
                {
                }
                else
                {

                    client.Headers.Add("auth_token", token);
                    client.Headers.Add("auth_appkey", auth_appkey);

                }

                try
                {
                    responseXml = client.DownloadString(Url);

                }
                catch (Exception exc)
                {
                    responseXml = "ERROR: " + exc.Message;
                }
            }
            System.IO.File.WriteAllText(@"M:\SSIS\rad\TopicProfile.xml", responseXml);
//here the TopicProfile xml file will be generated in the provided path
            return responseXml; 

            
        }
        string Call_Rest(string token)
        {
            //REST call:
            string auth_token = token;
            string responseXml = "";
            string url = "https://api.radian6.com/socialcloud/v1/auth/authenticate";


            ServicePointManager.SecurityProtocol = SecurityProtocolType.Ssl3;
            using (WebClient client = new WebClient())
            {
                //add HTTP headers (for auth)
                client.Headers.Add("auth_appkey", auth_appkey);
                if (auth_token == null || auth_token.Trim() == "")
                {
                    client.Headers.Add("auth_user", auth_user);
                    client.Headers.Add("auth_pass", auth_pass);

                }
                else
                {
                    url = "https://api.radian6.com/socialcloud/v1/topics";

                    client.Headers.Add("auth_token", auth_token);
                }

                //send web request; get web response as XML
                try
                {
                    responseXml = client.DownloadString(url);

                }
                catch (Exception exc)
                {
                    responseXml = "ERROR: " + exc.Message;

                }
            }

            return responseXml;
        }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

}
}
----------------------------------------------
now get into DFT and create XML Source task. In XML Source Editor, select the Data access mode as XML file location and brows the xml file location. Then click Generate XSD button to create XSD and provide the path to save it. Map the requried columns with the corresponding table.

Scenario of getting the largest amount from multiple max date entries of a customer

This is a scenario where getting the largest transaction amount of a customer in which there are multiple same max date entries available for him.

WITH CTE AS (SELECT ConstituentDefinitionId
,CompanyId
, RetailAmountReceived
,TransactionDate
,TransactionId
,SourceCodeId
, RN
FROM ( SELECT 
[ConstituentDefinitionId] 
 ,sc.CompanyId
 ,ts.RetailAmountReceived
 ,t.TransactionDate
 ,t.TransactionId
 ,t.SourceCodeId
 , ROW_NUMBER() OVER(PARTITION BY [ConstituentDefinitionId], ts.RetailAmountReceived ORDER BY transactiondate DESC) AS RN
 FROM [dbo].[Transaction] t
 INNER JOIN [dbo].[TransactionCompany] sc ON t.TransactionId = sc.TransactionId
 INNER JOIN dbo.TransactionSummary ts ON t.TransactionId = ts.TransactionId
WHERE t.Isbenevolent = 0 and RetailAmountReceived > 0 and TotalTransactionAmount > 0 ) AS t )


SELECT [ConstituentDefinitionId]
,CompanyId
, MAX(CONVERT(Binary(10),RetailAmountReceived) + CONVERT(Binary(10),[TransactionDate]) + CONVERT(Binary(10),[TransactionId]) + CONVERT(Binary(10),[SourceCodeId])) as LargestPurchaseData
FROM cte
WHERE RN = 1
GROUP BY [ConstituentDefinitionId], CompanyId

Scenario of getting first and last communication date of each customer

This is a scenario where I need to get the first and last communication date of each customer from a customer table along with corresponding communication Id and SourceCodeId


WITH cte AS(SELECT 
 [CustomerId] 
 ,sc.CompanyId

 ,MIN(CONVERT(VARCHAR, cm.CommunicationDate,121) + CONVERT(VARCHAR, cm.CommunicationId)+ '*' + CONVERT(VARCHAR,cm.SourceCodeId)) AS FirstCommunicationData
 ,MAX(CONVERT(VARCHAR, cm.CommunicationDate,121) + CONVERT(VARCHAR, cm.CommunicationId) + '*' + CONVERT(VARCHAR,cm.SourceCodeId)) AS LastCommunicationData

FROM dbo.Customer cm
INNER JOIN [dbo].[SourceCode] sc on cm.SourceCodeId = sc.SourceCodeId
WHERE CustomerId = 123 --Just used a customer id for test
GROUP BY CustomerId ,sc.CompanyId)

SELECT [CustomerId]
, CompanyId 
, CONVERT(DATE,SUBSTRING(FirstCommunicationData,1,23),121) [FirstCommunicationDate]
, CONVERT(INT,SUBSTRING(FirstCommunicationData,24,CHARINDEX('*',FirstCommunicationData)-24))[FirstCommunicationId]
, CONVERT(INT,SUBSTRING(FirstCommunicationData,CHARINDEX('*',FirstCommunicationData)+1,(LEN(FirstCommunicationData) - CHARINDEX('*',FirstCommunicationData)+1)))[FirstCommunicationSourceCodeID]
, CONVERT(DATE,SUBSTRING(LastCommunicationData,1,23),121) [LastCommunicationDate]
, CONVERT(INT,SUBSTRING(LastCommunicationData,24,CHARINDEX('*',LastCommunicationData)-24))[LastCommunicationId]
, CONVERT(INT,SUBSTRING(LastCommunicationData,CHARINDEX('*',LastCommunicationData)+1,(LEN(LastCommunicationData) - CHARINDEX('*',LastCommunicationData)+1)))[LastCommunicationSourceCodeID]
FROM cte



Using multiple char index:

WITH CTE AS(SELECT 
 [DonorId] 
 ,sc.CompanyId
 , MIN(CONVERT(VARCHAR,t.[TransactionDate],120) + CONVERT(VARCHAR,t.[TransactionId]) + '*' + CONVERT(VARCHAR,t.SourceCodeId)+ '&'+ CONVERT(VARCHAR,ts.TotalDonation)) [FirstDonationData]                            
 , MAX(CONVERT(VARCHAR,t.[TransactionDate],120) + CONVERT(VARCHAR,t.[TransactionId]) + '*' + CONVERT(VARCHAR,t.SourceCodeId)+ '&'+ CONVERT(VARCHAR,ts.TotalDonation)) [LastDonationData]
 , MAX(CONVERT(Binary(10),ts.TotalDonation) + CONVERT(Binary(10),t.[TransactionDate]) + CONVERT(Binary(10),t.[TransactionId]) + CONVERT(Binary(10),t.[SourceCodeId])) as LargestDonationData
 FROM [dbo].[Transaction] t
 INNER JOIN [dbo].[TransactionCompany] sc on t.TransactionId = sc.TransactionId
 INNER JOIN dbo.TransactionSummary ts ON t.TransactionId = ts.TransactionId
WHERE DonorId = 13383180
GROUP BY [DonorId], sc.CompanyId)

 SELECT DonorId
 , SUBSTRING([FirstDonationData],1,10) [FirstDonationDate]
 , REPLACE(SUBSTRING([FirstDonationData],11,LEN([FirstDonationData])), (SUBSTRING([FirstDonationData],CHARINDEX('*',[FirstDonationData]),LEN([FirstDonationData]))),'') [FirstDonationID]
 , REPLACE((SUBSTRING([FirstDonationData],CHARINDEX('*',[FirstDonationData])+1,LEN([FirstDonationData]))),(SUBSTRING([FirstDonationData],CHARINDEX('&',[FirstDonationData]),LEN([FirstDonationData]))),'') [FirstDonationSourceCodeId]
 , SUBSTRING([FirstDonationData],CHARINDEX('&',[FirstDonationData])+1,LEN([FirstDonationData])) [FirstDonationAmount]
 , SUBSTRING([LastDonationData],1,10) [LastDonationDate]
 , REPLACE(SUBSTRING([LastDonationData],11,LEN([LastDonationData])), (SUBSTRING([LastDonationData],CHARINDEX('*',[LastDonationData]),LEN([LastDonationData]))),'') [LastDonationID]
 , REPLACE((SUBSTRING([LastDonationData],CHARINDEX('*',[LastDonationData])+1,LEN([LastDonationData]))),(SUBSTRING([LastDonationData],CHARINDEX('&',[LastDonationData]),LEN([LastDonationData]))),'') [LastDonationSourceCodeId]
 , SUBSTRING([LastDonationData],CHARINDEX('&',[LastDonationData])+1,LEN([LastDonationData])) [LastDonationAmount]
 , CONVERT(money,SUBSTRING(LargestDonationData,1,10)) [LargestDonationAmount]
 , CONVERT(DATE,SUBSTRING(LargestDonationData,11,10)) [LargestDonationDate]
 , CONVERT(INT,SUBSTRING(LargestDonationData, 21,10)) [LargestDonationID]
 , CONVERT(INT,SUBSTRING(LargestDonationData,31,10)) [LargestDonationSourceCodeID]

FROM CTE

Retrieving Ooyala Engagement data from API

To retrieve Engagement data, we need to provide an ID which will be available from EmbedCode_Property table as text column. 

The following figure will illustrate the process flow of retrieving the Ooyala Engagement data from OoyalaAPI:

Select all the distinct Ids in SQL Task and have it in a result set. Create variable for it 
as an object.

Create For each loop container to have Script Task and DFT. In Script task provide the User:apikey and 
User:secretkey parameters in the ReadOnlyVariables and click the Edit Script button.

Provide the below mentioned code which will retrieve data using the referrence support of Json and OoyalaAPI cs files.

Here we need to add "Newtonsoft.Json.Net20.dll". Download the Json referrence file from the web and save it  in the corresponding foler. 

Go to Solution Explorer - Right Click Reference - Select Add Reference - Browse the Json file and add it.
Also add the JSON.cs and OoyalaAPI.cs files.

------------------------------------------------------------------

#region Help:  Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
 * a .Net application within the context of an Integration Services control flow. 
 * 
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use
 * Integration Services features within this script task. */
#endregion


#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net;
//using Newtonsoft.Json;
using System.IO;
using System.Security.Cryptography;
using System.Collections.Generic;
using System.Globalization;
using System.Text;
using System.Collections;
using System.Web;
using System.Xml;
using System.Xml.Linq;
using Newtonsoft.Json.Bson;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Schema;
using Newtonsoft.Json.Serialization;
using Newtonsoft.Json.Utilities;
//using System.Xml;

using System.Runtime.Serialization.Json;
using Newtonsoft.Json;
#endregion

namespace ST_e00b761ae09948d5aef4ab0b0a0e6a78
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
        #region Help:  Using Integration Services variables and parameters in a script
        ..........
        #endregion


/// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
        
        XmlDocument test1;
public void Main()
{
           
Call_RestFectchData();
Dts.TaskResult = (int)ScriptResults.Success;
           
}
        string Call_RestFectchData()
        {
            string responseXml = string.Empty;
            

            try
            {
                string api_key, secret_key, ContentKey;
                DateTime PlayDate;
                api_key = (string)Dts.Variables["User::apiKey"].Value;
                secret_key = (string)Dts.Variables["User::secretKey"].Value;
                ContentKey = (string)Dts.Variables["User::ContentKey"].Value;

                PlayDate = (DateTime)Dts.Variables["User::PlayDate"].Value;
                var date = PlayDate.ToString("yyyy-MM-dd");

                OoyalaAPI api = new OoyalaAPI(api_key,secret_key);

                Dictionary<String, String> parameters = new Dictionary<String, String>();
               
                Hashtable values = api.getHashtable("analytics/reports/asset/" + ContentKey + "/engagement/total/" + date ,parameters);

                var s2 = JsonConvert.SerializeObject(new { items = values });

                test1 =JsonToXML(s2);
                System.IO.File.WriteAllText(@"M:\ooyala\PlayHistory\Ooyala_PlayHistory.xml", test1.InnerXml);
                
              
            }
            catch (Exception exc)
            {
                responseXml = "ERROR: " + exc.Message;
            }

            return responseXml;
        }
        
        public XmlDocument JsonToXML(string json)
        {
            XmlDocument doc = new XmlDocument();

            using (var reader = JsonReaderWriterFactory.CreateJsonReader(Encoding.UTF8.GetBytes(json), XmlDictionaryReaderQuotas.Max))
            {
                XElement xml = XElement.Load(reader);
                doc.LoadXml(xml.ToString());
            }

            return doc;
        }
        }
    
        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

}

-------------------------------------------------------------
Now create DFT Task and add XML Source task and select the source files and generate the XSD file. From this XML source, we can map the columns to the corresponding tables.