0

I wrote a code like this in execute sql task

Declare @tmpHistoricDriveSpace Table 
( Servername varchar(255), MachineName varchar(255), Drive char(2), FreeSpace_MB int) 
insert into @tmpHistoricDriveSpace (Drive, FreeSpace_MB) exec xp_fixeddrives

update @tmpHistoricDriveSpace 
set Servername = convert(varchar(225), 
SERVERPROPERTY('servername')),   
MachineName = convert(varchar(225), SERVERPROPERTY('MachineName'))

Select MachineName, Servername, Drive, Freespace_MB 
from @tmpHistoricDriveSpace

This looks good but I am unable to carry this result into data flow task in SSIS which I am using to populate into a table.

I am not liking the idea of creating a temp table on master database or just creating a new database for this task alone.

Any help is much appreciated.

Thanks

Please refer this link for previous discussion

Community
  • 1
  • 1
Prav54
  • 3
  • 4
  • Is your objective to gather SQL Server drive space and save it to a central table? There are many ways to do this and you're discovering that SISS is one of the more convoluted ways to do it. Do you **have** to use `xp_fixeddrives` or is this just the way you've worked it out so far? Have you considered using the WMI task instead? Try this example but write to a table instead of a CSV: https://www.mssqltips.com/sqlservertip/2932/using-the-ssis-wmi-task-to-gather-system-information/ – Nick.Mc Mar 01 '16 at 23:29
  • Thanks Nick.. I really cannot use WMI for some reason and that doesn't really help my problem. I am trying to running different flows in the same package so that the results obtained are on sync.. like I am doing for file size , free space etc,. – Prav54 Mar 02 '16 at 06:05
  • But temp tables are made for this ... that's their purpose, why don't you create normal temp table, drop index on it, do your stuff and then drop it – Veljko89 Mar 02 '16 at 08:50
  • If you can't use WMI (I don't understand why not) then as I suggested in your last post, create the temp table if it doesn't exist beforehand. – Nick.Mc Mar 02 '16 at 10:07
  • Thanks Nick for your suggestion. The whole point for me to do a rework is because we are using temp table and the access is lost when ever the server is restarted which I am looking to fine tune. – Prav54 Mar 03 '16 at 01:41
  • When the server is restarted your temp table is lost. So you need to write SQL script that runs first creates the temp table _if it does't exist_. That way it doesn't matter whether the table exists or not, it will be created if it doesn't exist – Nick.Mc Mar 03 '16 at 03:20
  • A better way might be to just execute the `xp_fixeddrives` in a data flow source and use derived columns to add in the server information. No temp tables required. – Nick.Mc Mar 03 '16 at 03:22

1 Answers1

0

Instead of writing the code in execute sql task why can't you write it in OLE DB Source directly.

Thanks, Santhosh

user3498113
  • 1
  • 3