Kueri harus menampilkan daftar Item tertentu, bersama dengan info seperti informasi toko dan info manajer. Menggunakan Kursor untuk menelusuri daftar berbagai tingkat manajemen, memilih informasi yang relevan, lalu mengirimkan email kepada orang tersebut mengenai kueri yang dikembalikan untuk distrik/wilayah/toko mereka.
Masalah saya ada pada bagian perjalanan SSIS. Meskipun kodenya berfungsi seperti dijalankan, jika saya menjalankan Itemdata.dtsx secara terpisah (untuk melihat kesalahan), saya mendapat kesalahan:
"Peringatan: Kolom eksternal untuk komponen "Sources-ItemData"(1) tidak sinkron dengan kolom sumber data. Kolom eksternal "RM_Email"(49) perlu dihapus dari kolom eksternal. Kolom eksternal "SM_Email" (46) perlu dihapus dari kolom eksternal. Kolom eksternal "DM_Email"(43) perlu dihapus dari kolom eksternal."
Hal ini menyebabkan SQL Server Mngt Studio mengatakan itu berjalan, tetapi isi email tidak lain hanyalah header tabel; tidak ada data, dan header tabel tidak berubah apa pun yang saya lakukan.
Saya telah menghilangkan kolom-kolom ini dari penyebutan apa pun di kode sumber saya (diposting di bawah) dan di Tabel yang digunakannya. Apa yang saya lewatkan?
BEGIN
SET NOCOUNT ON;
DECLARE @ProcedureName varchar(255)
DECLARE @ExportFolder varchar(255)
DECLARE @TempFolder varchar(255)
DECLARE @WarningLevel varchar(255) = 'log'
DECLARE @MsgDescription varchar(2000) = ''
DECLARE @RecordCount int = 0
DECLARE @ReportDate date = getdate()
DECLARE @Begdate date = convert(date,(dateadd(month,-1,getdate())))
DECLARE @Enddate date = convert(date,(dateadd(day,-1,getdate())))
DECLARE @Siteid int
DECLARE @Subject varchar(75) = ''
DECLARE @Body varchar(50) = ''
DECLARE @RMLastName varchar(25)
DECLARE @RMFirstName varchar(25)
DECLARE @RegionKey int
DECLARE @DistrictKey int
DECLARE @Email varchar(50)
BEGIN TRY
--Table used as data source for each pass
Truncate Table Example.dbo.itemdata
--Send reports to Regional Managers by building Cursor RMListCursor,
--then running SELECT statement against each name (using @RMLastName and @RMFirstName to discern),
--then emails results of SELECT statement to that Regional Manager.
--Goes through CursorList, then ends.
BEGIN
--Set cursor for RM Email; returns all regional managers.
DECLARE RMListCursor CURSOR FOR
SELECT distinct t.lastname, t.firstname, t.Email
FROM Example.[dbo].[tblUser] t
JOIN example.dbo.vStoreDistrictRegionActive vs
ON t.LastName = vs.RMLastName and t.FirstName = vs.RMFirstName
ORDER BY LastName
OPEN RMListCursor
FETCH NEXT FROM RMListCursor
INTO @RMLastName
, @RMFirstName
, @Email
WHILE @@FETCH_STATUS = 0--(@SetInt < 6)
BEGIN
Truncate table Example.dbo.itemdata
--Builds data, then inserts into Table built for this query. Note that there is no mention of DM_EMAIL, RM_EMAIL, or SM_EMail anywhere.
INSERT INTO Example.dbo.itemdata
SELECT InvoiceNumber,
shipFROMid,
ad.SiteId,
MfrCode,
PartCode,
UnitNetCore,
ad.QuantityShipped,
ShipDate,
--First/Last Name of this item's store's District Manager.
rtrim(substring((SELECT ISNULL(DMfirstName,'') FROM example.dbo.vSiteRegionDistrictActiveV2 dm WHERE ad.siteid = dm.SiteNumber),1,30)) + ' ' +
substring((SELECT ISNULL(DMLastName,'') FROM example.dbo.vSiteRegionDistrictActiveV2 dm WHERE ad.siteid = dm.SiteNumber),1,30) DM
--This is where DM_EMAIL, RM_EMAIL, and SM_EMail originally were before they were removed from both here and .ItemData.
FROM example.dbo.vInvoiceHeaderDetail_Adis ad
join example.dbo.Site ss on ad.SiteId=ss.siteid
join example.dbo.vStoreDistrictRegionActive vs on ad.SiteId = vs.SiteId
WHERE ad.siteid is not null and UnitNetCore>=250 and SUBSTRING(InvoiceNumber,2,1)='D' and QuantityShipped>0
and isactive=1 and isowned=1
and ShipDate between @Begdate and @Enddate
and vs.RMFirstName = @RMFirstName
and vs.RMLastname = @RMLastName
ORDER BY ad.SiteId,ShipFROMID,shipdate
-- Execute SSIS package which downloads table to d: for email.
set @RecordCount=@@ROWCOUNT
--Quick check so that if the results were blank, don't bother sending a blank email.
IF @RecordCount<>0
BEGIN
set @Subject = 'Cores billed from PWI >= $250 ' + cast(CONVERT(date,GETDATE()) as varchar(12))
set @Body = 'Run date/time- ' + cast(GETDATE() as CHAR(20))
EXEC xp_cmdshell 'd:\"Program Files (x86)"\"Microsoft SQL Server"\100\DTS\Binn\DTexec.exe /f "D:\etl\bulk\ssis\Misc\ItemInfo.dtsx"'
EXEC msdb.dbo.sp_send_dbmail
@profile_name ='SQL Mail',
@recipients ='test', --@email
@subject = @Subject,
@body = @Body,
@body_format = 'HTML',
@File_attachments = 'D:\export\temp\ItemInfo.xls',
@attach_query_result_as_file =0,
@query_attachment_filename='\ItemInfo.xls',
@query_result_width = 500
END
--Brings CURSOR back up to next name on List, repeats process.
FETCH NEXT FROM RMListCursor
INTO @RMLastName
, @RMFirstName
, @Email
END
END
CLOSE RMListCursor
DEALLOCATE RMListCursor
END TRY
BEGIN CATCH
SET @WarningLevel = 'error'
SET @MsgDescription = 'SQL Err= [' + CAST(ERROR_MESSAGE() as varchar(200)) + ' (' + CAST(ERROR_LINE() as varchar) + ')]'
EXEC example.dbo.spAddSysMessage 'Store Aging', @WarningLevel , @ProcedureName , '', 'EOM Store Aging Report', @RecordCount
END CATCH
END
