Query should output a certain list of Items, along with info like store information and manager info. Uses a Cursor to flip through list of various different levels of management, selects relevant information, then emails that person what the query returned for their district/region/store.
My issue is with the SSIS leg of the journey. Although the code acts like it runs, if I run Itemdata.dtsx separately (so as to see errors), it throws me the error:
"Warning: The external columns for component "Sources-ItemData"(1) are out of sync with the data source columns. The external column "RM_Email"(49) needs to be removed from the external columns. The external column "SM_Email"(46) needs to be removed from the external columns. The external column "DM_Email"(43) needs to be removed from the external columns."
This results in SQL Server Mngt Studio saying it ran, but the emails' contents are nothing but the table headers; no data, and the table headers don't change regardless of what I do.
I have eliminated these columns from any mention in my source code (posted below) and in the Table it uses. What am I missing?
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
