CODEWITHSUNDEEP
ssis
Christopher Garcia
Christopher Garcia

Reputation: 2554

What are the non-obvious causes of a data type mismatch while loading data in an SSIS package?

I'm very new to SSIS, so please bear with me. A developer gave me a SSIS package and asked me to create a scheduled job on our database server to run it. He says it runs on his development box but I'm seeing the job fail with the following data type mismatch error:

0xC020837F The data type of column "output column 'col1' does not match the data type "System.Byte[]" of the source column 'col1'"

I opened the package in Visual Studio, and in the Input and Output Properties of the item, it shows both the External Column and Output Column as being of data type database timestamp [DT_DBTIMESTAMP]. I checked the source column on the server and verified that it is a datetime column. Are there any other reasons this error could be thrown?

Upvotes: 1

Views: 2401

Answers (1)

Filip Popović
Filip Popović

Reputation: 2655

This looks like your source table definition is not the same on development and production environment. Since You didn't provide enough details about what kind of source component and what connection manager You use and what is your source query (maybe You CAST or CONVERT some data), we have to make some assumptions.

As stated in SSIS Error and Message Reference, error code 0xC020837F (-1071611009) has name DTS_E_ADOSRCDATATYPEMISMATCH and description:

The data type of "" does not match the data type "" of the source column "__".

From error name (DTS_E_ADOSRCDATATYPEMISMATCH) and error message part "System.Byte[]" I conclude that You are probably using ADO NET Source source component.

For a start check following: open properties of source component, uncheck particular column and check it again - this forces source component to refresh external and output - this trick works for oledb source it might help You also

If that doesn't help, check following links to see if some of your source data types map to System.Byte:

Probably, on either development or production environment, column is of timestamp, image, varbinary or some other type that maps to managed System.Byte[] but on the other it is not. Please recheck source tables definitions.

If this answer doesn't help You, please post create statements for your source tables as well as source query itself.

Upvotes: 1

Related Questions