Skip to content

SSIS 2008 Flat File Source Issue

December 23, 2009

Ok, this might be my last post for this year, 2009. This is more of a note to myself than a blog post. Anyway, I want to document the issue that I have encountered and the solution that I have found through the help of  Todd McDermid (Blog | Twitter) and Andy Leonard (Blog | Twitter), so that it might help those who might be in the same situation.

A portion of a DTS package that I migrated to SSIS 2008 has some discrepancy. I was not getting the exact count of rows from my Flat File Source (Tab Delimited Text File in this case). I checked the DTS, and the Row Count pumped into the SQL Server 2000 matched that of the text file source. The Row Count pumped into the SQL Server 2008 from SSIS 2008 is short of about 10%. I could have fixed this by “correcting” the source text file but that wouldn’t work. I was tasked to bring solution to the current process. The process itself is to remain intact.

What was the issue?

Some records from the source text file do not have all the required columns. In DTS, this is fine. DTS knows how to deal with that by assumption. DTS can simply handle it. It is not the case with SSIS 2008 (and SSIS 2005, I guess). What is going on in SSIS 2008 in the situation just described is, and I am quoting Todd McDermid in this SSIS Forum (similar case was also documented in this Forum, also see this for other links to blogs discussing the same issue):

The Flat File Source in SSIS does not respect the “end-of-line” delimiter to indicate that the line is complete.  It reads in data until it sees a column delimiter, then proceeds to fill the next column.  It will continue until it has read in enough data (and column delimiters) to fill all of the columns of a row – regardless of whether it encountered an end-of-line delimiter in the data.  Once it has filled all of the columns that it expects, I think it then searches for the end-of-line.

That is exactly what was happening in my case. I tried to isolate the rows that did not make to the target table and I could see “combined columns” in a field. SSIS won’t give you errror message on this. Everything will run successfully. That’s makes it harder to investigate the issue.  Thank God for Twitter.

What is the Solution?

Simply put, I needed a better Data Flow Source Component. I cannot run the same old DTS from within SSIS. That would only defeat the purpose of migrating the old system (DTS & SQL Server 2000) into the new one (SSIS & SQL Server 2008).

First, I tried Delimited File Reader Source Component from Codeplex. But that did not solved the issue. It was throwing some “Row Overflow” issue and SSIS is failing the source component.

The second component that I tried worked. SSIS Delimited File Source Component (SSIS DFS 2008), also from Codeplex, “solved” my problem. You can find a documentation of DFS here.

kick it on DotNetKicks.com

About these ads
2 Comments leave one →
  1. May 28, 2010 3:03 AM

    To get the workaround and Step by Step description for developing SSIS package in order to overcome the issue with SSIS while importing text files with Flat File Connection Manager and Flat File Source where the “Row Delimiter” property does not work properly for rows having NULL or empty values, follow the below link:

    http://www.sqllion.com/2010/04/ssis-vs-text-file-importing-1/

    Thanks,

Trackbacks

  1. Josef Richberg and Marlon Ribunal Syndicate at SQLServerPedia | Brent Ozar - Too Much Information

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Archives



  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video


  • Software Blogs - BlogCatalog Blog Directory
  • Follow

    Get every new post delivered to your Inbox.

    Join 29 other followers

    %d bloggers like this: