SSIS-data insert from Temp table to real sql table

• mtz676

  SSCrazy Eights

  Points: 9212

  More actions

  July 24, 2015 at 3:06 am

  #319252

  DECLARE @ServiceStatus TABLE

  (ServerName varchar(128)

  ,ServiceName varchar(128)

  ,StatusOfService varchar(128)

  ,StatusAsOn datetime)

  INSERT INTO @ServiceStatus (StatusOfService)

  EXEC master..xp_servicecontrol 'QueryState', 'msdtc'

  UPDATE @ServiceStatus

  SET ServerName=cast(@@SERVERNAME as varchar(128))

  ,ServiceName='Microsoft Distributed Transaction Coordinator'

  ,StatusAsOn=GETDATE()

  --WHERE ServerName IS NULL

  SELECT * FROM @ServiceStatus

  The above code is part of a data flow task - code is in ole db source as sql code with a ole db destination(a sql table).I can preview the result in the ole db source but upon executing the data flow task the data is not inserted.The data flow task turns green with no data being inserted.

  Can someone please help fix this.I need the data to be inserted into the ole db destination(sql table) when I run the data flow task.

  Thanks

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  July 24, 2015 at 4:07 am

  #1814677

  metroman17 (7/24/2015)

  ---

  DECLARE @ServiceStatus TABLE

  (ServerName varchar(128)

  ,ServiceName varchar(128)

  ,StatusOfService varchar(128)

  ,StatusAsOn datetime)

  INSERT INTO @ServiceStatus (StatusOfService)

  EXEC master..xp_servicecontrol 'QueryState', 'msdtc'

  UPDATE @ServiceStatus

  SET ServerName=cast(@@SERVERNAME as varchar(128))

  ,ServiceName='Microsoft Distributed Transaction Coordinator'

  ,StatusAsOn=GETDATE()

  --WHERE ServerName IS NULL

  SELECT * FROM @ServiceStatus

  The above code is part of a data flow task - code is in ole db source as sql code with a ole db destination(a sql table).I can preview the result in the ole db source but upon executing the data flow task the data is not inserted.The data flow task turns green with no data being inserted.

  Can someone please help fix this.I need the data to be inserted into the ole db destination(sql table) when I run the data flow task.

  Thanks

  I would suggest creating a proc which contains this code and using your proc as the data source.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• mtz676

  SSCrazy Eights

  Points: 9212

  More actions

  July 31, 2015 at 12:29 am

  #1816355

  I cannot create a Stored proc across 100's of servers.

  I will be running this code across servers from the SSIS package.

• Tim Mitchell

  SSCoach

  Points: 15681

  More actions

  August 2, 2015 at 10:16 am

  #1816675

  Do you even need the table variable? Seems to me that you are creating the table variable strictly for the purpose of performing a relatively trivial update on the result set from the sproc master..xp_servicecontrol.

  I'd recommend using EXEC master..xp_servicecontrol as your data flow source query, and then update those three columns in a Derived Column transformation. This will eliminate the need for the table variable entirely.

  Tim Mitchell, Microsoft Data Platform MVP
  Data Warehouse and ETL Consultant
  TimMitchell.net | @Tim_Mitchell | Tyleris.com
  ETL Best Practices

• Phil Parkin

  SSC Guru

  Points: 246962

  More actions

  August 2, 2015 at 11:52 am

  #1816687

  Tim Mitchell (8/2/2015)

  ---

  Do you even need the table variable? Seems to me that you are creating the table variable strictly for the purpose of performing a relatively trivial update on the result set from the sproc master..xp_servicecontrol.

  I'd recommend using EXEC master..xp_servicecontrol as your data flow source query, and then update those three columns in a Derived Column transformation. This will eliminate the need for the table variable entirely.

  Good advice. Might need to add With Result Sets so that the component knows what sort of data it's dealing with.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Jeff Moden

  SSC Guru

  Points: 1004356

  More actions

  August 2, 2015 at 12:04 pm

  #1816689

  metroman17 (7/31/2015)

  ---

  I cannot create a Stored proc across 100's of servers.

  I will be running this code across servers from the SSIS package.

  Are all those servers on the same domain?

  As a bit of a sidebar, I'd love to see your license budget for the 100's of servers.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• mtz676

  SSCrazy Eights

  Points: 9212

  More actions

  August 4, 2015 at 4:40 am

  #1817060

  Thanks but...

  Why is the existing one not working?