Importing an entire text file into a varchar(max) | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Importing an entire text file into a varchar(max)

Hi all, It’s another work day, which means it’s another question from the bemused… [:I] I have a flat file source – a text file that will contain an undefined about of text. I want to import the entirety of that into a varchar(max) column. Currently I’m trying to do this in SSIS, using SSIS compoent, but I’m have a few issues – a couple of complaints about conversion from str to text (according to the component, it seemed to think that varchar(maz) = text. I cahnged the target column to a varchar(8000), and it worked. That’s not going to be a solution, since I’m pretty certain we are looking at > 8000 characters. Is there another way, or am I going to have to sink to using an SP, giving it the path to a file, and doing all the work ‘outside’ SSIS? I’ve tried:
1> Text Source to SQL Destination
2> Import Column task am I missing the boat again? Panic, Chaos, Disorder … my work here is done –unknown
Hi, Not sure if you are still looking for solution.
Here what i did DECLARE @obj VARCHAR(MAX) SELECT
@obj=BulkColumn
FROM
OPENROWSET(BULK ‘C: mp.TXT’,SINGLE_BLOB) AS
ExternalFile
You can use @obj to insert into your table. I am using this to import Image files in database. Good luck. Waqar. ________________________________________________
~* Opinions are like a$$holes, everyone got one. *~
Hi Waqar,<br /><br />thanks for the response. I was able to do this with T-SQL – I was trying to find out whether there was a relevant SSIS component to use for this, rather than T-SQL. BTW,m the code I was using is very similar, except I was using a dynamic SQL command to allow the change of the file location, and SINGLE_CLOB because my data is text. FWIW, here’s a sample of my code (I had to use a global TempTable because of the dynamic SQL to handle changing paths (the path was a input I received into the SP). I say was, because most of the process is now being done in BizTalk, so this requirement has fallen away.<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />IF EXISTS (SELECT 1 FROM tempdb.sys.tables WHERE [Name] = ‘##TestData’)<br />DROP TABLE ##TestData<br />CREATE TABLE ##TestData<br />(RuleTextNVARCHAR(MAX))<br /><br />DECLARE<br />@DebugLevel TINYINT,<br />@RuleSetTextNVARCHAR(MAX),<br />@SQLImportCMDVARCHAR(204<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />,<br />@RuleFilePathVARCHAR(255),<br />@RuleSetTokenNCHAR(13),<br />@CalculateTokenDATETIME<br /><br />SET @DebugLevel = 0 –set this to 1 to run in debug mode to include additional information<br /><br />SET @RuleFilePath = ‘D:DocumentationProjectsIntegration ServicesBIDS ProjectsTesco DNF Integration ServicesSample DataFiles Processing URLPromotionRulesNew.txt'<br />SET @SQLImportCMD = ‘Insert into ##TestData (RuleText)<br />select RuleFile.* from openrowset (BULK N”'<br />SET @[email protected][email protected]+”’, single_clob) as RuleFile'<br />IF (@DebugLevel &gt; 1) SELECT @SQLImportCMD<br />EXEC (@SQLImportCMD)<br />[email protected]=RuleText FROM ##TestData<br />IF (@DebugLevel &gt; 1) SELECT DATALENGTH(@RuleSetText) as [Size of NVARCHAR Used]<br />IF (@DebugLevel &gt; 0) SELECT * FROM ##TestData<br /></font id="code"></pre id="code"><br /><br /><br />Panic, Chaos, Disorder … my work here is done –unknown
]]>