Reading a binary file with SSIS

Question

We have a binary file (from a telecom system). Can SSIS read it?

From what I have read about Raw File Source, it can only read files created by Raw File Destination. Is that correct?

What type of file is it? You can use a Script task (C#) read it into an object, and then identify what type of file it is and use c# to read the data into a datatable or some other object to insert into SQL if that is what you are trying to do — Brad, Mar 04 '19 at 20:24
I think if you read the official documentation you will get the answer https://learn.microsoft.com/en-us/sql/integration-services/data-flow/raw-file-source?view=sql-server-2017 — Yahfoufi, Mar 05 '19 at 11:04

score 1 · Answer 1 · answered Mar 04 '19 at 20:30

1

Yes, the Raw File in SSIS parlance is a proprietary binary format aimed at primarily reducing speed of load, portability and size which can only be produced and consumed by the SSIS engine.

To be able to read a binary file in SSIS it needs to be pre-processed. What I mean is that you need to obtain documentation on what is the proper procedure to extract data from it.

answered Mar 04 '19 at 20:30

Arthur

1,441
1
13
17

We have full documentation on the binary file. We are clear about which fields are 2-byte integers, byte order (endianness), which are ASCII encoded text, and which bytes represent bit fields. It looks like we will need to write all of the code to produce fields to provide to SSIS. – lit Mar 04 '19 at 21:54
@Hadi - I think that is what I said. SSIS cannot read the file. We will need to write code to produce fields or a file that SSIS can understand. – lit Mar 04 '19 at 22:51
@lit oh i misunderstood it. Good luck – Hadi Mar 05 '19 at 00:57

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

Raw File Source

From the Raw file source official documentation::

The Raw File source reads raw data from a file. Because the representation of the data is native to the source, the data requires no translation and almost no parsing. This means that the Raw File source can read data more quickly than other sources such as the Flat File and the OLE DB sources.

The Raw File source is used to retrieve raw data that was previously written by the Raw File destination. You can also point the Raw File source to an empty raw file that contains only the columns (metadata-only file). You use the Raw File destination to generate the metadata-only file without having to run the package

You can check an exmaple of the usage of Raw files at the link below:

SQL SERVER – Using Raw Files in SSIS

Reading binary data

I think in SSIS, you should use a Script Component as source (Within a data flow task) to deserialize binary and implement your own logic to generate output columns.

You can check the following links to get more insights:

Script component as Source

Converting binary to string using C#

score 0 · Accepted Answer · edited Mar 05 '19 at 00:55

The answer at this time is that SSIS cannot read a binary file that is not formatted in the same way as a file produced by Raw File Destination.

You could use create a Custom SSIS Component Class and register it for use in SSIS.
Developing a Custom SSIS Source Component Not for the faint of heart.

Another way would be to write a program to convert the binary file into a file that can be read by SSIS or load the output into a database table. So much for what SSIS can do for you.

Reading a binary file with SSIS

3 Answers3

Raw File Source

Reading binary data