Biml - Script Component Source


Biml - Script Component Source

What is the Biml to create an SSIS Script Component Source? This is a very simplistic demo but you'll see the magic is distilled to two sections - the first part is where we define the output buffer, lines 20-24. In this case, I specify it is DemoOutput and then provide a columns collection with a single column, SourceColumn .

The second set of magic is in the CreateNewOutputRows , lines 54 to 58. There I use the buffer I defined above to inject a single row into it with a value of "Demo". Nothing fancy, everything is static from a Biml perspective but I needed to know the syntax before I could try something a little more advanced.

Biml Demo Script Component Source

Using this a simple matter of adding a new Biml file into an existing SSIS project and pasting the following code. What results from right-clicking on the file and selecting Generate New SSIS package will be a single SSIS package, BasicScriptComponentSource, with a Data Flow task "DFT Demo Source Component"

The data flow "DFT Demo Source Component" consists of our new Script Component, SCR Demo Source, and a Derived Column, DER Placeholder, so you can attach a data viewer if need be.

Use the following Biml to generate your package and feel free to tell me in the comments how you adapted it to solve a "real" problem.

<Biml xmlns=""><ScriptProjects><ScriptComponentProject Name="SC_Demo"><AssemblyReferences><AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSPipelineWrap" /><AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap" /><AssemblyReference AssemblyPath="Microsoft.SqlServer.PipelineHost" /><AssemblyReference AssemblyPath="Microsoft.SqlServer.TxScript" /><AssemblyReference AssemblyPath="System.dll" /><AssemblyReference AssemblyPath="System.AddIn.dll" /><AssemblyReference AssemblyPath="System.Data.dll" /><AssemblyReference AssemblyPath="System.Xml.dll" /></AssemblyReferences><OutputBuffers><!--Define what your buffer is called and what it looks likeMust set IsSynchronous as false. Otherwise it is a transformation(one row enters, one row leaves) and not a source.--><OutputBuffer Name="DemoOutput" IsSynchronous="false"><Columns><Column Name="SourceColumn" DataType="String" Length="50" /> </Columns></OutputBuffer> </OutputBuffers><Files><File Path="Properties/AssemblyInfo.cs">using System.Reflection;using System.Runtime.CompilerServices;[assembly: AssemblyTitle("SC_Demo")][assembly: AssemblyDescription("Demonstrate Script Component as source")][assembly: AssemblyConfiguration("")][assembly: AssemblyCompany("billinkc")][assembly: AssemblyProduct("SC_Demo")][assembly: AssemblyCopyright("Copyright @ 2015")][assembly: AssemblyTrademark("")][assembly: AssemblyCulture("")][assembly: AssemblyVersion("1.0.*")]</File><File Path="main.cs"><![CDATA[using System;using System.Data;using Microsoft.SqlServer.Dts.Pipeline.Wrapper;using Microsoft.SqlServer.Dts.Runtime.Wrapper;/// <summary>/// Demonstrate how to generate a Script Component Source in SSIS/// </summary>[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]public class ScriptMain : UserComponent{public override void CreateNewOutputRows(){DemoOutputBuffer.AddRow();DemoOutputBuffer.SourceColumn = "Demo";}}]]></File></Files></ScriptComponentProject></ScriptProjects><Packages><Package Name="BasicScriptComponentSource" ConstraintMode="Linear"><Tasks><Dataflow Name="DFT Demo Source Component"><Transformations><ScriptComponentSource Name="SCR Demo Source"><ScriptComponentProjectReferenceScriptComponentProjectName="SC_Demo"></ScriptComponentProjectReference></ScriptComponentSource><DerivedColumns Name="DER Placeholder" /></Transformations></Dataflow></Tasks></Package></Packages></Biml>