Archives

BimlScript Code Nuggets and Mad Libs

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.

Mad Libs?

Per Wikipedia, “Mad Libs is a phrasal template word game where one player prompts others for a list of words to substitute for blanks in a story, before reading the – often comical or nonsensical – story aloud. The game is frequently played as a party game or as a pastime.” I remember playing these games as a kid. Big Bang Theory even incorporated Mad Libs into one of their episodes.

Since each of these words in the list is saved, and then inserted into another string, it makes a good programming exercise. In fact, I remember doing this exercise in middle school back when acid washed jeans were in style. This was in the early days when most people hadn’t even used computers, much less own one, though I was lucky enough to have one at home. The school was fortunate enough to have a room of about 20 Apple IIe computers. Our math teacher was pretty progressive and let us spend an hour writing our own Mad Lib program in BASIC. The program would pop up questions to the user, save the input in variables, and then insert them into a bland, innocent paragraph of text. Well, being a class clown, I amused my classmates by inserting inappropriate terms into my program during testing. It got some cheap laughs, and then I got detention. Oh well.

BimlScript Mad Libs?

Well, if you’ve been following along with this series, you know that we are creating a basic BimlScript framework that exports delimited flat files. In two previous articles, I demonstrated how to create this Biml using Excel and T-SQL. To refresh your memory, this is the Biml that we need to generate:

<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>

The above Biml code is analogous to a Mad Lib “story.” First, create a Biml file from the above code. Second, determine which “questions” must be asked to automate our “story.” The first attribute we’ll need is the Feed Name, in this case it is “Orders”. The second question is what query is used for that feed. Add these variables to the top of your Biml.

<#
  string feedName = "Orders"; 
  string qry = "SELECT * FROM dbo.Orders";
#>

<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">

Now you are going to perform two Find and Replace (CTRL-H) operations on the Biml Code above for lines 3 through 22. Since the Flat File Format Columns section is more complicated, we’ll handle that in a bit. Notice that we are replacing the hard coded values with BimlScript text nuggets. The <#= > nugget simply displays the variable value as a string.

FindReplace
Orders<#=feedName#>
SELECT * FROM dbo.Orders<#=qry#>

Here are the results of our Find/Replace. Again, be careful not to perform the replace any of the column metadata.

<#
  string feedName = "Orders"; 
  string qry = "SELECT * FROM dbo.Orders";
#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FlatFileConnection Name="conn_ff_<#=feedName#>" FilePath="C:\FileExport\<#=feedName#>.csv" FileFormat="fff_<#=feedName#>"/>
    </Connections>
    <Packages>
        <Package Name="Export <#=feedName#>" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive" PackageSubpath="Export Flat Files">
            <Tasks>
                <Dataflow Name="DFT Load file" >
                    <Transformations>
                        <OleDbSource Name="OLE_SRC <#=feedName#>" ConnectionName="connNorthwind">
                            <DirectInput>
<#=qry#>
                            </DirectInput>
                        </OleDbSource>
                        <FlatFileDestination Name="FF_DEST <#=feedName#>"  ConnectionName="conn_ff_<#=feedName#>" Overwrite="true" ValidateExternalMetadata="false"/>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>
    <FileFormats>
        <FlatFileFormat Name="fff_<#=feedName#>" 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>

So, with a simple Replace exercise, we have prepared around 75% of our script to loop automatically. Now let’s handle the Flat File Format Columns.

GetQuerySchema

In the previous article, I documented how to Import Biml Metadata with GetQuerySchema. To simplify things, let’s work on the column section separately.

<#
  string feedName = "Orders"; 
  string qry = "SELECT * FROM dbo.Orders";
  
  var srcConn = SchemaManager.CreateConnectionNode("connNorthwind", @"Provider=SQLNCLI11;data source=localhost;integrated security=SSPI;initial catalog=Northwind;");
  var feedMd = ExternalDataAccess.GetQuerySchema(srcConn, qry,null,0);
#>
<#=feedMd.GetBiml()#>

Now let’s compare the two code sections. First, the Biml generated from the previous statement.

