title: Declare statement is not supported in SQL command of SSIS
body: <p>I am trying do the following using SSIS. I am using SDT latest version and querying SQL Server 2008 R2.</p>
  
  <p>Could somebody suggest if I am doing it wrong and if there is a better way in doing it</p>
  
  <p>I basically need to execute a stored procedures from the source and store the results it in the destination. There are around 20 stored procedures. </p>
  
  <p>Each of the stored procedures takes XML as input. I have created a Control task within that a data flow task. Within the data flow i am creating the oledb source.</p>
  
  <p>I have selected Data access mode as SQL command. I am created my query below but when i preview , I get error saying that declare statement is not supported</p>
  
  <pre><code>DECLARE @xmldata XML
  
  SET @xmldata = (SELECT DISTINCT TOP 1000 (mc.companyId) AS id
                  FROM ciqMarketCap mc 
                  JOIN ciqCompany c ON c.companyid = mc.companyid
                  WHERE c.companyStatusTypeId NOT IN (5, 6) 
                    AND c.companyTypeId IN (1, 4) 
                  FOR XML PATH(''), TYPE, ROOT('partyIds'))
  
  EXEC getPartiesMarketData @xmldata
  </code></pre>
  
  <p>How do I execute the rest of the stored procedures? Do I need to create several oledb sources?</p>