By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,144 Members | 892 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,144 IT Pros & Developers. It's quick & easy.

SSIS import full text from html or combine rows

P: 36
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
Sep 25 '09 #1
Share this Question
Share on Google+
4 Replies

P: 36
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
Oct 27 '09 #2

ck9663
Expert 2.5K+
P: 2,878
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
Oct 27 '09 #3

P: 36
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
Oct 29 '09 #4

P: 36
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
Oct 29 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.