Raw File Source and Destination in SSIS

The Raw file is a native format of the SSIS. The Raw File Source and Destination is a fast way of moving data between data flows, and can also be used when having to stage data to avoid slow writes to other locations.

The use of raw files as SSIS data source is highly recommended for huge volume of data due to its superior read/write performance. Raw files store data in a very basic format that requires almost zero translation and parsing. This enables faster data reads/writes when compared to Flat Files and even faster than Database tables.

Raw file Destination

First I am going to create a raw file. In this file I am store the table data. Let’s see how to create the Raw file destination. Farther I use this file as source.

Taking Data Flow task

 

Double click the DFT.

Taking the OLEDB source from here I am reading the data.

Now I am creating the connection.

 

Click on ok.

Taking Raw File destination.

 

Double click on the Raw File destination transformation.

 

See the access mode.

We can take file name directly or through variable. Better to use File name from variable because you can configure the file name in configuration file.

File Name Access Mode

  

File Name from Variable Access Mode

If you are using File Name from Variable Access mode. Need to create a variable.

 

You can use expression. As you wish.

 

Now we need to map the columns.

 

Now we need to generate initial RAW file.

   

Click on the Generate initial raw file

 

Now most important option we need to set “Write option”. I mean how you want to write this RAW file.

 

Create Always: - Every time creating the RAW file with the same name.

Create Once: - First time when we run the package it will create the RAW file. But when we will be running the package it will throw the error.

Append: - Every time appending the value in the raw file.

Truncate and append: - Truncates an existing file and then writes the data to the file. The metadata of the appended data must match the file format.

Select any of the above as per your requirement. Click on OK.

Now Package is ready to run. I am running the package.

 

Package executed successfully. Now I am seeing the RAW file.

 

That is not a human readable format.

RAW File Source

Now I am going to use this RAW file as Source and loading this data in the database.

Step by step to using RAW file Source.

 

Double Click on the Data Flow task.

Take RAW File Source.

Create the connection.

Access Mode is similar to RAW File destination. Either file name directly or use variable.

 

Map the columns.

 

Click ok.

Now I am taking the OLEDB destination to load the data in the database.

Create the connection.

 

Map the columns.

Click Ok.

Package is ready to run. Now I am executing the package.

 

Package executed successfully.

Now I am seeing the table.

 

Got the expected result.