title: import large flat file with very long string as SSIS package
body: <p>I am trying to create a SSIS package through data import in SQL Server 2012.</p>
  
  <p>The data looks like this (this is <strong>just 1 row</strong> of the data, removed confidential data):</p>
  
  <blockquote>
    <p>35  XXXX.XXXXXXXXXXXXXXXX     2.  XXXX.     6.    XXXX.       1.  10.NNN    80000000.XXXXXXXXd  1001.  1001.     6.NNNNNNNNNNNNNNN   0.   0.        17.XXXXXXXX-XXXXXXXXXXX-XXINCMGFO                       XXXX.     0.   0.00000000000000N N XXXXXXXXXXXXXXXXXXXXXXXXXXXEVITE, INC.              XXXXXXXX  CA US   5734.     0.0TMP 88          12.XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX        12.XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX XXXXXXXXXXXXXXXXXXXXX         -.270000        .00</p>
  </blockquote>
  
  <p>Each row of this flat file does NOT share the same pattern and <strong>some may have single quote or double quote</strong> in the data. These single/double quotes may not be complete, they left in the data because the data has not been cleaned completely.</p>
  
  <p>Each flat file is generated at the end of each month. What I am planning to do is to create a SSIS package, then create a job to call this SSIS package to store all the monthly raw data into 1 staging table, automatically. Later I will write a stored procedure to clean the data in this staging table and move the cleaned data into another table.</p>
  
  <p>But my question here is about creating this SSIS package.</p>
  
  <p>It kept giving me error like this:
  <a href="https://i.stack.imgur.com/RW6oU.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/RW6oU.png" alt="error imag"></a></p>
  
  <p>Therefore, I tried to change the properties:</p>
  
  <p><strong>Change String to 5000 length</strong>, I also tried 8000, didn't work either</p>
  
  <p><a href="https://i.stack.imgur.com/5zme7.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/5zme7.png" alt="set string length as 5000"></a></p>
  
  <p><strong>Change to Text Stream</strong>, since I thought this may allow longer string..</p>
  
  <p><a href="https://i.stack.imgur.com/VXOrJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/VXOrJ.png" alt="set as text stream"></a></p>
  
  <p>They still gave me the same error.</p>
  
  <p>So I have found a solution <a href="https://stackoverflow.com/questions/9805948/how-to-solve-the-maximum-lenght-error-it-is-too-long-in-sql-query-c-sharp">here</a>
  I guess maybe it's because my data has incomplete double quote or single quote in some rows, but I cannot parameterized queries as the solution does, because everything in a row from the flat file will be saved in 1 column....</p>
  
  <p>Therefore, is there any way to resolve this problem as create SSIS package?</p>