Transformations using dynamic SQL

[mutley1]

Hi all,

I'm trying to create a package the will connect to about 50 servers to collect the data in the code below, but seeing as it is to query each DB on each server, i need to use the sp_MSForeachdB @command1='SQL CODE HERE', but it doesn't seem to recognise the clumn names I have specified in the SQL

It works when I run it on the server I am currently connected to, but when using it as a SQL task step in DTS, the transformation task does not have anything in the source columns, and therefore doesn't work. I have tried insert into server.db.dbo.table, but get the error message about linked servers, and I don't want to have to set up 50 linked servers.

Any help would be appreciated.

exec sp_MSforeachdb @command1='use [?] 
select	GetDate() AS [datestamp],
	@@servername AS [Servername],
	''?'' AS [DataBaseName],
	fileid AS [Fileid],
	sf.groupid AS [GroupId],
	left([groupname],20) AS [Grp],
	rtrim(filename) AS [phname],
	[size]/128 AS [size_mb],
	FILEPROPERTY([name], ''SpaceUsed'')/128 AS [used_mb],
	case when (100 - ((convert (float,(FILEPROPERTY([name],
''SpaceUsed'')/128)) / (convert (float,[size]/128)))*100)) is NULL then 0
		else 100 - ((convert (float,(FILEPROPERTY([name],
''SpaceUsed'')/128)) / (convert (float,[size]/128)))*100) end AS [space_free],
	case when (sf.status&0x100000) > 0 then str(growth)+'' %''
		else str(growth/128)+'' mb'' end AS [file_growth],
	case when [maxsize]<0 then ''Unrestricted''
		else str([maxsize]/128) end AS [max_mb],
	rtrim([name]) AS [lname],
	case
		when filename like ''%.mdf%'' THEN ''DATAFILE''
		when filename like ''%.ldf%'' THEN ''LOGFILE''
		when filename like ''%.ndf%'' THEN ''MULTIPLE FILE GROUP''
		else ''UNKNOWN FILE TYPE''
			END AS [filetype]
from sysfiles sf 
	left outer join
sysfilegroups sfg 
	on sf.groupid=sfg.groupid
order by 1, 2, 3'

SET ARITHABORT ON
SET ANSI_WARNINGS ON

Cheers

M.