0

I have created a console app in SSDT VS 2017 which reads a list of tables based on a SQL query and generates an SSIS package to transfer the records to another server. A few of the tables have columns which contain country names with special characters in the column name. The package is generated successfully and when I open the package using SSDT I am able to see that all the mappings were done successfully, but upon executing the package I receive the following error:

Load OleDb Table:Warning: The external columns for OleDb Destination OleDb Table are out of synchronization with the data source columns. The column "CÔTE_DIVOIRE" needs to be added to the external columns.
The column "RÉUNION" needs to be added to the external columns.
The column "SAINT-BARTHÉLEMY" needs to be added to the external columns.
The OleDb Destination OleDb Table.Inputs[OLE DB Destination Input].ExternalColumns[SAINT-BARTHÉLEMY] needs to be removed from the external columns.
The OleDb Destination OleDb Table.Inputs[OLE DB Destination Input].ExternalColumns[RÉUNION] needs to be removed from the external columns.
The OleDb Destination OleDb Table.Inputs[OLE DB Destination Input].ExternalColumns[CÔTE_DIVOIRE] needs to be removed from the external columns.

My code is based on API Sample - OleDb Source and OleDb Destination

The code that performs the mapping is as follows:

IDTSVirtualInput100 destVirInput = destInput.GetVirtualInput();
IDTSInputColumnCollection100 destInputCols = destInput.InputColumnCollection;
IDTSExternalMetadataColumnCollection100 destExtCols = destInput.ExternalMetadataColumnCollection;
IDTSOutputColumnCollection100 sourceColumns = srcComponent.OutputCollection[0].OutputColumnCollection;
// The OLEDB destination requires you to hook up the external columns
foreach (IDTSOutputColumn100 outputCol in sourceColumns)
{
    // Get the external column id
    IDTSExternalMetadataColumn100 extCol = (IDTSExternalMetadataColumn100)destExtCols[outputCol.Name];
    if (extCol != null)
    {
        // Create an input column from an output col of previous component.
        destVirInput.SetUsageType(outputCol.ID, DTSUsageType.UT_READONLY);
        IDTSInputColumn100 inputCol = destInputCols.GetInputColumnByLineageID(outputCol.ID);
        if (inputCol != null)
        {
            // map the input column with an external metadata column
            destDesignTimeComponent.MapInputColumn(destInput.ID, inputCol.ID, extCol.ID);
        }
    }
}
AndreL
  • 11
  • 3
  • Does this help? https://stackoverflow.com/questions/2393887/how-to-replace-special-characters-with-their-equivalent-such-as-%C3%A1-for-a – ppijnenburg Oct 17 '18 at 09:59
  • What happens if you manually create an SSIS package and attempt to route data into a column that has unicode characters in it? What is the SSIS designer behaviour? You might have to hit F7 and scan through the resulting XML to try and see how they handle it. Also, [Biml](http://stackoverflow.com/questions/tagged/biml?sort=newest&pageSize=50) might be an option for auto-generating SSIS packages - I'm fan versus the managed object model API, EzAPI, etc. – billinkc Oct 17 '18 at 12:48
  • @billinkc Thanks for the suggestion of using BIML. I'm busy having a look at that right now. When I created the package manually (I cannot believe that I had not tried this) it also failed after being deployed. It does work fine from my machine though. – AndreL Oct 18 '18 at 08:02

0 Answers0