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"> <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 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"> <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"> <ScriptComponentProjectReference ScriptComponentProjectName="SC_Demo"> </ScriptComponentProjectReference> </ScriptComponentSource> <DerivedColumns Name="DER Placeholder" /> </Transformations> </Dataflow> </Tasks> </Package> </Packages> </Biml>
3 comments:
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
}
))
{
try
{
Output0Buffer.AddRow();
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!
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 ;)
Great thanks Bill, I'll post one on Stack Overflow now...
Post a Comment