THE SQL Server Blog Spot on the Web

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

SSIS Design Pattern: Loading Variable-Length Rows

This blog has moved! You can find this content at the following new location:

Published Tuesday, May 18, 2010 8:00 AM 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



Todd McDermid said:

I've gotten quite a bit of feedback that the Delimited File Source on CodePlex is very good for this kind of thing, right out of the box.  (

May 18, 2010 9:47 AM

Siddharth Mehta said:

Hi Andy,

I think that this can be said as a trick and not a design pattern. In my views design pattern should be able to solve a design issue in a generic manner irrespective of technical implementation. This soln works for this dataset, but what would be the case if the first column was a primary key ? So I feel that it's a way to deal with this kind of issue, but can be classified as a design pattern. Just my opinion :)


May 18, 2010 11:02 AM

Kevin Stevens said:

It would be easier using Cobol.

May 19, 2010 12:30 PM

Chris said:

Nice article, do you have any other design patterns like these?

Could you please guide me?

July 7, 2011 6:30 AM

ssis said:

I am trying to do something similar , but I do not have any delimiters in my source do I handle the situation?

September 1, 2011 4:20 PM

Harshad said:

Awsome... Andy.

it helped me in a BIG way

September 12, 2011 2:40 PM

Harshad said:

my case is similar to that of "ssis", fixed length rows of different layouts.

I created multiple buffers, like Andy suggested and then in script component... used the substring (actually it is Mid()) to extract out the desired values.

September 12, 2011 2:42 PM

MtnJim said:

This post helped but i used a slightly different approach. The connection setup was the same but I used a conditional split based on your different cases. i.e. in the split derive the column value using substring(column,1,1) == "A", "B", "C". Name the different outputs accordingly and build your inserts based on this.

March 21, 2012 3:28 PM

baba said:

hi andy,

the script u are written in this solution is vb, can you guide me c#  code for the same

May 4, 2012 6:22 AM

David Tigue said:

Used this to solve another problem on StackOverflow. Of one to many on a single flat file. Thanks for the input. This was the only helpful reference I could find.

May 7, 2012 11:16 AM

Meera Khanna said:


Can it be done like, Lets say Output should be to only 2 different destination.

One will have a fixed count of column and the other will have all the rest of the records.

In such a case can you give me some idea how can it be acheived in similar way ??

September 11, 2012 6:30 AM

Drew Gillson said:

Andy, this was absolutely invaluable. Thank you so much for taking the time to post this.

September 14, 2012 4:02 PM

Carlos Gonzalez said:

Thank you for sharing.  Very helpful!

November 21, 2013 2:56 PM

Ramesh Iyer said:

Very helpful post.

I am using this along with what MtnJim suggested. Another advantage of using the script component within data flow for fixed width files is that it eliminates the need to manually enter each column name and length for the connection.

July 25, 2014 3:26 AM

Leave a Comment


This Blog



My Latest Book:

Community Awards

Friend of Red Gate

Contact Me


Privacy Statement