c#sql-servervisual-studiossisbiml
Set row delimiter property on Flat File Connector
I am creating a BIML script that will allow me to import CSV's.
However, the property Row Delimiter on the Flat File Connection Manager is somehow set to Vertical Bar, just as the Column Delimiter is. That prevents me from importing the files.
The column delimiter as a vertival bar right, I just do not get why it sets the row delimiter as vertical bar also...
Whenever I manually set the Row Delimiter to CRLF the thing works like a charm. But from BIML it is always set to the same value as the column delimiter.
I can not find where that happens....
I tried to use expressions in several ways to set the Row Delimiter but alas.....
Does anyone have any clue?
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#
string Applicatie = "BIML";
string Prefix = "import";
string fileName;
string path = @"c:\temp";
string[] myFiles = Directory.GetFiles(path, "*.csv");
string[] myColumns;
#>
<FileFormats>
<#
foreach (string filePath in myFiles)
{
#>
<FlatFileFormat Name="FlatFileFormat<#=Path.GetFileNameWithoutExtension(filePath)#>" RowDelimiter="CRLF" ColumnNamesInFirstDataRow="true" IsUnicode="false">
<Columns>
<#
StreamReader myFile = new StreamReader(filePath);
myColumns = myFile.ReadLine().Replace("\"","").Split('|');
myFile.Close();
// to determine the column delimiter
int columnCount = 0;
string columnDelimiter = "|";
foreach (string myColumn in myColumns)
{
columnCount++;
if (columnCount == myColumns.Length)
{
columnDelimiter = "CRLF";
}
{
columnDelimiter = "|";
}
#>
<Column Name="<#=myColumn#>" DataType = "String" Length="250" Delimiter="<#=columnDelimiter#>"></Column>
<# } #>
</Columns>
</FlatFileFormat>
<#}#>
</FileFormats>
<Connections>
<#
foreach (string filePath in myFiles)
{
#>
<FlatFileConnection Name="FF_CSV-<#=Path.GetFileNameWithoutExtension(filePath)#>"
FilePath="<#=filePath#>"
FileFormat="FlatFileFormat<#=Path.GetFileNameWithoutExtension(filePath)#>"
>
<Expressions>
<Expression ExternalProperty="TextQualifier">"\""</Expression>
<Expression ExternalProperty="HeaderRowDelimiter">"CRLF"</Expression>
<Expression ExternalProperty="RowDelimiter">"CRLF"</Expression>
</Expressions>
</FlatFileConnection>
<# } #>
<OleDbConnection
Name="STG_<#=Applicatie#>"
ConnectionString="Data Source=SQLSERVER;Initial Catalog=TEST;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;">
</OleDbConnection>
<Connection ConnectionName="FF_CSV - <#=Path.GetFileNameWithoutExtension(filePath)#>">
<Expressions>
<Expression ExternalProperty="TextQualifier">"\""</Expression>
<Expression ExternalProperty="HeaderRowDelimiter">"CRLF"</Expression>
<Expression ExternalProperty="RowDelimiter">"CRLF"</Expression>
</Expressions>
</Connection>
</Connections>
<Packages>
<# // Loop trough the files
int TableCount = 0;
foreach (string filePath in myFiles)
{
TableCount++;
fileName = Path.GetFileNameWithoutExtension(filePath);
#>
<Package Name="stg_<#=Prefix#>_<#=TableCount.ToString()#>_<#=fileName#>" ConstraintMode="Linear" AutoCreateConfigurationsType="None" ProtectionLevel="EncryptSensitiveWithPassword" PackagePassword="secret">
<Variables>
<Variable Name="CountStage" DataType="Int32" Namespace="User">0</Variable>
</Variables>
<Tasks>
<ExecuteSQL ConnectionName="STG_<#=Applicatie#>" Name="SQL - Truncate <#=fileName#>">
<DirectInput>TRUNCATE TABLE dbo.<#=Prefix#>_<#=fileName#></DirectInput>
</ExecuteSQL>
<Dataflow Name="DFT-Transport CSV_<#=fileName#>">
<Transformations>
<FlatFileSource Name="SRC_FF-<#=fileName#> " ConnectionName="FF_CSV - <#=Path.GetFileNameWithoutExtension(filePath)#>">
</FlatFileSource>
<OleDbDestination ConnectionName="STG_<#=Applicatie#>" Name="OLE_DST - <#=fileName#>" >
<ExternalTableOutput Table="dbo.<#=Prefix#>_<#=fileName#>"/>
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
<# } #>
<!-- Create Master Package -->
<Package Name="stg_<#=Prefix#>_0_Master" ConstraintMode="Parallel" AutoCreateConfigurationsType="None" ProtectionLevel="EncryptSensitiveWithPassword" PackagePassword="secret">
<Tasks>
<# int TableCount2 = 0;
foreach (string filePath in myFiles)
{
TableCount2++;
fileName = Path.GetFileNameWithoutExtension(filePath); #>
<ExecutePackage Name="stg_<#=Prefix#>_<#=TableCount2.ToString()#>_<#=fileName#>">
<ExternalProjectPackage Package="stg_<#=Prefix#>_<#=TableCount2.ToString()#>_<#=fileName#>.dtsx" />
</ExecutePackage>
<#
}
#>
</Tasks>
</Package>
</Packages>
</Biml>
<!--Includes/Imports for C#-->
<#@ template language="C#" hostspecific="true"#>
<#@ import namespace="System.Data"#>
<#@ import namespace="System.IO"#>
This is the actual BIML outputted:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<Connections>
<FlatFileConnection Name="FF_CSV - AB" FilePath="c:\temp\AB.csv" FileFormat="FlatFileFormatAB">
<Expressions>
<Expression ExternalProperty="TextQualifier">"\""</Expression>
<Expression ExternalProperty="RowDelimiter">"CRLF"</Expression>
</Expressions>
</FlatFileConnection>
<Connection Name="STG_BIML" ConnectionString="Data Source=SQLSERVER;Initial Catalog=TEST;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" />
</Connections>
<Packages>
<Package Name="stg_import_1_AB" ConstraintMode="Linear" ProtectionLevel="EncryptSensitiveWithPassword" PackagePassword="secret">
<Variables>
<Variable Name="CountStage" DataType="Int32">0</Variable>
</Variables>
<Tasks>
<ExecuteSQL Name="SQL - Truncate AB" ConnectionName="STG_BIML">
<DirectInput>TRUNCATE TABLE dbo.import_AB</DirectInput>
</ExecuteSQL>
<Dataflow Name="DFT - Transport CSV_AB">
<Transformations>
<FlatFileSource Name="SRC_FF - AB " ConnectionName="FF_CSV - AB" />
<OleDbDestination Name="OLE_DST - AB" ConnectionName="STG_BIML">
<ExternalTableOutput Table="dbo.import_AB" />
</OleDbDestination>
</Transformations>
</Dataflow>
</Tasks>
</Package>
<Package Name="stg_import_0_Master" ProtectionLevel="EncryptSensitiveWithPassword" PackagePassword="secret">
<Tasks>
<ExecutePackage Name="stg_import_1_AB">
<ExternalProjectPackage Package="stg_import_1_AB.dtsx" />
</ExecutePackage>
</Tasks>
</Package>
</Packages>
<FileFormats>
<FlatFileFormat Name="FlatFileFormatAB" ColumnNamesInFirstDataRow="true" RowDelimiter="CRLF" IsUnicode="false">
<Columns>
<Column Name="Relatienummer" Length="250" DataType="String" Delimiter="|" />
<Column Name="Tussenpersoon" Length="250" DataType="String" Delimiter="|" />
<Column Name="Polisnummer" Length="250" DataType="String" Delimiter="|" />
<Column Name="Subpakketpolisnummer" Length="250" DataType="String" Delimiter="|" />
<Column Name="Product" Length="250" DataType="String" Delimiter="|" />
<Column Name="Maatschappij" Length="250" DataType="String" Delimiter="|" />
<Column Name="CodeVerzekering" Length="250" DataType="String" Delimiter="|" />
<Column Name="Branche" Length="250" DataType="String" Delimiter="|" />
<Column Name="Termijn" Length="250" DataType="String" Delimiter="|" />
<Column Name="SoortBedrijf" Length="250" DataType="String" Delimiter="|" />
<Column Name="SBIcode" Length="250" DataType="String" Delimiter="|" />
<Column Name="VerzekerdBedragA" Length="250" DataType="String" Delimiter="|" />
<Column Name="VerzekerdBedragB" Length="250" DataType="String" Delimiter="|" />
</Columns>
</FlatFileFormat>
</FileFormats>
</Biml>
Solution
Found it
if (columnCount == myColumns.Length)
{
columnDelimiter = "CRLF";
}
{
columnDelimiter = "|";
}
You lack an else in there. It should be
if (columnCount == myColumns.Length)
{
columnDelimiter = "CRLF";
}
else
{
columnDelimiter = "|";
}
Minimal repro
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#
List<string> myColumns = new List<string>{"A", "B", "C"};
#>
<FileFormats>
<FlatFileFormat Name="SO_66970366" RowDelimiter="CRLF" ColumnNamesInFirstDataRow="true" IsUnicode="false">
<Columns>
<#
int columnCount = 0;
string columnDelimiter = "";
WriteLine($"<!-- ref count {myColumns.Count} -->");
foreach(string myColumn in myColumns)
{
columnCount++;
bool finalColumn = columnCount == myColumns.Count;
WriteLine($"<!-- actual count {columnCount} -->");
WriteLine($"<!-- what {columnCount == myColumns.Count} {finalColumn} -->");
if (finalColumn)
{
columnDelimiter = "CRLF";
}
else
{
columnDelimiter = "|";
}
WriteLine($"<!-- delimiter {columnDelimiter} -->");
#>
<Column Name="<#=myColumn#>" DataType = "String" Length="250" Delimiter="<#=columnDelimiter#>" />
<#
}
#>
</Columns>
</FlatFileFormat>
</FileFormats>
</Biml>
Emitted Biml would look like
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<FileFormats>
<FlatFileFormat Name="SO_66970366" RowDelimiter="CRLF" ColumnNamesInFirstDataRow="true" IsUnicode="false">
<Columns>
<!-- ref count 3 -->
<!-- act count 1 -->
<!-- what False False -->
<!-- delimiter | -->
<Column Name="A" DataType = "String" Length="250" Delimiter="|" />
<!-- act count 2 -->
<!-- what False False -->
<!-- delimiter | -->
<Column Name="B" DataType = "String" Length="250" Delimiter="|" />
<!-- act count 3 -->
<!-- what True True -->
<!-- delimiter CRLF -->
<Column Name="C" DataType = "String" Length="250" Delimiter="|" />
</Columns>
</FlatFileFormat>
</FileFormats>
</Biml>