SSIS import full text from html or combine rows

Hi

I'm pretty new to SSIS and I wonder if you could help me.

I download HTML files (thounsands) as text files using SSIS and then using foreach loop I load each file into table text data type field. Works great apart from that some files appear on multiple rows I presume SSIS thinks there is a new record but there isn't as I want to load the whole file into the table text data type field.

So I have two questions:
1) How to import text file in full?
2) If there isn't a reliable way to do point 1 is there an easy way to combine several rows into one? (The table contains text data type field with html code + extra field FileName so files in multiple rows have the same filename)

Many thanks in advance.
Emil

re: SSIS import full text from html or combine rows

Hi

I have decided to do it using a cursor which should work fine but i have a problem to insert (insert into) a row with more then 43679 characters. It works perfectly with nvarchar(max) <= 43679 characters but it inserts NULL if it is above it.

That might be also the reason why the SSIS import text file is split into multiple lines.

The table field is text (I tried nvarchar(max)) and the variable is nvarchar(max) as i couldn't use text. I've tried all possible ways and cannot seem to be able to resolve it.

Many thanks in advance for any information
Regards
Emil

re: SSIS import full text from html or combine rows

1. Check if there are line terminator that could serve as record terminator.
2. Create a table with single column with enough space for that record.
3. Store the entire table as XML (max size: 2G).
4. Parse the XML

Good luck!!!


--- CK

re: SSIS import full text from html or combine rows

Hi Ck

That sounds like a way to go.

Quick question. In the past I come across an issue where I was exceeded row size but I was getting error and now I don't. Another thing I noticed is that when I use a cursor to merger the rows before I insert them I used print and it printed everything on my screen but it seems doesn't insert it.... Is it possible that it inserts it but cannot display it?

Many thanks
Emil

re: SSIS import full text from html or combine rows

I've checked the text file and it is 46kb and proper one is 90kb but after taking out rubbish it is 4k so I think I will just clean each row and then merge it.

That should work for me.

Thanks for the respone.

Regards
Emil