SSIS Error: External Column for Source out of sync with Data Source columns; How do I remove External Columns?

Question

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

Answer 1

I had this error when I added an identity column to all of my tables. That column must not be filled, but it must still be mapped, else you have the yellow exclamation mark in each destination box. The only thing I had to do was to enter each of these boxes, go to mapping so that the new column on the right would be mapped to "ignore" on the left, which was default and which just needs to be refreshed, and then close with "OK". This is tedious if you have dozens of destinations, but easy work. If you have far too many destinations, try automating the refresh like the other answer that asks you to search and replace with a text editor.

Answer 2

Double click on " Flat file Storage" --->

The metadata of following output column not match metadata of external column with which output column are associated

Do you want replace metadata of output column with metadata of external columns?

Answer 3

I realize this is an older question, but I have a text file that is 458 columns wide. SIGH. Resetting Columns, etc., could be tedious and in this case, I only had one column that was updated post-configuration in the Flat File Connection Manager. The data type length shows as 13 and warns on synchronization to the data source which shows a length of 12.

Here is what I did because I did not want to Reset Columns and none of the other methods worked or would cause me too much heart-ache/ time wasted:

1. Close the project in Visual Studio.

2. Save a copy of the project in case you fat-finger something.

3. Open the project file in something like NotePad++

4. I searched for the Flat File Source string in the project. This is to get a template of what to search for in the next step.

5. In my case, the connection manager flat file data source is configured and looks like so: Package\Import Data\Flat File Source.Outputs[Flat File Source Output].Columns[MyColumn_1a]

6. Now, use that template to search for the offending column:
   Package\Import Data\Flat File Source.Outputs[Flat File Source Output].Columns[MyColumn_238b] Look at the length (the property reporting to be out of sync in my case) and update accordingly.

   refId="Package\Import Data\Flat File Source.Outputs[Flat File Source Output].Columns[EXAMT_1]"
   codePage="1252"
   dataType="str"
   errorOrTruncationOperation="Conversion"
   errorRowDisposition="FailComponent"
   externalMetadataColumnId="Package\Import Data\Flat File Source.Outputs[Flat File Source Output].ExternalColumns[EXAMT_1]"
   length="12"  <<<-----  UPDATED THIS FROM 13 TO 12 
   lineageId="Package\Import Data\Flat File Source.Outputs[Flat File Source Output].Columns[EXAMT_1]"
   name="EXAMT_1"
   truncationRowDisposition="FailComponent">
    <properties>
     <property
     dataType="System.Boolean"
     description="Indicates whether the column uses the faster, locale-neutral parsing routines."
     name="FastParse">false</property>
     <property
     dataType="System.Boolean"
     description="Indicates whether the data is in binary format."
     name="UseBinaryFormat">false</property>
    </properties>
   </outputColumn>```
   
   

7. Save the file (in NotePad++), and close the file.

8. Open the file in Visual Studio and check for error.

Answer 4

Rather than deleting and recreating:

1. Copy the existing Data Source.
2. Paste it back into the Data Flow Task. The pasted Data Source has a refreshed schema. You've preserved any downstream mappings that are possible to keep. You now face the least possible maintenance.
3. Delete the connector from the old Data Source.
4. Substitute the connector from the new Data Source.
5. Maintain mappings, if necessary, in the Data Destination.

Answer 5

I had also same issue where all my column names were same but still getting the "The external columns for 'excelName' are out of synchronization with the data source columns. The column "mycolname" needs to be added to the external columns. The column "F62" needs to be added to the external columns." I have validated many time as well but no luck,even I matched my column header with destination table as well in excel formula and all seems to correct.So manually one by one column I checked and finally found that the file I have used for my connection initially had first letter in capital "Mycolname" , where as the file I was using (I used to overwrite the source file ) I just pasted data from some other file due which my header got changed. I changed the header again my input file and it worked.

Note : Be careful while using SSIS...really dangerous.

Answer 6

It seems like you were changing the OLEDB Source or other type of source recently.

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.

Answer 7

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.

Please have a look of this as well.