<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Annotations>
<Annotation>
File: Add.RowNumber.biml
Demo biml adding a rownumber to FileExport
Enviroment:
DB: MS-SQL2012
BIML: 1.6 VS2010 BIDS Helper
(c) John Minkjan biml101.blogspot.com
</Annotation>
</Annotations>
<FileFormats>
<FlatFileFormat
Name ="FFF AdventureWorks2012 Person Address"
ColumnNamesInFirstDataRow="true"
FlatFileType="Delimited"
HeaderRowDelimiter=";"
RowDelimiter="CRLF"
TextQualifer="|">
<Columns>
<Column Name="RowNumber" DataType ="String" Length="38" Delimiter =";"></Column>
<Column Name="AddressID" DataType ="String" Length="128" Delimiter =";"></Column>
<Column Name="AddressLine1" DataType ="String" Length="128" Delimiter =";"></Column>
<Column Name="AddressLine2" DataType ="String" Length="128" Delimiter =";"></Column>
<Column Name="City" DataType ="String" Length="128" Delimiter =";"></Column>
<Column Name="StateProvinceID" DataType ="String" Length="128" Delimiter =";"></Column>
<Column Name="PostalCode" DataType ="String" Length="128" Delimiter =";"></Column>
<Column Name="rowguid" DataType ="String" Length="128" Delimiter =";"></Column>
<Column Name="ModifiedDate" DataType ="String" Length="128" Delimiter ="CRLF"></Column>
</Columns>
</FlatFileFormat>
</FileFormats>
<Connections>
<OleDbConnection
Name="CnOleDBAdventureWorks2012"
ConnectionString="Data Source=.\MSSQL2012;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI10.1;Integrated Security=SSPI;Connect Timeout=30;"
RetainSameConnection="true">
</OleDbConnection>
<FlatFileConnection Name ="FF AdventureWorks2012 Person Address"
FileFormat ="FFF AdventureWorks2012 Person Address"
FilePath="C:\AdventureWorks2012.Person.Address.dat"></FlatFileConnection>
</Connections>
<Packages>
<Package Name="Add.RowNumber" ConstraintMode="Linear">
<Tasks>
<Dataflow Name="DFT Add Rownumber">
<Transformations>
<OleDbSource Name ="ODS AdventureWorks2012 Person Address"
ConnectionName ="CnOleDBAdventureWorks2012">
<DirectInput>
SELECT [AddressID]
,[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks2012].[Person].[Address]
</DirectInput>
</OleDbSource>
<DerivedColumns Name="DC ADD RowNumber Column">
<Columns>
<Column Name="RowNumber" DataType ="Int64">0</Column>
</Columns>
</DerivedColumns>
<ScriptComponentTransformation Name ="SC Add RowNumber">
<ScriptComponentProject>
<ScriptComponentProject Name="SC_AddRowNumber">
<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>
<InputBuffer Name="Input0">
<Columns>
<Column Name="RowNumber" DataType ="Int64" UsageType ="ReadWrite"></Column>
</Columns>
</InputBuffer>
<OutputBuffers>
<OutputBuffer Name="Output0"></OutputBuffer>
</OutputBuffers>
<Files>
<File Path ="AssemblyInfo.cs">
using System.Reflection;
using System.Runtime.CompilerServices;
[assembly: AssemblyTitle("SC_AddRowNumber")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("")]
[assembly: AssemblyProduct("SC_AddRowNumber")]
[assembly: AssemblyCopyright("Copyright @ 2012")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
[assembly: AssemblyVersion("1.0.*")]
</File>
<File Path ="main.cs">
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
Int64 lRownumber = 0;
public override void PreExecute()
{
base.PreExecute();
}
public override void PostExecute()
{
base.PostExecute();
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
lRownumber = lRownumber + 1;
Row.RowNumber = lRownumber ;
}
}
</File>
</Files>
</ScriptComponentProject>
</ScriptComponentProject>
</ScriptComponentTransformation>
<FlatFileDestination Name ="FFD AdventureWorks2012 Person Address"
ConnectionName ="FF AdventureWorks2012 Person Address"
Overwrite ="true">
</FlatFileDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
</Packages>
</Biml>
This will get you:
Till Next Time
How can we get the column name in Input0Buffer dynamically without mentioning hard code
ReplyDeleteHi ,
ReplyDeleteI am working on BIML Script component, where I am taking data from OLEDB Source.
In Script Component I want all the input columns to be checked by default as input.
I have no idea how to proceed for the same.
Below is my code :-
-->
using System.Reflection;
using System.Runtime.CompilerServices;
[assembly: AssemblyTitle("SC_Example.csproj")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("Ciber Nederland")]
[assembly: AssemblyProduct("SC_Example.csproj")]
[assembly: AssemblyCopyright("Copyright @ Ciber Nederland 2012")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
[assembly: AssemblyVersion("1.0.*")]
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Security.Cryptography;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Reflection;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
string strColumnsValue = string.Empty;
Type rowType = Row.GetType();
PropertyInfo columnProperty;
MD5 md5 = new MD5CryptoServiceProvider();
Encoder enc = System.Text.Encoding.Unicode.GetEncoder();
foreach(IDTSInputColumn100 Rw in this.ComponentMetaData.InputCollection[0].InputColumnCollection)
{
columnProperty = rowType.GetProperty(Rw.Name);
strColumnsValue += Convert.ToString(columnProperty.GetValue(Row,null));
}
byte[] bbb = new byte[strColumnsValue.Length * 3];
bbb = UnicodeEncoding.Unicode.GetBytes(strColumnsValue);
byte[] hash = md5.ComputeHash(bbb);
String strHash = Convert.ToBase64String(hash);
Row.RowChecksum = strHash;
}
}