[code language=”sql”]
DECLARE @Table TABLE(
SPID INT,
Status VARCHAR(MAX),
LOGIN VARCHAR(MAX),
HostName VARCHAR(MAX),
BlkBy VARCHAR(MAX),
DBName VARCHAR(MAX),
Command VARCHAR(MAX),
CPUTime INT,
DiskIO INT,
LastBatch VARCHAR(MAX),
ProgramName VARCHAR(MAX),
SPID_1 INT,
REQUESTID INT
)
INSERT INTO @Table EXEC sp_who2
SELECT *
FROM @Table
where LOGIN like ‘%xxx%’
[/code]
SSRS Automation
First of all create a proxy class in c# using wsdl see this Technet Article for more details
wsdl /out:C:/ReportService.cs http://[servername]/ReportServer/ReportExecution2005.asmx?wsdl
Create a new SSIS Package using BIDS
Add a Script component, pre-configure the component as a transformation, and connect it to the OLE DB source.
Confirm that the ScriptLanguage property is set to Microsoft Visual C# 2008. On the Input Columns page of the Script Transformation Editor, select all the available input columns.
Edit the script to add references to the System.Web.Services and System.Xml assemblies, and to add the C# proxy class.
Build the code.
If you don’t Build the code – then the references will disappear – don’t know why but just a single build is all you need!