title: SSIS Execute SQL Task - Statement from Variable - Variable value from a table
body: <p>Folks,</p>
  
  <p>I am creating SSIS package, which contains Execute SQL task.  The "SQL Source Type" property is set to be Variable and it uses one of my package variables.  So far so good.  Now all of this is wrapped inside "For Each" loop, which reads values from a database table.  You see, I have a table with I bunch of SQL statements (stored in VARCHAR column), which I would like to execute.  All of this seems to work until I have a multi-line SQL statement in my table.  If there is a carriage return present, here is what I observe.  Let's say I have this statement stored in my table and I would like to run it </p>
  
  <pre><code>IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.MyTable') AND name = N'MyIndex')
  DROP INDEX MyTable.MyIndex 
  </code></pre>
  
  <p>If I set a breakpoint in SSIS package an look at the value of the variable, which gets populated with this sql text, it looks as follows (notice \r\n):</p>
  
  <pre><code>IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.MyTable') AND name = N'MyIndex')\r\nDROP INDEX MyTable.MyIndex
  </code></pre>
  
  <p>and if I let it execute and capture the results using Profiler, the statement, which actually runs will be everything up to \r, in other words, only the first line:</p>
  
  <pre><code>IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.MyTable') AND name = N'MyIndex')
  </code></pre>
  
  <p>This is obviously not what I want.  The actual statements I need to run are much more complicated and would prefer to keep them nicely formatted, so they can be visually examined if necessary.  Is this a known limitation I am encountering and are there any workarounds?</p>
  
  <p>Thank you!</p>