SSIS-data insert from Temp table to real sql table

Question

SSIS-data insert from Temp table to real sql table

mtz676

SSCrazy Eights

Points: 9180
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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246695 More actions · Answer 1

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.

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.

mtz676 SSCrazy Eights Points: 9180 More actions · Answer 2

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 · Answer 3

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: 246695 More actions · Answer 4

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.

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.

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 5

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: 9180 More actions · Answer 6

mtz676

SSCrazy Eights

Points: 9180

August 4, 2015 at 4:40 am

#1817060

Thanks but...

Why is the existing one not working?