How to use SQL Task to update a table

Question

I have this SQL to update modified records in my table. It runs in SSMS, but how do I implement this in SSIS package? I tried to use SQL task but it does not recognize the columns.

I can put a select within a data flow ole db source (sql command has select statement), derived column for my import date, then ole db destination to update the records - but what is the syntax of the sql command to update?

update nss.MKT_CUSTOM
set 
      [INSURED_NUMBER] = stg_MKT_CUSTOM.[INSURED_NUMBER]
      ,[SALES_AGENCY_NUMBER] = stg_MKT_CUSTOM.[SALES_AGENCY_NUMBER]
      ,[PRODUCT_CODE] = stg_MKT_CUSTOM.[PRODUCT_CODE]
      ,[INS_MKT_INFO_SRC_CREATE_DATE] = stg_MKT_CUSTOM.[INS_MKT_INFO_SRC_CREATE_DATE]
      ,[INS_MKT_INFO_SRC_CREATE_USER] = stg_MKT_CUSTOM.[INS_MKT_INFO_SRC_CREATE_USER]
      ,[MKT_INFO_SRC_CODE] = stg_MKT_CUSTOM.[MKT_INFO_SRC_CODE]
      ,[MKT_INFO_SRC_DESC_EN] = stg_MKT_CUSTOM.[MKT_INFO_SRC_DESC_EN]
      ,[MKT_INFO_SRC_DESC_FR] = stg_MKT_CUSTOM.[MKT_INFO_SRC_DESC_FR]
      ,[MKT_INFO_SRC_START_DATE] = stg_MKT_CUSTOM.[MKT_INFO_SRC_START_DATE]
      ,[MKT_INFO_SRC_END_DATE] = stg_MKT_CUSTOM.[MKT_INFO_SRC_END_DATE]
      ,[MKT_INFO_SRC_CREATE_DATE] = stg_MKT_CUSTOM.[MKT_INFO_SRC_CREATE_DATE]
      ,[MKT_INFO_SRC_MOD_DATE] = stg_MKT_CUSTOM.[MKT_INFO_SRC_MOD_DATE]
      ,[MKT_INFO_SRC_CLIENT_CODE] = stg_MKT_CUSTOM.[MKT_INFO_SRC_CLIENT_CODE]
      ,[MKT_INFO_SRC_CAT_CODE] = stg_MKT_CUSTOM.[MKT_INFO_SRC_CAT_CODE]
      ,[MKT_INFO_SRC_CAT_DESC_EN] = stg_MKT_CUSTOM.[MKT_INFO_SRC_CAT_DESC_EN]
      ,[MKT_INFO_SRC_CAT_DESC_FR] = stg_MKT_CUSTOM.[MKT_INFO_SRC_CAT_DESC_FR]
      ,[MKT_INFO_SRC_CAT_CREATE_DATE] = stg_MKT_CUSTOM.[MKT_INFO_SRC_CAT_CREATE_DATE]
      ,[MKT_INFO_SRC_CAT_MOD_DATE] = stg_MKT_CUSTOM.[MKT_INFO_SRC_CAT_MOD_DATE]
      ,[IMPORT_DATE] = GETDATE()
from 
    nss.stg_MKT_CUSTOM
inner join
    MKT_CUSTOM on (stg_MKT_CUSTOM.INS_MKT_INFO_SRC_ID = MKT_CUSTOM.INS_MKT_INFO_SRC_ID)
where 
    (stg_MKT_CUSTOM.MKT_INFO_SRC_MOD_DATE <> MKT_CUSTOM.MKT_INFO_SRC_MOD_DATE 
     or stg_MKT_CUSTOM.MKT_INFO_SRC_CAT_MOD_DATE <> MKT_CUSTOM.MKT_INFO_SRC_CAT_MOD_DATE)

Can you do it in an Execute SQL Task? – shree.pat18 Jul 08 '15 at 04:28 — shree.pat18, Jul 08 '15 at 04:28

score 0 · Answer 1 · answered Jul 08 '15 at 07:47

0

If you need to perform an update action within data flow task, you need to use OLE DB Command component.

However as I cannot see any parameters/variables within your statement, you can try to use Execute Sql Task in control flow panel.

answered Jul 08 '15 at 07:47

cqi

539
3
13

I do have it in Execute SQl task (in control flow panel). When I run it, my exe results shows this error:[Execute SQL Task] Error: Executing the query "update nss.MKT_CUSTOM set [INSURED_NUMBER] = stg_..." failed with the following error: "Incorrect syntax near ')'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. – Steph Roberts Jul 08 '15 at 14:12
Unfortunately I could not figure out the error from your statement without knowing your table's structure. If the above statement can be run in SSMS, what you can do is 1 adding a using clause to verify connection issue 2 ensure the option in resultset is NONE, 3 change BypassPrepare to false, and click Parse Query button to verify your sql statement in the component is correct – cqi Jul 09 '15 at 03:23

score 0 · Answer 2 · answered Aug 10 '15 at 19:11

0

i this case i try to put your update in a new sql procedure, and in execute sql-task I try to execute it.

answered Aug 10 '15 at 19:11

Mattia Caputo

959
1
8
17

How to use SQL Task to update a table

2 Answers2