Code SSIS Services

• mtz676

  SSCrazy Eights

  Points: 9176

  More actions

  July 1, 2015 at 4:08 am

  #305419

  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 - ole db source as sql code with a ole db destination(a sql table)

  When I run this code I am able to find the data in preview in the ole db source but upon executing the data flow task the data is not inserted inThe 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: 246695

  More actions

  July 1, 2015 at 4:47 am

  #1809418

  metroman17 (7/1/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

  INSERT INTO @ServiceStatus (StatusOfService)

  EXEC master..xp_servicecontrol 'QueryState', 'MSSQLServer'

  UPDATE @ServiceStatus

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

  ,ServiceName='MSSQL Server'

  ,StatusAsOn=GETDATE()

  WHERE ServerName IS NULL

  INSERT INTO @ServiceStatus (StatusOfService)

  EXEC master..xp_servicecontrol 'QueryState', 'MSSQLServerADHelper'

  UPDATE @ServiceStatus

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

  ,ServiceName='SQL Server Active Directory Helper'

  ,StatusAsOn=GETDATE()

  WHERE ServerName IS NULL

  INSERT INTO @ServiceStatus (StatusOfService)

  EXEC master..xp_servicecontrol 'QueryState', 'SQLServerAgent'

  UPDATE @ServiceStatus

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

  ,ServiceName='SQL Server Agent'

  ,StatusAsOn=GETDATE()

  WHERE ServerName IS NULL

  INSERT INTO @ServiceStatus (StatusOfService)

  EXEC master..xp_servicecontrol 'QueryState', 'MSSQLServerOLAPService'

  UPDATE @ServiceStatus

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

  ,ServiceName='SQL Server OLAPService'

  ,StatusAsOn=GETDATE()

  WHERE ServerName IS NULL

  INSERT INTO @ServiceStatus (StatusOfService)

  EXEC master..xp_servicecontrol 'QueryState', 'SQLBrowser'

  UPDATE @ServiceStatus

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

  ,ServiceName='SQL Server Browser'

  ,StatusAsOn=GETDATE()

  WHERE ServerName IS NULL

  INSERT INTO @ServiceStatus (StatusOfService)

  EXEC master..xp_servicecontrol 'QueryState', 'MSSQLFDLauncher'

  UPDATE @ServiceStatus

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

  ,ServiceName='SQL Server Full Text Search'

  ,StatusAsOn=GETDATE()

  WHERE ServerName IS NULL

  --INSERT INTO @ServiceStatus (StatusOfService)

  --EXEC master..xp_servicecontrol 'QueryState', 'MsDtsServer'

  --UPDATE @ServiceStatus

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

  -- ,ServiceName='SQL Server Integration Services'

  -- ,StatusAsOn=GETDATE()

  --WHERE ServerName IS NULL

  INSERT INTO @ServiceStatus (StatusOfService)

  EXEC master..xp_servicecontrol 'QueryState', 'ReportServer'

  UPDATE @ServiceStatus

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

  ,ServiceName='SQL Server Reporting Services'

  ,StatusAsOn=GETDATE()

  WHERE ServerName IS NULL

  INSERT INTO @ServiceStatus (StatusOfService)

  EXEC master..xp_servicecontrol 'QueryState', 'SQLWriter'

  UPDATE @ServiceStatus

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

  ,ServiceName='SQL Server VSS Writer'

  ,StatusAsOn=GETDATE()

  WHERE ServerName IS NULL

  SELECT * FROM @ServiceStatus

  Is this a blog post?

  If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.