Hi all,

We have many SSIS packages built in Visual Studio 2008 and previously deployed to SQL 2008 (SSIS 10) and then migrated to SQL 2012 (SSIS 11).

Most packages work OK but a couple hang and never complete.  These import text files with a footer which has a different number of columns to the data rows.  We have found that SSIS 10 allowed this OK and the footer row was filtered out once in SSIS.

Every bit of documentation states that in SSIS 11 (SQL 2012) the flat file import has been improved but as far as we can see it means that some of our imports do not work.  The data looks like this:

10;  234234;SMITH;2015-01-07;  3123123123;VE65          
10;  234234;SMITH;2015-01-07;  3123123123;VE65
10;  234234;SMITH;2015-01-07;  3123123123;VE65
99;000277;000000                                                                                                    

You can see the footer has a different number of columns.  In SSIS 11 (SQL 2012) it starts to process the file and never completes, the SSIS package runs continuously and does not complete, I have to stop it.

I can find no other references to this on the internet, I really do not want to pre-process the file or use a custom script as a data source for the import because there should be a way to handle this - it must surely be a bug if it hangs?

Is there a update I need to do to fix this?

Regards,

Gary.

Hi Gary,

It does not appear being a bug. You probably already had a means of processing the footer but you do not tell... actually I think you are able to process the files, but when the execution comes to the footer row it generates NULLs that cannot be accepted by the destination so you get an error you do not see as it comes out of a package in SSIS catalog and in turn because you cannot dismiss the error you get an impression the package got hung.

To recover I suggest you add the Conditional Split Component that will infilter the footer. Example:

http://www.timmitchell.net/post/2015/04/13/handling-mixed-format-data-files-in-ssis/

Hi Arthur,

Thanks for your reply - I have done some further testing to provide more information.

My file is delimited but also space padded - it looks almost like a fixed width file apart from it has delimiters.  This means the footer row is space padded to the full length of the main data rows, i.e. there are a lot of spaces (over 1,500) after the last character of the footer.

The package had error and truncation (in the Error Output section of Flat File source editor) redirected to an error table for analysis - when you remove these (change error and truncation to "Fail component") it fails with a truncation error, giving the footer row number and 3rd column.  The 3rd column is the last column in the footer - I believe this issue occurs due to the space padding, i.e. it cannot import column 3.

I hope this makes sense - SSIS 2012 is attempting to import the last column of the footer into a column which cannot handle the large number of spaces which it considers to be part of the field since there is no delimiter - when the truncation output is redirected to a table, it hangs reading the file.  When I say hangs, I can see in Resource Monitor Disk area that the file is being read continuously with no end, until I stop SSIS running.  When I take off error and truncation redirection then I see the truncation error as all those spaces dont fit into column 3.

Thanks for the link you provided - my difference is the space padding which means that unfortunately this will not work for me as the 3rd column cannot hold the large number of spaces - I am not sure why the footer row is not redirected to the error table, it contains a field which is type text therefore this should be able to contain the row.  Perhaps I could make column 3 large enough to hold the spaces and modify it later but this is not a good change because it means I cannot handle normal data rows which have this field being to big automatically (data errors get reported in output reports, which this would break).

I still feel this is a bug because there is no error when redirecting the output, it is reading the file continually and never completes.  I can provide a sanitised data file if someone would like to test if they get the same result?  As far as I can tell, handling space padded delimited text files with a footer in SSIS 2012 is not possible, whereas in SSIS 2008 it worked!

At the moment I can only process the files in SQL 2012 by removing the footer.

Thanks,

Gary.

• Edited by Gary_1_2_3 14 hours 32 minutes ago

My file is delimited but also space padded - it looks almost like a fixed width file apart from it has delimiters.  This means the footer row is space padded to the full length of the main data rows, i.e. there are a lot of spaces (over 1,500) after the last character of the footer.

If the flat file has padded spaces in the footer row, you can try to treat this file as fixed width file. You should only attempt this if you are 100% sure that (a) # of flat file columns are fixed (b) and their width will also be constant. This will allow footer to be loaded into the destination table, that can be deleted by T-SQL delete.

Looks like it is the ragged right file, hmm I think I encountered an error like that before,

e.g.

Name     $200.10   Comment<\n>
Name2    $200.10   Comment2<\n>
---------1---------2---------3

Is this the case?

See http://www.timmitchell.net/post/2013/01/14/ragged-flat-file-processing-in-ssis/ about the SSIS 2012 gotcha and yes, looks like you need some coding.