Using subquery in SSIS results in NULL values

Question

I am trying to execute some SQL within a OLE DB Source Connector within SSIS that will get some statistics regarding databasesizes.

This query ran fine when used in SSIS for one server. Now however I am looping through a number of servers and two columns that should be (and had been) producing ints now produce NULLS with datatype four-byte signed integer [DT_I4]. These are DataFileSizeMB and LogFileSizeMB. I am at a total loss here... (as always when turning here, it kinda feels like defeat simply having to ask ;)

This is the query, why won't it run in SSIS and does it run in SSMS?

   SELECT       @@SERVERNAME as Server
                ,getdate() as Meetmoment
                ,name as Databasenaam
                ,(  SELECT CAST(ROUND(SUM(size), 0) AS float) AS Expr1
                    FROM  ( SELECT database_id, type, size * 8.0 / 1024 AS size  
                            FROM sys.master_files) AS fs_2
                    WHERE        (type = 0) AND (database_id = db.database_id)
                ) AS DataFileSizeMB
                ,(  SELECT CAST(ROUND(SUM(size), 0) AS float) AS Expr1
                    FROM (  SELECT database_id, type, size * 8.0 / 1024 AS size
                            FROM sys.master_files) AS fs_1
                     WHERE        (type = 1) AND (database_id = db.database_id)
                ) AS LogFileSizeMB
 FROM            sys.databases AS db
 ORDER BY DataFileSizeMB DESC

I changed the query to

select @@SERVERNAME as Server, 
            getdate() as Meetmoment, 
            mdf.name as Databasename, 
            mdf.Size_MBs as  DataFileSizeMB, 
            ldf.Size_MBs as LogFileSizeMB 
FROM 

(SELECT d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
where type = 0
GROUP BY d.name) as mdf 

LEFT JOIN

(SELECT d.name,
ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs
FROM sys.master_files mf
INNER JOIN sys.databases d ON d.database_id = mf.database_id
where type = 1
GROUP BY d.name) as ldf on mdf.name = ldf.name

but alas...... It must be something else............ Where to look?

As far as I can see these columns are ints all the way, except during calculation. I tried changing all relevant columns, in- and outputs to float but no difference. Already in Query Builder the outcome fopr these columns is NULL where in SSMS the query runs just fine

Answer 1

My assumption is that you are running into a permissions issue on the connection with the NULL. – @billinkc 18 hours ago

Well, bugger..... That is actually it.... Turns out the account I used DOES have access to SYS.DATABASES but NOT to SYS.master_files.....

I have something to look into ;)

See the following threads

• How to give a user access to sys.master_files in SQL Server?
• SQL Server: sys.master_files vs. sys.database_files

I ended up creating a script that creates a job that creates a table that inserts the relevant columns from sys.master_files in a table on which the reporting user has db_datareader. It is that table I use in the query used in SYS

Answer 2

I rewrote the query and avoided all subqueries: this one did the trick:

select     @@SERVERNAME as Server, 
            getdate() as Meetmoment, 
            sysdb.name as Databasename, 
            sum(datafile.Size* 8 / 1024) as  DataFileSizeMB, 
            sum(logfile.Size* 8 / 1024) as LogFileSizeMB 
FROM SYS.DATABASES (NOLOCK) sysdb 
        left join SYS.master_files datafile on sysdb.database_id = datafile.database_id and datafile.type = 0
        left join SYS.master_files logfile on sysdb.database_id = logfile.database_id and logfile.type = 1
group by sysdb.name