Recently I ran into the following error when compiling BIML into SSIS packages:

The message itself is not very revealing:

The data type for input buffer column ‘True’ on the ScriptComponent input buffer ‘Input0’ does not match the mapped output column from the previous transformation.

You might have guessed it already; there is no column called True in the data flow. A quick Google search brought back this topic on the Varigence forums: Error: Script Transformation. However, uninstalling BIDS Helper and installing an older version (and possible lose other functionality) is not an option. Besides, the BIML code worked when I generated the package for another table, so BIDS Helper was not to blame.

After a quick debug using Messageboxes – so old school and it is possible in BIMLScript by the way – I confirmed it had nothing to do with the C# code generating the BIML.

So let’s take another good look at the error message. It mentions something about data types not being correct. All the data types were simple varchars, numerics and datetimes and I was sure I mapped them to the corresponding DBType. After all, the code works for other tables.

And then it hit me: the destination table had some columns with a greater length than the source column. For example varchar(250) instead of varchar(213). I enlarged them to deal with the risk of other sources being added with larger columns. In the SSIS data flow, the metadata of the columns is derived from the columns returned by the source query. However, in the script component I specified the metadata of the destination columns, all having another length. I changed the lengths of the destination columns, lo and behold, the BIML script compiled successfully. Apparently SSIS (or BIML) has some serious issues of the metadata of an input column doesn’t match 100% the metadata of the output column of the previous component.

If now only one of the kind BIML developers would substitute true with the actual column name, life would be much easier.