sql server 2008 - Using SSIS to transfer a CSV file that contains SQL (i.e., potential special characters) in it -

Using SSIS to transfer to an SSI file that contains SQL.

I am using the net. To create a CSV file that I then move to the table using an SSIS package.

The content of the file is a 36 character GUID and any SQL text that contains tab character, pipe characters, and possibly any type of character I thought I would use my own column and line delimitator Windows Charmmap Accessory Utility To specify non-type characters for the delimiter. I chose one ¼ and one half as the kernel and line delimiters, respectively.

test file I created looks like this:

  Guid¼Sql½3afc912b-917d-4719-8ded-22e5d95930a3¼SELECT * from TABLE½a867fa30-f2c7-459e-8985-9ef42616991e¼SELECT *  
  as count: string [DT_STR] 36 Sql: text stream [DT_TEXT]   
 I have the following SQL Server Moving into the target table:.   CREATE TABLE [dbo] [CodeObjectSql] ([Guid] [four] (36) No NULL, [Sql] [Varchar] (max) No) [Primary]   
 When I preview the file, the column delimiter character looks like the last (37th) character and row of the first column delimiter character appears as the last character of the SQL column value < / P> 
 I get this error:.  
  Error: 0xC02020A1 CodeObjectSql load, CodeObjectSql file [1]: Data conversion failed column "data conversion status values ​​to guide" returned 4 and status text "Text was truncated or target One or more characters in the code page did not match. " Error loading 0xC020902A on CodeObjectSql, CodeObjectSql file [1]: "Output column" GUID "(10)" failed because truncation occurred, and cut Guid "output column" on-cut line nature "(10)" cut failure -Specifies on the screen. There was a transaction error on the specified object of specified component. Error: 0xC0202092 load CodeObjectSql, CodeObjectSql file [1]: 2. Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED: load CodeObjectSql, 0xC0047038 at SSIS.Pipeline an error went file processing "C: \ CodeObjectSql.csv" data line. Component "CodeObjectSql file is defined by Principal output method (1) by means of an error code 0xC0202092 returned component due to called Pipeline Engine Praimotput () failure code returned failure code components", but the error is fatal and execute pipeline has stopped. Error messages can be posted before with more information about the failure.   
 I then tried to convert files into Unicode and tried to modify column types in their Unicode counterparts  
  Guid: string [DT_WSTR] 36 Sql: text stream [DT_NTEXT]   
 And there is still no luck.  
 My experience is that SSIS can not handle the delimiter character of a column in one row or in the data, qualifying character and text value to double an special character while representing an event in the data Is for me.  
 If my beliefs are all truth based on my trial, then what is the best format for this type of data?  
   
 
 
 > 
 Test file: all on one line  
  Guid¼Sql½3afc912b-917d-4719-8ded-22e5d95930a3¼SELECT * from   
 Flat File Source Editor configuration:  
  Code page: 1252 Format: delimited text qualification: & lt; None & gt; Header line delimiter: {CR / LF} Leaving header row: 0 column names in the first data row: checked   
 Now go to the column:  
  Line delimiter: 1/2 column delimiter: 1/4 question / suggestion: can not you simplify the delimiter? Usage of 1/2 and 1/4 is unusual.  
 Also, right-click on the data source. Go to the input and output properties tab // output column // Guid. Convert area properties to DT-STR (36).