A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Monday, October 26, 2015

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="http://schemas.varigence.com/biml.xsd">
        <ScriptComponentProject Name="SC_Demo">
                <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" />
                Define what your buffer is called and what it looks like
                Must set IsSynchronous as false. Otherwise it is a transformation
                (one row enters, one row leaves) and not a source.
                <OutputBuffer Name="DemoOutput" IsSynchronous="false">
                        <Column Name="SourceColumn" DataType="String" Length="50" /> 
                <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 Path="main.cs">
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

    /// &lt;summary&gt;
    /// Demonstrate how to generate a Script Component Source in SSIS
    /// &lt;/summary&gt;
    public class ScriptMain : UserComponent

        public override void CreateNewOutputRows()
            DemoOutputBuffer.SourceColumn = "Demo";

        <Package Name="BasicScriptComponentSource" ConstraintMode="Linear">
                <Dataflow Name="DFT Demo Source Component">
                        <ScriptComponentSource Name="SCR Demo Source">
                        <DerivedColumns Name="DER Placeholder" />


Andrew Guyton said...

Another great post Bill...I'm having some issues trying to implement this in one of my BIML projects but keep getting an error:

'Output0Buffer' does not contain a definition for 'ColumnName' and no extension method 'ColumnName' accepting a first argument of type 'Output0Buffer' could be found (are you missing a using directive or an assembly reference?). This is occurring for each column. The columns are dynamic and come from a c# class so should be the same names and casing everywhere.

Output buffer is defined as below:

The script task code where I am trying to add to the buffer is below:

public override void CreateNewOutputRows()
foreach (var myXmlData in (
from elements in StreamReader(sourceFile, "INSTRUMENT")
select new
ColumnName = elements.Element("ColumnName").Value

Output0Buffer.ColumnName = myXmlData.ColumnName;
catch (Exception e)
string errorMessage = string.Format("Data retreval failed: {0}", e.Message);
bool cancel;
ComponentMetaData.FireError(0, ComponentMetaData.Name, errorMessage,string.Empty,0, out cancel);

I've checked the code in a console app and it reads the XML file fine, but no luck with the BIML. There are about 250 odd columns so I am trying to avoid doing this manually so if you have any ideas what I am doing wrong I'd really appreciate it!

Bill Fellows said...

Hi Andrew and thanks,
The Output buffer definition got swallowed up when you posted but I'm assuming it's similar to what I had above. It sounds like you should have multiple columns in the output0buffer collection but the above assumes it's a single column output buffer with a column literally named ColumnName - is that intended? Also, feel free to put a full question over on stackoverflow and tag it with Biml. I have an alert that fires for those questions and trying to troubleshoot over the comments is probably going to be painful ;)

Andrew Guyton said...

Great thanks Bill, I'll post one on Stack Overflow now...