Search code examples
sql-servert-sqlssis

UPDATE Statement Not Updating in Execute T-SQL Task

I have a simple package that flows data from point A to point B - in the middle is a row count transformation that populates a variable of type Int32.

After the Data Flow Task, I have a simple Execute SQL Task with an update statement:

UPDATE  [SAP].[SAPCopyInfo]
  SET [endtime] = GETDATE(),
      [MSEGCnt] = ?
  WHERE [BegTime] = CAST(GETDATE() AS DATE)

I have mapped my variable as a parameter like so:

Execute SQL Task Parameter Mapping

When I execute the package, the Execute SQL Task (named 'Update Log') shows success:

Execution Status

The variable watch shows that intMSEGCnt is populated correctly:

intMSEGCnt

Yet the table doesn't update - and there are no errors in the output window:

Table

Manually executing the UPDATE SQL in SSMS works fine (replacing ? with the correct value), so it doesn't appear to be a permissions issue.

I've tried various iterations of the 'Data Type' for the Parameter Mapping, yet nothing works.

I've even de-parameterized the UPDATE statement and run it directly in the Execute SQL Task with hardcoded values and it still doesn't work.

What am I missing?

Solution

  • Sometimes you just need another set of eyes - thanks to @GSerg & @T N

    The answer was to simply change the UPDATE query to this:

    UPDATE  [SAP].[SAPCopyInfo]
  SET [endtime] = GETDATE(),
      [MSEGCnt] = ?
  WHERE CAST([BegTime] AS DATE) = CAST(GETDATE() AS DATE)