SQL Server Performance

Importing an entire text file into a varchar(max)

Discussion in 'SQL Server 2005 Integration Services' started by SQL_Guess, Jun 19, 2006.

  1. SQL_Guess New Member

    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
  2. waqar Member

    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. *~
  3. SQL_Guess New Member

    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 @SQLImportCMD=@SQLImportCMD+@RuleFilePath+''', single_clob) as RuleFile'<br />IF (@DebugLevel &gt; 1) SELECT @SQLImportCMD<br />EXEC (@SQLImportCMD)<br />SELECT@RuleSetText=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

Share This Page