0

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.

enter image description here

  • 1
    Can multiple people run this SSIS package concurrently? If not, then just use a permanent table (and simply truncate it after each run). If so, use a permanent table with a session id key (and delete your session's rows after every run). – Aaron Bertrand Jan 10 '17 at 16:59
1

Replace temp tables, if you can, with table variables. The metadata analysis is used against the last SELECT statement in the batch.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.