SQL SSIS 错误：源的外部列与数据源列不同步；如何删除外部列？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24313478/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SSIS Error: External Column for Source out of sync with Data Source columns; How do I remove External Columns?
提问by Samsonite
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.
查询应输出特定的项目列表，以及商店信息和经理信息等信息。使用 Cursor 浏览各种不同管理级别的列表，选择相关信息，然后通过电子邮件将查询返回的地区/地区/商店内容发送给该人。
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:
我的问题是旅程的 SSIS 部分。虽然代码就像运行一样，但如果我单独运行 Itemdata.dtsx（以便查看错误），它会抛出错误：
"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."
“警告：组件“Sources-ItemData”(1) 的外部列与数据源列不同步。需要从外部列中删除外部列“RM_Email”(49)。外部列“SM_Email” (46) 需要从外部列中删除。外部列“DM_Email”(43) 需要从外部列中删除。
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.
这导致 SQL Server Mngt Studio 说它运行了，但电子邮件的内容只是表头；没有数据，无论我做什么，表头都不会改变。
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 >= 0 ' + 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"0\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
回答by Vikramsinh Shinde
Recheck column assignment between source and destination and in-between component as well.
重新检查源和目标之间以及中间组件之间的列分配。
Give it a try by setting data flow component's Properties, ValidateExternalMetadata to False.
通过将数据流组件的属性 ValidateExternalMetadata 设置为 False 来尝试一下。
Please have a look of thisas well.
请也看看这个。
回答by Turpan
It seems like you were changing the OLEDB Source or other type of source recently.
您最近似乎在更改 OLEDB 源或其他类型的源。
Thus, you must delete OLEDB Source and create a new one again. Then also delete the mapping of particular field, save, go back to mapping again and map it back. Than it should work fine.
因此，您必须删除 OLEDB 源并重新创建一个新源。然后也删除特定字段的映射，保存，再次返回映射并将其映射回来。比它应该工作正常。
