SSIS 错误:源的外部列与数据源列不同步；如何删除外部列? [英] SSIS Error: External Column for Source out of sync with Data Source columns; How do I remove External Columns?
问题描述
Query 应该输出特定的项目列表，以及商店信息和经理信息等信息.使用 Cursor 浏览各种不同管理级别的列表，选择相关信息，然后通过电子邮件将查询返回的地区/地区/商店内容发送给该人.
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.
我的问题是旅程的 SSIS 部分.虽然代码就像运行一样，但如果我单独运行 Itemdata.dtsx(以便查看错误)，它会抛出错误:
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:
警告:组件Sources-ItemData"(1) 的外部列与数据源列不同步.需要从外部列中删除外部列RM_Email"(49).外部列需要从外部列中删除SM_Email"(46).需要从外部列中删除外部列DM_Email"(43)."
"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."
这导致 SQL Server Mngt Studio 说它运行了，但电子邮件的内容只是表头；没有数据，无论我做什么，表头都不会改变.
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
推荐答案
重新检查源和目标之间以及中间组件之间的列分配.
Recheck column assignment between source and destination and in-between component as well.
通过将数据流组件的属性、ValidateExternalMetadata 设置为 False 来尝试一下.
Give it a try by setting data flow component's Properties, ValidateExternalMetadata to False.
请看一下this 也是如此.
这篇关于SSIS 错误:源的外部列与数据源列不同步；如何删除外部列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
