THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is AndyLeonard.blog.

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:

ImportPackage_0

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:

ImportPackage

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

ImportPackage_1

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

ImportPackage_2

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:

ImportPackage_3

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

ImportPackage_4

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

ImportPackage_5

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:

ImportPackage_6

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

ImportPackage_8

Now, the Code

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

ImportPackage_9

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

ImportPackage_10

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

ImportPackage_11

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

ImportPackage_12

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

ImportPackage_15

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

ImportPackage_13

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
SSIS Design Patterns

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

Published Wednesday, July 6, 2016 1:19 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Thomas Meltofte Eriksen said:

If you are using Mist, how about just executing a Transfomer:

<#@ target type="FlatFileColumn" mergemode="LocalMerge" #>

<Column Name="<#= TargetNode.Name.Replace(" ","") #>" />

Thomas

July 7, 2016 5:51 AM
 

andyleonard said:

Hi Thomas,

  Yep, that'll also work - and it's just a couple lines of code!

:{>

July 7, 2016 7:41 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement