SSIS Issue -- Related to #Temp Table Referencing

I am completely new to SSIS.I am using SQL Server 2014. I am not able to understand what is wrong with code as it results good in SSMS. I use OLEDB connection as source assistant and input the below code in SQL Command option. I am using the below query as part of exporting source T-SQL output to Flat file. I am getting below issue while creating a SSIS package. can anyone help me on this. Below is the code I use (I modified for finding complete backup Info purpose) : if object_id ('tempdb..#temp1') is not null drop table tempdb..#temp1 create table #temp1 ([server] nvarchar(200), [name] nvarchar(200), [last full backup] datetime , [last differential backup] datetime, [last log backup] datetime ) insert into #temp1 SELECT  (select @@SERVERNAME) as Server,name , d AS 'Last Full Backup' ,             i AS 'Last Differential Backup' ,             l AS 'Last log Backup'     FROM    ( SELECT    db.name ,                         db.state_desc ,                         db.recovery_model_desc ,                         type ,                         backup_finish_date FROM      master.sys.databases db                         LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name             ) AS Sourcetable          PIVOT              ( MAX(backup_finish_date) FOR type IN ( D, I, L ) ) AS MostRecentBackup if object_id ('tempdb..#temp2') is not null drop table tempdb..#temp2 create table #temp2 ([server] nvarchar(200), [name] nvarchar(200), Backupsize nvarchar(max)) insert into #temp2 ------------------------------------------------------------------------------------------- --Most Recent Full Database Backup for Each Database - Detailed ------------------------------------------------------------------------------------------- SELECT A.[Server] As SQLServerName, A.database_name As DatabaseName, --A.last_db_backup_date As DatabaseLastBackupDate, -- B.backup_start_date As DatabaseBackupStartDate, CAST(B.backup_size/1024.0/1024/1024 as decimal(10,2)) --B.backupset_name FROM ( SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'D' and server_name = (select @@servername) AND name = 'CommVault Galaxy Backup' GROUP BY msdb.dbo.backupset.database_name ) AS A LEFT JOIN ( SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, msdb.dbo.backupset.backup_size, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'D' ) AS B ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] ORDER BY A.database_name if object_id ('tempdb..#tempdiff') is not null drop table tempdb..#tempdiff create table #tempdiff ([server] nvarchar(200), [name] nvarchar(200), DiffBkpGB nvarchar(MAX), backupset_name nvarchar(max)) insert into #tempdiff SELECT A.[Server] As SQLServerName, A.database_name As DatabaseName, --A.last_db_backup_date As DatabaseLastBackupDate, --B.backup_start_date As DatabaseBackupStartDate, --B.backup_size/1024/1024 as DifferentialBackupSizeGB, CAST(B.backup_size/1024.0/1024/1024 as decimal(10,3)) as DiffBkpGB , B.backupset_name FROM ( SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'I' and server_name = (select @@servername) AND name = 'CommVault Galaxy Backup' GROUP BY msdb.dbo.backupset.database_name ) AS A LEFT JOIN ( SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, msdb.dbo.backupset.backup_size, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'I' ) AS B ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] ORDER BY A.database_name if object_id ('tempdb..#templog') is not null drop table tempdb..#templog create table #templog ([server] nvarchar(200), [name] nvarchar(200), LogBkpGB nvarchar(MAX) ) insert into #templog ------------------------------------------------------------------------------------------- --Most Recent Log Database Backup for Each Database - Detailed ------------------------------------------------------------------------------------------- SELECT A.[Server] As SQLServerName, A.database_name As DatabaseName, --A.last_db_backup_date As DatabaseLastBackupDate, --B.backup_start_date As DatabaseBackupStartDate, --B.backup_size/1024/1024 as LogBackupSizeGB, CAST(B.backup_size/1024.0/1024/1024 as decimal(10,3)) AS LogBkpGB --B.backupset_name FROM ( SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'L' and server_name = (select @@servername) AND name = 'CommVault Galaxy Backup' GROUP BY msdb.dbo.backupset.database_name ) AS A LEFT JOIN ( SELECT CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, msdb.dbo.backupset.database_name, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date, msdb.dbo.backupset.expiration_date, msdb.dbo.backupset.backup_size, msdb.dbo.backupset.name AS backupset_name, msdb.dbo.backupset.description FROM msdb.dbo.backupmediafamily INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id WHERE msdb..backupset.type = 'L' ) AS B ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date] ORDER BY A.database_name select (select getdate()) as CurrentDateTime,#temp1.server, #temp1.name as Database_Name, #temp1.[last full backup] as Last_Full_Backup, #temp2.Backupsize as FullBkpGB, #temp1.[last differential backup] as Last_Differential_Backup,#tempdiff.DiffBkpGB, #temp1.[last log backup] as Last_Log_Backup, #templog.LogBkpGB, #tempdiff.backupset_name from #temp1 left join #temp2 on #temp1.server = #temp2.server and #temp1.name = #temp2.name left join #tempdiff on #temp1.server = #tempdiff.server and #temp1.name = #tempdiff.name left join #templog on #temp1.server = #templog.server and #temp1.name = #templog.name When I am working on creating a new SSIS package I get the following issue. Please help.