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

Which column in your query is now returning a `NULL`? You state you're iterating through a server list, which server are you connected to when the query generates a NULL? My assumption is that you are running into a permissions issue on the connection with the NULL. — billinkc, Jan 07 '14 at 16:36
It are both number columns. It also goes wrong on the server it worked originally — Henrov, Jan 07 '14 at 16:48

score 1 · Accepted Answer · edited May 23 '17 at 12:03

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

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

score 0 · Answer 2 · answered Jan 08 '14 at 09:18

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

Unfortunately, it ran only in the query builder, after executing the package I get NULLS again...... It has to have to do anything with the destination column..... — Henrov, Jan 08 '14 at 09:41

Using subquery in SSIS results in NULL values

2 Answers2