Archives

Generating Biml Scripts Using T-SQL

This post is part of a series, Short Cuts to Biml Mastery which shows you how to learn BimlScript through a series of short cuts and remain productive while doing so. In the previous article, I showed you how to use Excel generate Biml. In this article, we’ll generate the entire Biml Script for an export package using only T-SQL. To refresh your memory, this is the Biml from a previous article that we need to automate:

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FlatFileConnection Name="conn_ff_Orders" FilePath="C:\FileExport\Orders.csv" FileFormat="fff_Orders"/>
    </Connections>
    <Packages>
        <Package Name="Export Orders" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive" PackageSubpath="Export Flat Files">
            <Tasks>
                <Dataflow Name="DFT Load file" >
                    <Transformations>
                        <OleDbSource Name="OLE_SRC Orders" ConnectionName="connNorthwind">
                            <DirectInput>
SELECT * FROM dbo.Orders
                            </DirectInput>
                        </OleDbSource>
                        <FlatFileDestination Name="FF_DEST Orders"  ConnectionName="conn_ff_Orders" Overwrite="true" ValidateExternalMetadata="false"/>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
    <FileFormats>
        <FlatFileFormat Name="fff_Orders" FlatFileType="Delimited"  ColumnNamesInFirstDataRow="true" TextQualifier=""">
            <Columns>
                <Column Name="OrderID" DataType="Int32" Length="0" Delimiter="Comma"/> 
                <Column Name="CustomerID" DataType="StringFixedLength" Length="5" Delimiter="Comma"/> 
                <Column Name="EmployeeID" DataType="Int32" Length="0" Delimiter="Comma"/> 
                <Column Name="OrderDate" DataType="DateTime" Length="0" Delimiter="Comma"/> 
                <Column Name="RequiredDate" DataType="DateTime" Length="0" Delimiter="Comma"/> 
                <Column Name="ShippedDate" DataType="DateTime" Length="0" Delimiter="Comma"/> 
                <Column Name="ShipVia" DataType="Int32" Length="0" Delimiter="Comma"/> 
                <Column Name="Freight" DataType="Currency" Length="0" Delimiter="Comma"/> 
                <Column Name="ShipName" DataType="String" Length="40" Delimiter="Comma"/> 
                <Column Name="ShipAddress" DataType="String" Length="60" Delimiter="Comma"/> 
                <Column Name="ShipCity" DataType="String" Length="15" Delimiter="Comma"/> 
                <Column Name="ShipRegion" DataType="String" Length="15" Delimiter="Comma"/> 
                <Column Name="ShipPostalCode" DataType="String" Length="10" Delimiter="Comma"/> 
                <Column Name="ShipCountry" DataType="String" Length="15" Delimiter="CRLF"/> 
            </Columns>
        </FlatFileFormat>
    </FileFormats>
</Biml>

We are also starting with the T-SQL script used last time. However, I’m adding a temp table to make code manipulation easier in later steps. I’m also adding a variable for Feed Name which we’ll need as well. I’ve highlighted the changes below.

USE Northwind
GO

DECLARE @SrcQuery NVARCHAR(4000), @FeedName NVARCHAR(25);
SELECT @SrcQuery = 'SELECT * FROM dbo.Orders', @FeedName = 'Orders';

IF object_id('tempdb..#FileFormatMetadata') IS NOT NULL DROP TABLE #FileFormatMetadata;
SELECT 
 t.[Column Name]
,t.[SQL DT]
,CASE 
WHEN t.[SQL DT] in ('char', 'varchar','text') THEN (t.max_length)
WHEN t.[SQL DT] in ('nchar', 'nvarchar','ntext') THEN (t.max_length/2)
ELSE 0 END [Length]
 INTO #FileFormatMetadata
FROM (SELECT name [Column Name]
 ,CASE WHEN charindex('(',system_type_name) = 0 THEN system_type_name
  ELSE left(system_type_name, charindex('(',system_type_name) - 1)
  END [SQL DT]
 ,max_length
FROM sys.dm_exec_describe_first_result_set(@SrcQuery, NULL, 1) where [name] is not NULL  and is_hidden = 0) t

SELECT * FROM #FileFormatMetadata;

If you’ll recall from our Excel adventure, there are two more primary attributes we need to derive, Delimiter and Biml Data Type. In order to determine the Delimiter value, we need to know which column is last in the feed. That’s not difficult because “column_ordinal” is included in sys.dm_exec_describe_first_result_set output. The Biml Data Type was derived via a lookup table in the spreadsheet. In this example, we’ll use a Case When statement instead. The new query and output are below.

IF object_id('tempdb..#FileFormatMetadata') IS NOT NULL DROP TABLE #FileFormatMetadata;
SELECT 
 t.[Column Name]
,t.[SQL DT]
,CASE 
WHEN t.[SQL DT] in ('char', 'varchar','text') THEN (t.max_length)
WHEN t.[SQL DT] in ('nchar', 'nvarchar','ntext') THEN (t.max_length/2)
ELSE 0 END [Length]
,CASE 
WHEN t.[SQL DT] = 'smallint' THEN 'Int16'
WHEN t.[SQL DT] = 'tinyint' THEN 'Byte'
WHEN t.[SQL DT] = 'int' THEN 'Int32'
WHEN t.[SQL DT] = 'bigint' THEN 'Int64'
WHEN t.[SQL DT] = 'char' THEN 'AnsiStringFixedLength'
WHEN t.[SQL DT] = 'varchar' THEN 'AnsiString'
WHEN t.[SQL DT] = 'nchar' THEN 'StringFixedLength'
WHEN t.[SQL DT] = 'nvarchar' THEN 'String'
WHEN t.[SQL DT] = 'date' THEN 'Date'
WHEN t.[SQL DT] = 'datetime' THEN 'DateTime'
WHEN t.[SQL DT] = 'datetime2' THEN 'DateTime2'
WHEN t.[SQL DT] = 'datetimeoffset' THEN 'DateTimeOffset'
WHEN t.[SQL DT] = 'smalldatetime' THEN 'DateTime'
WHEN t.[SQL DT] = 'time' THEN 'Time'
WHEN t.[SQL DT] = 'decimal' THEN 'Decimal'
WHEN t.[SQL DT] = 'numeric' THEN 'Decimal'
WHEN t.[SQL DT] = 'bit' THEN 'Boolean'
WHEN t.[SQL DT] = 'float' THEN 'Double'
WHEN t.[SQL DT] = 'real' THEN 'Single'
WHEN t.[SQL DT] = 'money' THEN 'Currency'
WHEN t.[SQL DT] = 'smallmoney' THEN 'Currency'
WHEN t.[SQL DT] = 'text' THEN 'AnsiString'
WHEN t.[SQL DT] = 'ntext' THEN 'String'
ELSE NULL END [Biml Data Type]
,t.ColumnOrdinal
 INTO #FileFormatMetadata
FROM (SELECT name [Column Name]
 ,CASE WHEN charindex('(',system_type_name) = 0 THEN system_type_name
  ELSE left(system_type_name, charindex('(',system_type_name) - 1)
  END [SQL DT]
 ,max_length
 ,column_ordinal ColumnOrdinal
FROM sys.dm_exec_describe_first_result_set(@SrcQuery, NULL, 1) where [name] is not NULL  and is_hidden = 0) t

SELECT * FROM #FileFormatMetadata;
Column NameSQL DTLengthBiml Data TypeColumnOrdinal
OrderIDint0Int321
CustomerIDnchar5StringFixedLength2
EmployeeIDint0Int323
OrderDatedatetime0DateTime4
RequiredDatedatetime0DateTime5
ShippedDatedatetime0DateTime6
ShipViaint0Int327
Freightmoney0Currency8
ShipNamenvarchar40String9
ShipAddressnvarchar60String10
ShipCitynvarchar15String11
ShipRegionnvarchar15String12
ShipPostalCodenvarchar10String13
ShipCountrynvarchar15String14

Remember that Biml is simply XML, and the FOR XML Clause is a clean way to generate it. So, let’s start with the Connections node, which is simple because the only variable needed is @FeedName. Our query only returns a single record which is element FlatFileConnection, who’s parent (root) is Connections.

SELECT 'conn_ff_' + @FeedName "@Name"
	,'C:\FileExport\' + @FeedName + '.csv' "@FilePath"
	,'fff_' + @FeedName "@FileFormat"
    FOR XML PATH('FlatFileConnection'), type, root('Connections')
<Connections>
  <FlatFileConnection Name="conn_ff_Orders" FilePath="C:\FileExport\Orders.csv" FileFormat="fff_Orders" />
</Connections>

The Packages code is slightly more complex than Connections, as it involves several levels in the XML tree. This first query simply returns the attributes at the Package level:

SELECT 'Export ' + @FeedName "@Name"
	  ,'Linear' "@ConstraintMode"
	  ,'DontSaveSensitive' "@ProtectionLevel"
	FOR XML PATH('Package'), TYPE,ROOT('Packages')
<Packages>
  <Package Name="Export Orders" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive" />
</Packages>

Inside the package element we have to generate the Tasks and Transformations. The root of this query is Tasks. Notice the aliases on the element names reflect their tree levels because Tasks include one or more Transformations. The base query for the Tasks is:

SELECT	'DFT Load File' "@Name"
		,'OLE_SRC ' + @FeedName "Transformations/OleDbSource/@Name"
		,'connNorthwind' "Transformations/OleDbSource/@ConnectionName"
		,@SrcQuery "Transformations/OleDbSource/DirectInput"
		,'FF_DEST ' + @FeedName "Transformations/FlatFileDestination/@Name"
		,'conn_ff_' + @FeedName "Transformations/FlatFileDestination/@ConnectionName"
		,'true'  "Transformations/FlatFileDestination/@Overwrite"
		,'false'  "Transformations/FlatFileDestination/@ValidateExternalMetadata"
		FOR XML PATH('Dataflow'),TYPE, root('Tasks')
<Tasks>
  <Dataflow Name="DFT Load File">
    <Transformations>
      <OleDbSource Name="OLE_SRC Orders" ConnectionName="connNorthwind">
        <DirectInput>SELECT * FROM dbo.Orders</DirectInput>
      </OleDbSource>
      <FlatFileDestination Name="FF_DEST Orders" ConnectionName="conn_ff_Orders" Overwrite="true" ValidateExternalMetadata="false" />
    </Transformations>
  </Dataflow>
</Tasks>

I could have avoided the lengthy element aliases by creating more sub-queries, but those get more difficult to read as well. Since Tasks are a sub-element of each Package, their query becomes a sub-query of Packages like this:

SELECT 'Export ' + @FeedName "@Name"
	  ,'Linear' "@ConstraintMode"
	  ,'DontSaveSensitive' "@ProtectionLevel"
		,(SELECT	
		'DFT Load File' "@Name"
		,'OLE_SRC ' + @FeedName "Transformations/OleDbSource/@Name"
		,'connNorthwind' "Transformations/OleDbSource/@ConnectionName"
		,@SrcQuery "Transformations/OleDbSource/DirectInput"
		,'FF_DEST ' + @FeedName "Transformations/FlatFileDestination/@Name"
		,'conn_ff_' + @FeedName "Transformations/FlatFileDestination/@ConnectionName"
		,'true'  "Transformations/FlatFileDestination/@Overwrite"
		,'false'  "Transformations/FlatFileDestination/@ValidateExternalMetadata"
		FOR XML PATH('Dataflow'),TYPE, root('Tasks'))
	FOR XML PATH('Package'), TYPE,ROOT('Packages')
<Packages>
  <Package Name="Export Orders" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
    <Tasks>
      <Dataflow Name="DFT Load File">
        <Transformations>
          <OleDbSource Name="OLE_SRC Orders" ConnectionName="connNorthwind">
            <DirectInput>SELECT * FROM dbo.Orders</DirectInput>
          </OleDbSource>
          <FlatFileDestination Name="FF_DEST Orders" ConnectionName="conn_ff_Orders" Overwrite="true" ValidateExternalMetadata="false" />
        </Transformations>
      </Dataflow>
    </Tasks>
  </Package>
</Packages>

Now let’s tackle the most difficult section of our code, the File Format and its columns. This time, let’s start with our lowest level, columns. Notice how I derive the Delimiter from the ColumnOrdinal column:

SELECT [Column Name] "@Name"
		,[Biml Data Type] "@DataType"
		,Length "@Length"
		,CASE WHEN ColumnOrdinal = (SELECT MAX(ColumnOrdinal) FROM #FileFormatMetadata) THEN 'CRLF'
		ELSE 'Comma' END "@Delimiter" 
		from #FileFormatMetadata
		FOR XML PATH('Column'), type, root('Columns')
<Columns>
  <Column Name="OrderID" DataType="Int32" Length="0" Delimiter="Comma" />
  <Column Name="CustomerID" DataType="StringFixedLength" Length="5" Delimiter="Comma" />
  <Column Name="EmployeeID" DataType="Int32" Length="0" Delimiter="Comma" />
  <Column Name="OrderDate" DataType="DateTime" Length="0" Delimiter="Comma" />
  <Column Name="RequiredDate" DataType="DateTime" Length="0" Delimiter="Comma" />
  <Column Name="ShippedDate" DataType="DateTime" Length="0" Delimiter="Comma" />
  <Column Name="ShipVia" DataType="Int32" Length="0" Delimiter="Comma" />
  <Column Name="Freight" DataType="Currency" Length="0" Delimiter="Comma" />
  <Column Name="ShipName" DataType="String" Length="40" Delimiter="Comma" />
  <Column Name="ShipAddress" DataType="String" Length="60" Delimiter="Comma" />
  <Column Name="ShipCity" DataType="String" Length="15" Delimiter="Comma" />
  <Column Name="ShipRegion" DataType="String" Length="15" Delimiter="Comma" />
  <Column Name="ShipPostalCode" DataType="String" Length="10" Delimiter="Comma" />
  <Column Name="ShipCountry" DataType="String" Length="15" Delimiter="CRLF" />
</Columns>

The File Format is easy. However, I couldn’t find a way to generate XML with T-SQL and keep the TextQualifier &quot; value so I substituted the hex value for double quotes instead.

SELECT 'fff_' + @FeedName "@Name"
	, 'Delimited' "@FlatFileType"
	,'true' "@ColumnNamesInFirstDataRow"
	,'_x0022_' "@TextQualifier"
	FOR XML PATH('FlatFileFormat'), type, root('FileFormats')
<FileFormats>
  <FlatFileFormat Name="fff_Orders" FlatFileType="Delimited" ColumnNamesInFirstDataRow="true" TextQualifier="_x0022_" />
</FileFormats>

And of course Columns needs to be nested inside of FlatFileFormat, so we use a subquery:

SELECT 'fff_' + @FeedName "@Name"
	, 'Delimited' "@FlatFileType"
	,'true' "@ColumnNamesInFirstDataRow"
	,'_x0022_' "@TextQualifier"
		,(SELECT [Column Name] "@Name"
		,[Biml Data Type] "@DataType"
		,Length "@Length"
		,CASE WHEN ColumnOrdinal = (SELECT MAX(ColumnOrdinal) FROM #FileFormatMetadata) THEN 'CRLF'
		ELSE 'Comma' END "@Delimiter" 
		from #FileFormatMetadata
		FOR XML PATH('Column'), type, root('Columns'))
	FOR XML PATH('FlatFileFormat'), type, root('FileFormats')
<FileFormats>
  <FlatFileFormat Name="fff_Orders" FlatFileType="Delimited" ColumnNamesInFirstDataRow="true" TextQualifier="_x0022_">
    <Columns>
      <Column Name="OrderID" DataType="Int32" Length="0" Delimiter="Comma" />
      <Column Name="CustomerID" DataType="StringFixedLength" Length="5" Delimiter="Comma" />
      <Column Name="EmployeeID" DataType="Int32" Length="0" Delimiter="Comma" />
      <Column Name="OrderDate" DataType="DateTime" Length="0" Delimiter="Comma" />
      <Column Name="RequiredDate" DataType="DateTime" Length="0" Delimiter="Comma" />
      <Column Name="ShippedDate" DataType="DateTime" Length="0" Delimiter="Comma" />
      <Column Name="ShipVia" DataType="Int32" Length="0" Delimiter="Comma" />
      <Column Name="Freight" DataType="Currency" Length="0" Delimiter="Comma" />
      <Column Name="ShipName" DataType="String" Length="40" Delimiter="Comma" />
      <Column Name="ShipAddress" DataType="String" Length="60" Delimiter="Comma" />
      <Column Name="ShipCity" DataType="String" Length="15" Delimiter="Comma" />
      <Column Name="ShipRegion" DataType="String" Length="15" Delimiter="Comma" />
      <Column Name="ShipPostalCode" DataType="String" Length="10" Delimiter="Comma" />
      <Column Name="ShipCountry" DataType="String" Length="15" Delimiter="CRLF" />
    </Columns>
  </FlatFileFormat>
</FileFormats>

So now we have three separate pieces of XML code Connections, Packages, and FileFormats. To combine them, we make each section a sub-query of our root element, which is Biml.

   SELECT
   (SELECT 'conn_ff_' + @FeedName "@Name"
	,'C:\FileExport\' + @FeedName + '.csv' "@FilePath"
	,'fff_' + @FeedName "@FileFormat"
    FOR XML PATH('FlatFileConnection'), type, root('Connections'))
	,(SELECT 'Export ' + @FeedName "@Name"
	, 'Linear' "@ConstraintMode"
	,'DontSaveSensitive' "@ProtectionLevel"
		,(SELECT	
		'DFT Load File' "@Name"
		,'OLE_SRC ' + @FeedName "Transformations/OleDbSource/@Name"
		,'connNorthwind' "Transformations/OleDbSource/@ConnectionName"
		,@SrcQuery "Transformations/OleDbSource/DirectInput"
		,'FF_DEST ' + @FeedName "Transformations/FlatFileDestination/@Name"
		,'conn_ff_ ' + @FeedName "Transformations/FlatFileDestination/@ConnectionName"
		,'true'  "Transformations/FlatFileDestination/@Overwrite"
		,'false'  "Transformations/FlatFileDestination/@ValidateExternalMetadata"
		FOR XML PATH('Dataflow'),TYPE, root('Tasks'))
	FOR XML PATH('Package'), TYPE,ROOT('Packages'))
	,(SELECT 'fff_' + @FeedName "@Name"
	, 'Delimited' "@FlatFileType"
	,'true' "@ColumnNamesInFirstDataRow"
	,'_x0022_' "@TextQualifier"
		,(SELECT [Column Name] "@Name"
		,[Biml Data Type] "@DataType"
		,Length "@Length"
		,CASE WHEN ColumnOrdinal = (SELECT MAX(ColumnOrdinal) FROM #FileFormatMetadata) THEN 'CRLF'
		ELSE 'Comma' END "@Delimiter" 
		from #FileFormatMetadata
		FOR XML PATH('Column'), type, root('Columns'))
	FOR XML PATH('FlatFileFormat'), type, root('FileFormats'))
FOR XML PATH ('Biml')

And this creates what we need except for one little thing. Notice that the opening line is missing the Biml namespace?

<Biml>
  <Connections>
    <FlatFileConnection Name="conn_ff_Orders" FilePath="C:\FileExport\Orders.csv" FileFormat="fff_Orders" />
  </Connections>

T-SQL natively supports namespaces by using the WITH XMLNAMESPACES clause, but there is a problem with that. If we use that method, the Biml namespace will be repeated for each sub-query, which is technically correct but really ugly.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections xmlns="http://schemas.varigence.com/biml.xsd">
    <FlatFileConnection Name="conn_ff_Orders" FilePath="C:\FileExport\Orders.csv" FileFormat="fff_Orders" />
  </Connections>
  <Packages xmlns="http://schemas.varigence.com/biml.xsd">
    <Package Name="Export Orders" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">

According to Microsoft, the code is functioning as designed and they have no plans to fix it. I’ve explored several ways to “hack” this, and the simplest is to take our query result, cast it as NVARCHAR, perform a replace, and then cast it back into a XML data type. So, our final query looks like this:

USE Northwind
GO

DECLARE @SrcQuery NVARCHAR(4000)
,@FeedName NVARCHAR(25)
;
SELECT @SrcQuery = 'SELECT * FROM dbo.Orders', @FeedName = 'Orders';
--SET @SrcQuery = 'SELECT * FROM dbo.Customers';


--EXECUTE sp_executesql @SrcQuery;
IF object_id('tempdb..#FileFormatMetadata') IS NOT NULL DROP TABLE #FileFormatMetadata;

SELECT 
 t.[Column Name]
,t.[SQL DT]
,CASE 
WHEN t.[SQL DT] in ('char', 'varchar','text') THEN (t.max_length)
WHEN t.[SQL DT] in ('nchar', 'nvarchar','ntext') THEN (t.max_length/2)
ELSE 0 END [Length]
,CASE 
WHEN t.[SQL DT] = 'smallint' THEN 'Int16'
WHEN t.[SQL DT] = 'tinyint' THEN 'Byte'
WHEN t.[SQL DT] = 'int' THEN 'Int32'
WHEN t.[SQL DT] = 'bigint' THEN 'Int64'
WHEN t.[SQL DT] = 'char' THEN 'AnsiStringFixedLength'
WHEN t.[SQL DT] = 'varchar' THEN 'AnsiString'
WHEN t.[SQL DT] = 'nchar' THEN 'StringFixedLength'
WHEN t.[SQL DT] = 'nvarchar' THEN 'String'
WHEN t.[SQL DT] = 'date' THEN 'Date'
WHEN t.[SQL DT] = 'datetime' THEN 'DateTime'
WHEN t.[SQL DT] = 'datetime2' THEN 'DateTime2'
WHEN t.[SQL DT] = 'datetimeoffset' THEN 'DateTimeOffset'
WHEN t.[SQL DT] = 'smalldatetime' THEN 'DateTime'
WHEN t.[SQL DT] = 'time' THEN 'Time'
WHEN t.[SQL DT] = 'decimal' THEN 'Decimal'
WHEN t.[SQL DT] = 'numeric' THEN 'Decimal'
WHEN t.[SQL DT] = 'bit' THEN 'Boolean'
WHEN t.[SQL DT] = 'float' THEN 'Double'
WHEN t.[SQL DT] = 'real' THEN 'Single'
WHEN t.[SQL DT] = 'money' THEN 'Currency'
WHEN t.[SQL DT] = 'smallmoney' THEN 'Currency'
WHEN t.[SQL DT] = 'text' THEN 'AnsiString'
WHEN t.[SQL DT] = 'ntext' THEN 'String'
ELSE NULL END [Biml Data Type]
,t.ColumnOrdinal
,@FeedName FeedName
INTO #FileFormatMetadata 
FROM (SELECT name [Column Name]
 ,CASE WHEN charindex('(',system_type_name) = 0 THEN system_type_name
  ELSE left(system_type_name, charindex('(',system_type_name) - 1)
  END [SQL DT]
 ,max_length
 ,column_ordinal ColumnOrdinal
   FROM sys.dm_exec_describe_first_result_set(@SrcQuery, NULL, 1) where [name] is not NULL  and is_hidden = 0) t

SELECT CAST(REPLACE(CAST( (
   SELECT
   (SELECT 'conn_ff_' + @FeedName "@Name"
	,'C:\FileExport\' + @FeedName + '.csv' "@FilePath"
	,'fff_' + @FeedName "@FileFormat"
    FOR XML PATH('FlatFileConnection'), type, root('Connections'))
	,(SELECT 'Export ' + @FeedName "@Name"
	, 'Linear' "@ConstraintMode"
	,'DontSaveSensitive' "@ProtectionLevel"
		,(SELECT	
		'DFT Load File' "@Name"
		,'OLE_SRC ' + @FeedName "Transformations/OleDbSource/@Name"
		,'connNorthwind' "Transformations/OleDbSource/@ConnectionName"
		,@SrcQuery "Transformations/OleDbSource/DirectInput"
		,'FF_DEST ' + @FeedName "Transformations/FlatFileDestination/@Name"
		,'conn_ff_ ' + @FeedName "Transformations/FlatFileDestination/@ConnectionName"
		,'true'  "Transformations/FlatFileDestination/@Overwrite"
		,'false'  "Transformations/FlatFileDestination/@ValidateExternalMetadata"
		FOR XML PATH('Dataflow'),TYPE, root('Tasks'))
	FOR XML PATH('Package'), TYPE,ROOT('Packages'))
	,(SELECT 'fff_' + @FeedName "@Name"
	, 'Delimited' "@FlatFileType"
	,'true' "@ColumnNamesInFirstDataRow"
	,'_x0022_' "@TextQualifier"
		,(SELECT [Column Name] "@Name"
		,[Biml Data Type] "@DataType"
		,Length "@Length"
		,CASE WHEN ColumnOrdinal = (SELECT MAX(ColumnOrdinal) FROM #FileFormatMetadata) THEN 'CRLF'
		ELSE 'Comma' END "@Delimiter" 
		from #FileFormatMetadata
		FOR XML PATH('Column'), type, root('Columns'))
	FOR XML PATH('FlatFileFormat'), type, root('FileFormats'))
FOR XML PATH ('Biml')
) AS NVARCHAR(MAX)), '<Biml>', '<Biml xmlns="http://schemas.varigence.com/biml.xsd">') AS XML)

And this finally produces the Biml we want.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <FlatFileConnection Name="conn_ff_Orders" FilePath="C:\FileExport\Orders.csv" FileFormat="fff_Orders" />
  </Connections>
  <Packages>
    <Package Name="Export Orders" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive">
      <Tasks>
        <Dataflow Name="DFT Load File">
          <Transformations>
            <OleDbSource Name="OLE_SRC Orders" ConnectionName="connNorthwind">
              <DirectInput>SELECT * FROM dbo.Orders</DirectInput>
            </OleDbSource>
            <FlatFileDestination Name="FF_DEST Orders" ConnectionName="conn_ff_ Orders" Overwrite="true" ValidateExternalMetadata="false" />
          </Transformations>
        </Dataflow>
      </Tasks>
    </Package>
  </Packages>
  <FileFormats>
    <FlatFileFormat Name="fff_Orders" FlatFileType="Delimited" ColumnNamesInFirstDataRow="true" TextQualifier="_x0022_">
      <Columns>
        <Column Name="OrderID" DataType="Int32" Length="0" Delimiter="Comma" />
        <Column Name="CustomerID" DataType="StringFixedLength" Length="5" Delimiter="Comma" />
        <Column Name="EmployeeID" DataType="Int32" Length="0" Delimiter="Comma" />
        <Column Name="OrderDate" DataType="DateTime" Length="0" Delimiter="Comma" />
        <Column Name="RequiredDate" DataType="DateTime" Length="0" Delimiter="Comma" />
        <Column Name="ShippedDate" DataType="DateTime" Length="0" Delimiter="Comma" />
        <Column Name="ShipVia" DataType="Int32" Length="0" Delimiter="Comma" />
        <Column Name="Freight" DataType="Currency" Length="0" Delimiter="Comma" />
        <Column Name="ShipName" DataType="String" Length="40" Delimiter="Comma" />
        <Column Name="ShipAddress" DataType="String" Length="60" Delimiter="Comma" />
        <Column Name="ShipCity" DataType="String" Length="15" Delimiter="Comma" />
        <Column Name="ShipRegion" DataType="String" Length="15" Delimiter="Comma" />
        <Column Name="ShipPostalCode" DataType="String" Length="10" Delimiter="Comma" />
        <Column Name="ShipCountry" DataType="String" Length="15" Delimiter="CRLF" />
      </Columns>
    </FlatFileFormat>
  </FileFormats>
</Biml>

Up until this point, we’ve been Scripting Biml using shortcuts. In the next article, we’ll finally start using C# to create BimlScript. Stay Tuned!

The T-SQL file used for this post can be downloaded here.

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>