SSIS Flat File Import Error

Hello,

I am having an issue importing a flat file into an SSIS package. The file I am importing has a footer and header that have fewer fields than the rest of the file, I set flat file connection to skip the header row. The file is usually pretty small, just a few records, and it usually runs just fine, but the file that came in the other day was a bit bigger, 17 records or so, and it ended up failing.

I am using SQL Server 2005, and Visual Studio 2005.

Here are the errors messages I get:

[ACT_RLP input file [116792]] Warning: There is a partial row at the end of the file.

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "ACT_RLP input file" (116792) returned error code 0x80040E21.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

Error Code DTS_E_THREADFAILED.  Thread "SourceThread0" has exited with error code 0xC0047038.  There may be error messages posted before this with more information on why the thread has exited.

SSIS Error Code DTS_E_THREADCANCELLED.  Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.  There may be error messages posted before this with more information on why the thread was cancelled.

SSIS Error Code DTS_E_THREADFAILED.  Thread "WorkThread0" has exited with error code 0xC0047039.  There may be error messages posted before this with more information on why the thread has exited.

SSIS Error Code DTS_E_THREADFAILED.  Thread "WorkThread0" has exited with error code 0xC0047039.  There may be error messages posted before this with more information on why the thread has exited.


None of that really means much to me, but I believe the problem is with the footer.

Here is what the footer looks like: "+|9|9999999|16|9|20130906|LOAD-DELTA : ALP              |LGNT |1|21|0|17|2"

It's pipe delimeted, I have found that the file will run successfully if I change the file like:

1. remove the footer.
2. add a pipe to the end of the footer.
3. add an empty footer line to the end of the file like "+|||||||||||||"

I have also found the file will load successfully if I change the 12th field, the "17" to a single digit, 0 - 9, so finding that out I looked into the flat file connection manager, and the 12th field, which was a status_code had a OutputColumnWidth of 1, I changed it to 2 and the file ran successfully.

So, I am just wondering if anyone has any suggestions how to solve this problem without changing the field length of one of my fields, or manipulating the actual file.

Thanks
OpusJonesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
afaik SSIS doesn't handle a flat file with multiple row formats, so I believe the only way you're going to be able to pull this off is to pump the entire file into one table / one column, then createa stored proc to strip away headers and footers and parse the remaining detail with the same row format into a table with those columns.  Then use a SQL Task to call the SP.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.