Use Biml to Remove Spaces from Flat File Column Names

I hate a space in a database column name.

Kent Bradshaw and I are updating our demo for loading National Provider Index (NPI) file data (We plan to present on this soon… more later…). The NPI files currently have 329 fields and almost every one of them contains spaces:

I had 5 minutes, so I decided to fix it with Biml. I first created an SSIS package and added a Flat File Connection Manager configured to consume the NPI data file. I opened Mist and clicked the Import tab. Then I clicked the Import Packages button on the ribbon:

When the Import Packages window displayed, I selected “DTSX From File System” option from the SSIS Asset Location dropdown:

The Import Packages window is reconfigured to support the import of an SSIS Package:

I click the “Select DTSX File Paths” link to open the “Browse for DTSX Files” dialog. I select the SSIS package I wish to import and click the Open button:

Once the SSIS package is selected, click the Import button:

Once the SSIS package has been imported, click the Add To Project button to add the imported SSIS package to the Mist project:

The Biml

Once hidden nugget of Biml in Mist is the Object Model. You can traverse the SSIS package via the RootNode. Actually, you can traverse and reference much more than just a single SSIS package, but that is for another post.

RootNode is your friend. What is RootNode? Reeves Smith provides an explanation in Stairway to Biml Level 5 – Biml Language Elements, part of the Stairway to Biml series at SQL Server Central.

There are a couple ways to approach removing spaces from the Flat File Connection Manager’s column names. I chose to build the Biml for the columns and paste it into the Flat File Connection Manager’s Biml.

The Biml file for the Flat File Connection Manager may be found on the Project View tab in Mist. Expand the project (MedicalDataBiml, in this case)—>addedBiml—>FileFormats to locate the Flat File Connection Manager’s Biml file:

Opening this file reveals the Biml representation of the Flat File Connection Manager’s format.

Now, the Code

I used the following Biml to remove spaces from the column names:

<#
AstFlatFileFormatNode ffformat = (AstFlatFileFormatNode)RootNode.FileFormats["FFCM Provider Data"];
foreach(var col in ffformat.Columns)
{#>
    <Column Name="<#=col.Name.Replace(" ", "") #>" DataType="<#=col.DataType #>" Length="<#=col.Length #>" MaximumWidth="<#=col.MaximumWidth #>" Delimiter="<#=col.Delimiter #>" />
<#}
#>

This Biml declares an AstFlatFileFormatNode named ffformat and sets it to the FileFormat named “FFCM Provider Data” found the RootNode’s FileFormats collection. I next loop through each column in the ffformat AstFlatFileFormatNode object. I use Biml to generate the <Column> object, replacing the spaces with an empty string for the Name attribute. The results in Mist appear as shown below:

I copied these 329 results from the Preview Expanded BimlScript box and pasted it back into the FFCM Provider Data.biml file:

I saved the FFCM Provider Data.biml and built the Package:

Now, the Flat File Connection Manager’s column names contain no spaces:

The only caveat was the Unicode checkbox was checked when I reopened the Flat File Connection Manager Editor:

Unchecking the checkbox was a small price to pay. Ok, make that 5 minutes and 10 seconds.

:{>

Learn more!

Related Training:
IESSIS2: Immersion Event on Advanced SQL Server Integration Services
Biml Academy

Related Articles:
Stairway to Integration Services
Stairway to Biml

Related Books

Help!
Enterprise Data & Analytics – We are here to help.