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.
Best Answer
Replace temp tables, if you can, with table variables. The metadata analysis is used against the last SELECT statement in the batch.