Invalid Data Causes SSIS Flat File Import to Hang

Question

I'm working on a SSIS package in VS 2010 for a 2012 instance. I'm importing flat files from a vendor who will not clean their data. I cannot get beyond the "Flat File Source" step because the data is so mangled it hangs and won't continue parsing.

Here is an example of good data with headers:

EventID|AccountID|ListID|ID|Date
1|3000|20030|1092997696|10-Nov-2014 09:36:13

Here is bad data that will (and is) be/ing captured by error handling:

1|3000|20030|1092997696;ҧ��DAVNJ��|11-Nov-2014 06:40:28

Here is data that hangs my package:

1|3000|20030|1092997696ci[
a5��~[�t:RW�uXXïA,u��ïn��I�    �JA!QXQ|11-Nov-2014 08:27:27

How do I deal with this? Remember, I cannot get beyond the flat file parse step to use a derived column/conditional split/script task.

Thanks in Advance! Kirsten

Answer 1

Figured out a way to get the data in so I can clean it!

CREATE TABLE dbo.crap_data_varcharmax(
    DataBlob NVARCHAR(MAX));

BULK INSERT dbo.crap_data_varcharmax
FROM '\\SQLSERVERNAME01\e$\Folder\FileName.txt' WITH (ROWTERMINATOR = '\n', FIELDTERMINATOR = '|', FIRSTROW = 2);

SELECT * 
FROM dbo.crap_data_varcharmax;