Creating a ScriptComponentProject

Defines a 'ScriptComponentProject' named 'LoadCrimeDataFeed' that calls the legacy Azure DataMarket Crimes OData service through 'System.Data.Services.Client', supplying credentials from the 'UserName' and 'AccountKey' read-only variables. The script pages over the 'CityCrime' entity set using Skip and Take, copying each record into the 'ODataOutput' buffer (declared as 'IsSynchronous="false"' so the component acts as a source). All referenced assemblies and the entry-point attribute are declared explicitly in the project metadata so SSIS can compile the script at design time. Azure DataMarket has since been retired, so this snippet is most useful as a complete template for any 'ScriptComponentProject' that calls an OData or HTTP API and emits rows. Adjust the entity types, output columns, and 'InitializeDataSource' implementation to match the service you need to read.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <ScriptProjects>
    <ScriptComponentProject ProjectCoreName="SC_2bca370105ff4883a705860bac68cfba" Name="LoadCrimeDataFeed">
      <AssemblyReferences>
        <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSPipelineWrap.dll" />
        <AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap.dll" />
        <AssemblyReference AssemblyPath="Microsoft.SqlServer.PipelineHost.dll" />
        <AssemblyReference AssemblyPath="Microsoft.SqlServer.TxScript.dll" />
        <AssemblyReference AssemblyPath="System.dll" />
        <AssemblyReference AssemblyPath="System.Data.dll" />
        <AssemblyReference AssemblyPath="System.Windows.Forms.dll" />
        <AssemblyReference AssemblyPath="System.Xml.dll" />
        <AssemblyReference AssemblyPath="System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=MSIL, Custom=null" />
        <AssemblyReference AssemblyPath="System.Data.Services, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=MSIL, Custom=null" />
        <AssemblyReference AssemblyPath="System.Data.DataSetExtensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=MSIL, Custom=null" />
        <AssemblyReference AssemblyPath="System.Data.Services.Client, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089, processorArchitecture=MSIL, Custom=null" />
      </AssemblyReferences>
      <OutputBuffers>
        <OutputBuffer Name="ODataOutput" IsSynchronous="false">
          <Columns>
            <Column Name="ROWID" DataType="Int32" />
            <Column Name="State" DataType="String" Length="20" />
            <Column Name="City" DataType="String" Length="50" />
            <Column Name="Year" DataType="Int32" />
            <Column Name="Population" DataType="Int32" />
            <Column Name="PropertyCrime" DataType="Int32" />
            <Column Name="Burglary" DataType="Int32" />
            <Column Name="LarcenyTheft" DataType="Int32" />
            <Column Name="MotorVehicleTheft" DataType="Int32" />
            <Column Name="Arson" DataType="Int32" />
          </Columns>
        </OutputBuffer>
      </OutputBuffers>
      <Files>
        <File Path="AssemblyInfo.cs">
using System.Reflection;
using System.Runtime.CompilerServices;

//
// General Information about an assembly is controlled through the following 
// set of attributes. Change these attribute values to modify the information
// associated with an assembly.
//
[assembly: AssemblyTitle("SC_2bca370105ff4883a705860bac68cfba.csproj")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("Varigence")]
[assembly: AssemblyProduct("SC_2bca370105ff4883a705860bac68cfba.csproj")]
[assembly: AssemblyCopyright("Copyright @ Varigence 2011")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
//
// Version information for an assembly consists of the following four values:
//
//      Major Version
//      Minor Version 
//      Build Number
//      Revision
//
// You can specify all the values or you can default the Revision and Build Numbers 
// by using the '*' as shown below:

[assembly: AssemblyVersion("1.0.*")]		
        </File>

        <File Path="main.cs">
/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/

using System;
using System.Data;
using System.Linq;
using System.Net;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
  public override void PreExecute()
  {  
    base.PreExecute();
  }

  public override void PostExecute()
  {
    base.PostExecute();
  }

  public override void CreateNewOutputRows()
  {
    var context = InitializeDataSource();

    int skip = 0;
    const int take = 100;

    while (true)
    {
      var rows = context.CityCrime.Skip(skip).Take(take);

      if (rows.Count() == 0)
      {
        break;
      }

      foreach (var row in rows)
      {
        this.ODataOutputBuffer.AddRow();
        this.ODataOutputBuffer.Arson = row.Arson;
        this.ODataOutputBuffer.Burglary = row.Burglary;
        this.ODataOutputBuffer.City = row.City;
        this.ODataOutputBuffer.LarcenyTheft = row.LarcenyTheft;
        this.ODataOutputBuffer.MotorVehicleTheft = row.MotorVehicleTheft;
        this.ODataOutputBuffer.Population = row.Population;
        this.ODataOutputBuffer.PropertyCrime = row.PropertyCrime;
        this.ODataOutputBuffer.ROWID = row.ROWID; 
        this.ODataOutputBuffer.State = row.State;
        this.ODataOutputBuffer.Year = row.Year;
      }  

      skip += take;
    }
  }

  private datagovCrimesContainer InitializeDataSource()
  {      
    var context = new datagovCrimesContainer(new Uri("https://api.datamarket.azure.com/Data.ashx/data.gov/Crimes/"));     
    context.Credentials = new NetworkCredential(Variables.UserName, Variables.AccountKey);    
    return context;
  }
} 
        </File>
      </Files>
      <ReadOnlyVariables>
        <Variable Namespace="User" DataType="String" VariableName="UserName" />
        <Variable Namespace="User" DataType="String" VariableName="AccountKey" />
      </ReadOnlyVariables>
    </ScriptComponentProject>
  </ScriptProjects>
</Biml>