<Table Name="" SchemaName="">
	<Columns>
		<Column Name="OrderID" Precision="0" Scale="0" />
		<Column Name="CustomerID" DataType="StringFixedLength" Length="5" Precision="0" Scale="0" />
		<Column Name="EmployeeID" Precision="0" Scale="0" />
		<Column Name="OrderDate" DataType="DateTime" Precision="0" Scale="0" />
		<Column Name="RequiredDate" DataType="DateTime" Precision="0" Scale="0" />
		<Column Name="ShippedDate" DataType="DateTime" Precision="0" Scale="0" />
		<Column Name="ShipVia" Precision="0" Scale="0" />
		<Column Name="Freight" DataType="Currency" Precision="0" Scale="0" />
		<Column Name="ShipName" DataType="String" Length="40" Precision="0" Scale="0" />
		<Column Name="ShipAddress" DataType="String" Length="60" Precision="0" Scale="0" />
		<Column Name="ShipCity" DataType="String" Length="15" Precision="0" Scale="0" />
		<Column Name="ShipRegion" DataType="String" Length="15" Precision="0" Scale="0" />
		<Column Name="ShipPostalCode" DataType="String" Length="10" Precision="0" Scale="0" />
		<Column Name="ShipCountry" DataType="String" Length="15" Precision="0" Scale="0" />
	</Columns>
</Table>        

Next our hard coded sample from above.

                <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"/> 

Notice how similar they are? GetQuerySchema returns the Column Name, Data Type, and Length. We’re only responsible for the Delimiter attribute. We’ll have to loop through the columns of the Flat File Format. Let’s add a simple foreach loop and include a row of the hard coded column metadata that we need to generate.

<# foreach (var column in feedMd.Columns) {#>
                <Column Name="OrderID" DataType="Int32" Length="0" Delimiter="Comma"/>
			<# } #>	

Next go through the Mad Libs Find/Replace process again with the following attributes:

FindReplace
OrderID<#=column.Name#>
Int32<#=column.DataType#>
"0""<#=column.Length#>"

Now we only have to handle the Delimiter. Remember that the value for this is Comma for every column except the last which is CRLF. There are a couple of ways to handle this in C#.

 if (column == feedMd.Columns.Last()) { "CRLF" } else { "Comma" } 
// Or Shorthand
(column == feedMd.Columns.Last()) ? "CRLF"  : "Comma"

To make our BimlScript easier to read, we’ll use the shorthand.

<# foreach (var column in feedMd.Columns) {#>
				<Column Name="<#=column.Name#>" DataType="<#=column.DataType#>" Length="<#=column.Length#>" Delimiter="<#=(column == feedMd.Columns.Last()) ? "CRLF"  : "Comma"#>"/> 
			<# } #>	

And this produces the output we need.

<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"/> 

So, our final BimlScript looks like this:

<#
  string feedName = "Orders"; 
  string qry = "SELECT * FROM dbo.Orders";
  
  var srcConn = SchemaManager.CreateConnectionNode("connNorthwind", @"Provider=SQLNCLI11;data source=localhost;integrated security=SSPI;initial catalog=Northwind;");
  var feedMd = ExternalDataAccess.GetQuerySchema(srcConn, qry,null,0);
#>


<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <FlatFileConnection Name="conn_ff_<#=feedName#>" FilePath="C:\FileExport\<#=feedName#>.csv" FileFormat="fff_<#=feedName#>"/>
    </Connections>
    <Packages>
        <Package Name="Export <#=feedName#>" ConstraintMode="Linear" ProtectionLevel="DontSaveSensitive" PackageSubpath="Export Flat Files">
            <Tasks>
                <Dataflow Name="DFT Load file" >
                    <Transformations>
                        <OleDbSource Name="OLE_SRC <#=feedName#>" ConnectionName="connNorthwind">
                            <DirectInput>
<#=qry#>
                            </DirectInput>
                        </OleDbSource>
                        <FlatFileDestination Name="FF_DEST <#=feedName#>"  ConnectionName="conn_ff_<#=feedName#>" Overwrite="true" ValidateExternalMetadata="false"/>
                    </Transformations>
                </Dataflow>
            </Tasks>
        </Package>
    </Packages>	
    <FileFormats>
        <FlatFileFormat Name="fff_<#=feedName#>" FlatFileType="Delimited"  ColumnNamesInFirstDataRow="true" TextQualifier=""">
            <Columns>
<# foreach (var column in feedMd.Columns) {#>
				<Column Name="<#=column.Name#>" DataType="<#=column.DataType#>" Length="<#=column.Length#>" Delimiter="<#=(column == feedMd.Columns.Last()) ? "CRLF"  : "Comma"#>"/> 
			<# } #>	
            </Columns>
        </FlatFileFormat>
    </FileFormats>
</Biml>

So, now we’re using real BimlScript, but this still isn’t automated. To generate new feeds, we’d need to copy and paste the Biml file and change the variable values at the top. In order to fully automate this process we’d need an external metadata source that contains a list of Feed Names and Source Queries. In the next article, I’ll demonstrate how to import Biml metadata directly from an Excel spreadsheet, loop through each feed’s metadata, and generate a separate package for each.

The Biml files 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>