title: INSERT variable values into a table
body: <p>I have several variables in an SSIS package that I would like inserting into a table.</p>
  
  <p>example:-</p>
  
  <pre><code>@financialMonth, @Status, @Comments
  </code></pre>
  
  <p>The Variables have been populated along the way with values based on lookups, filename, dates, etc, and I want to store them in a results table.</p>
  
  <p>Is using the execute SQL task the way to do this ?</p>
  
  <p>Do I need to call a sproc and pass those variales as parameters ?</p>
  
  <p>I've tried putting the following T-SQL into the SQLStatement property</p>
  
  <pre><code>INSERT INTO FilesProcessed 
     (ProcessedOn, ProviderCode, FinancialMonth, 
     FileName, Status, Comments) 
  SELECT     GETDATE(), 'ABC' , 201006, 
     'ABC_201005_Testology.csv', 
     'Imported','Success' 
  </code></pre>
  
  <p>
  I tried hardcoding the values above to get it to work</p>
  
  <p>These are the columns on the table I'm inserting into</p>
  
  <pre><code>Column_name     Type         Computed  Length
  fileID          int          no          4
  ProcessedOn     datetime     no          8
  ProviderCode    nchar        no          6
  FinancialMonth  int          no          4
  FileName        nvarchar     no        510
  Status          nvarchar     no         40
  Comments        nvarchar     no        510
  </code></pre>
  
  <p>This is the Expression code that feeds the SQLStatementSource property</p>
  
  <pre><code>"INSERT INTO FilesProcessed (ProcessedOn, ProviderCode, FinancialMonth, 
    FileName, Status, Comments) SELECT     GETDATE() AS ProcessedOn, '"
    + @[User::providerCode] + "' , " 
    + (DT_STR,6,1252)@[User::financialMonth] + ", '" 
    + @[User::fileName] + "', 'Imported' AS Status,'Successfully' AS Comments "
  </code></pre>
  
  <p></p>
  
  <p>Unfortunately I'm missing something, and can't quite get it to work.</p>
  
  <blockquote>
    <p>The Error message I'm getting is ...
    Error: 0xC002F210 at Log entry in
    FilesProcessed, Execute SQL Task:
    Executing the query "INSERT INTO
    FilesProcessed (ProcessedOn,
    ProviderCode, FinancialMonth,
    FileName, Status, Comments) SELECT<br>
    GETDATE(), 'ABC' , 201006,
    'DAG_201005_Testology.csv',
    'Imported','Successfully'" failed with
    the following error: "An error
    occurred while extracting the result
    into a variable of type (DBTYPE_I2)".
    Possible failure reasons: Problems
    with the query, "ResultSet" property
    not set correctly, parameters not set
    correctly, or connection not
    established correctly.</p>
  </blockquote>
  
  <p>Please </p>
  
  <p>a). Advise whether the Execute SQL Task is the way to do what I want to do.</p>
  
  <p>b). Give me any pointers or pitfalls to look out for and check.</p>
  
  <p>Thanks in advance.</p>