SSIS Components: Source Output ColumnName Does Not Match the Data Type "System.String" of the Source Column

Original error message is:
Source Output columnName does not match the data type "System.String" of the source column

Normally, this would typically depend on the type of source component.

For instance, non-static components like SharePoint will behave differently from Facebook components, which use a set of static metadata files.

The SharePoint SSIS components dynamically determine the fields' data types every time you run the CData SSIS components by querying the live metadata from the server. The table definitions are dynamically obtained based on your SharePoint site. Any changes you make, such as adding custom fields or changing a field's data type, are reflected at connect time.

Troubleshooting Steps That May Help Resolve the Issue:

• The VS_NEEDSNEWMETADATA error shows up when the underlying data behind one of the tasks changes. You can try quickly resetting this cache. To do so, right-click your CData Source Component, click 'Advanced editor,' then click the 'Refresh' button at the bottom. Update any remaining tasks that depend on data that may no longer exist.
• You can try to delete the source component and recreate it (normally that will refresh the metadata.)
• Ask yourself: am I actually using this field in the data flow? If not, you may want to consider writing a custom SQL query that only SELECTs the columns you are interested in, rather than pulling the entire columns from the table. This should minimize the chance something changes in the data types of the fields you are using.
• Additionally, you should be able to manually adjust the column's data type by right-clicking the source component and then open the "Advanced Editor." On the 'Inputs and Outputs' tab, you can change the data type of the output column to a valid type.
• It can happen due to different user profiles accounts that connect to development and production environments. If the user profiles have different security settings on fields visibility, it is possible for the fields mapping to cause this error.
• If none of these options have helped, there are a couple hidden connection properties that will allow you to cache the metadata for the source component so it is not retrieved dynamically. However, you would want to be aware that this can cause its own problems if the type is stored incorrectly in the local cache. To enable the metadata caching, there are two properties that will need to be set in the Other field in the connection manager. These are:
  • Cache Metadata
  • Cache Location
  They would be set in Other like so: Other: CacheMetadata=True;CacheLocation=C:\Users\Public\metadata.db; You can, of course, set any path you like here. This will store the metadata in a local SQLite database as soon as you make the first connection to get metadata with the components.