Flat file destination columns data types validation

Question

A source database field of type INT is read through an OLE DB Source. It is eventually written to a Flat File Destination. The destination Flat File Connection Manager > Advanced page reports it as a four-byte signed integer [DT_I4].

This data type made me think it indicated binary. Clearly, it does not. I was surprised that it was not the more generic numeric [DT_NUMERIC].

I changed this type setting to single-byte signed integer [DT_I1]. I expected this to fail, but it did not. The process produced the same result, even though the value of the field was always > 127. Why did this not fail?

Some of the values that are produced are

1679576722
1588667638
1588667638
1497758544
1306849450
1215930367
1215930367
1023011178
1932102084 

Clearly, outside the range of a single-byte signed integer [DT_I1].

As a related question, is it possible to output binary data to a flat file? If so, what settings and where should be used?

Answer 1

Data types validation

I think this issue is related to the connection manager that is used, since the data type validation (outside the pipeline) is not done by Integration services, it is done by the service provider:

• OLEDB for Excel and Access
• SQL Database Engine for SQL Server
• ...

When it comes to flat file connection manager, it doesn't guarantee any data types consistency since all values are stored as text. As example try adding a flat file connection manager and select a text file that contains names, try changing the columns data types to Date and go to the Columns preview tab, it will show all columns without any issue. It only take care of the Row Delimiter, column delimiter , text qualifier and common properties used to read from a flat file. (similar to TextFieldParser class in VB.NET)

The only case that data types may cause an exception is when you are using a Flat file source because the Flat file source will create an External columns with defined metadata in the Flat file connection manager and link them to the original columns (you can see that when you open the Advanced editor of the Flat file source) when SSIS try reading from flat file source the External columns will throw the exception.

Binary output

You should convert the column into binary within the package and map it to the destination column. As example you can use a script component to do that:

public override void myInput_ProcessInputRow(myInputBuffer Row)
  {

  Row.ByteValues=System.Text.Encoding.UTF8.GetBytes (Row.name);

  }

I haven't try if this will work with a Derived column or Data conversion transformation.

References

• Converting Input to (DT_BYTES,20)
• DT Bytes in SSIS