THE SQL Server Blog Spot on the Web

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

Jamie Thomson

This is the blog of Jamie Thomson, a freelance data mangler in London

Parsing flat files using SSIS : SSIS Nugget

Often when using SQL Server Integration Services (SSIS) you will find there is more than one way of accomplishing a task and that the most obvious method of doing so might not be the optimal one. In the video below I demonstrate this by way of an experiment using SSIS’s Flat File Source component; I show different ways that you can pull data from a flat file into the SSIS dataflow and also how the nature of the data itself can influence your choice as to how this task should be accomplished.

If you are having trouble viewing the video in your blog reader then head to http://sqlblog.com/blogs/jamie_thomson/archive/2010/03/25/parsing-flat-files-using-ssis-ssis-nugget.aspx to see it as it is hosted on my blog! 

The main point I want to get across from this video is that a little bit of creative thinking when building your dataflows can sometimes be very beneficial for performance; quite often building a solution that isn’t the most obvious might actually turn out to be the best one.

You’ll notice, if you have watched the video, that my editing skills weren’t quite up to snuff and I cut off the final few words however all I was saying was that if you have any feedback on this video then I would love to hear it either via email or preferably the comments section below. I hope this turns out to be useful to some of you.

@Jamiet

P.S. Incidentally the parsing that we do using SSIS expressions in the video would be much easier if we had a TOKENISE function in SSIS’s expression language and I have asked for the introduction of such a function on Connect at [SSIS] TOKEN(string, tokeniser_string, occurence) function. Feel free to go and vote that up if you think this feature would be useful!

Published Thursday, March 25, 2010 1:11 AM by jamiet

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

 

SSIS Junkie : Parsing flat files using SSIS : SSIS Nugget said:

March 24, 2010 7:19 PM
 

Tasman120 said:

Very good video Jamie.  Thank you for taking the time to thoroughly explain each DFT.

March 25, 2010 9:13 AM
 

MarkVSQL said:

Great food for thought. Thanks much.

April 14, 2010 2:17 PM
 

subrat.etc said:

Hi Jamie,

I need an urgent help regarding processing flat files in SSIS.

My package does the below steps:

1. A folder has many .txt files.

2. My SSIS package has a for each loop container which reads the file one by one and puts them into a table.

3. The flat file is pipe delimited and text qualified by double quotes.

When i run the package, first 5 files get processed successfully. On the 6th file onwards, all files are failing (i have configured the for each loop to continue looping even after a task failes). The error message is - The column delimiter for the column 'STRING_ID' was not found. STRING_ID is the last column of the file.

Strange, that when i process that particular failed file, through a separate ssis package with only this file as the source and same table destination, it gets processed with success.

This shows it is not a data problem.

I am really unable to find out what is wrong here. Is there any problem due to which 6th file onwards all are failing with same error.

Please help

Subrat

June 9, 2010 1:50 PM
 

jbooker said:

would be neat to see the same sort of comparison for SSIS expression parsing using substring() vs. using new token() expression vs. a script compentent using your old tokenise() function.

http://consultingblogs.emc.com/jamiethomson/archive/2006/07/14/SSIS-Nugget_3A00_-Extracting-data-from-unstructured-files.aspx

October 27, 2012 10:45 AM
 

kiran said:

subrat i am facing same problem did u find solution

November 26, 2012 3:57 AM
 

gby said:

Unfortunately time differences were really insignificant, thanks for the video though.

June 1, 2013 10:52 AM
 

Dinah Suitor said:

Thanks, Jamie!  Nice demo to show that with SSIS there is no "right way" to do things, and that "it depends" often applies.

re: gby's comment - yes, the time savings was minimal in this demo example, but you missed Jamie's point.  Jamie is trying to show that you need to consider the bigger picture, think outside the default SSIS box, and test the efficiency of the components as a whole.

March 19, 2014 12:36 PM
 

Abhi said:

IS there a way to add a line feed charecter to end of the LAST row in a flat file destination.Please help me guys.

DELIMETER SHOULD ONLY BE AT THE END OF THE LAST ROW IN FALT FILE DESTINATION.

April 25, 2014 12:38 PM
 

Shringa said:

Is there any solution to the issue explained by subhrat ?

I am facing same issue.

August 11, 2014 9:20 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement