SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

31 Days of SSIS – Raw Files Are Awesome (1/31)

31 Days of SSIS

As I mentioned in the introductory post, I’m writing 31 Days of SSIS.  Let’s start off this series with a SSIS file format that I was unaware of until just over a year ago.  As the title already gives away – this file format is the raw file format.  And it is awesome!

You’ve heard about this file format before, you say?  Unfortunately, most people that I talk about SSIS with haven’t heard of it or know when to apply it to there solutions.  So the purpose for today is to define, describe, and demonstrate.

Raw File Format

Starting with the definition, the raw file format is the data format that is native to SSIS.  When data is being consumed by the data flows of an SSIS package it has a format.  This format is the same that appears in raw files.

Since these are the same file format, SSIS doesn’t have to parse or translate the data between the data streams or the raw file format.  This results in the raw file format being faster than most other data source and destination formats.  For some statistics on the speed benefit see this post by John Welch (blog | @John_Welch).

The raw file format can be incredibly useful.  It can be used for either a data source or a data destination.  Once you’ve gotten your hands wet with them, you’ll find a great many uses.  Some of which may solve issues that you’ve run into with previous development projects.

For instance if you want to build an SSIS package that processes data in multiple steps, temporary versions of the data can be stored in raw files and passed between data flows.  This will allow packages to be built such that they can be restarted at different points in the data processing. 

Similar to the previous example, raw files can be used to exchange data between SSIS packages.  You can develop one package to convert a clients file to a standardized structure in the raw file format.  And then use a couple other packages to read that format in the next packages that process the data in a standard format.  All of this can be done without hitting the database.

Another thing you can do is export data from a database to a raw file to use a reference file for multiple SSIS packages.  If you need to cycle through a group of IDs or states multiple time, store the data in a raw file and use it as a data cache.  Sure you can use a Lookup Cache instead – but that doesn’t fit all scenarios and not everyone is using SQL Server 2008.

Let’s move onto a couple demonstrations

Raw File Format Destination

First off, let’s build an SSIS package that uses a Raw File as destination.  We’ll fast forward through adding the Data Flow and the OLE DB Source.  The query for the source doesn’t matter for this demonstration so we’ll skip over that too.

Let’s begin.  Start by dragging a Raw File Destination onto the the Data Flow and connecting the OLE DB Source to it.  The package should look like this:


From here, open the Raw File Destination so that we can configure it.  The first thing you will need to configure is the location for the raw file.  This can either be a static value or tied to a variable.  In most cases I use variables to improve configurability between environments


Next you’ll configure how you want the Raw File Destination to behave when it writes.  The write option selection is important since it will have significant impact on how the destination is populated


There are a few options for this, they are (definitions from Books Online):

  • Append:  Appends data to an existing file. The metadata of the appended data must match the file format.
  • Create always: Always creates a new file.
  • Create once: Creates a new file. If the file exists, the component fails.
  • 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.

Missing from this list is a Create First Time or Create Metadata option.  As you begin to use the file format more, you’ll realize that this is an important option to have.  Please, help out by voting for a metadata option for raw files.

At this point, you can move to the next screen and select the columns you need.  There is a select all checkbox for SQL Server 2008 and forward, which is quite handy.  For SQL Server 2005, you need to select all of the columns individually.


One extremely nice thing about the Raw File Destination is that the order of the columns in it don’t matter.  Whether you add a column now or in six months it’s position in the raw data file doesn’t matter.  Or if you forget to add a column that data source expects and it is setup for the source as the first column.

Execute the package and you will have a Raw File created.

Raw File Format Source

Now that we have a file to work with, let’s create a quick package that can import some data from a raw file.  As before, we’ll skip going through adding the Data Flow and then Raw File Source and Row Sampling task.  The Row Sampling task is just there to provide a place to add a data viewer if you desire to see the data that is returned.


Configuration for the the Raw File Source is similar to the Raw File Destination.  Open up the data source and first you determine the location for the raw file.  Same options as above with either be a static value or tied to a variable.


Next you’ll select the columns that you want returned.  They can be selected through the checkboxes or by typing their names in the grid below.  The column names can also be changed at this point in the Output Column column.


At this point the Raw File Data Source is configured.  It knows the underlying data so there isn’t any need to call up and discuss this with the person that created the raw file, if it isn’t you.  The steps are simple and not likely to get you bogged down in details – since there really aren’t any.

Raw File Wrap-Up

Now that we’ve got through this far, I hope that you see some of the value is using the raw files.  I’ve got a couple upcoming posts in this series that will go deeper into their actual use, but for now this should get you started.

One bit of advice that I’ll leave you with.  After the data types for you data source or data destination are set it is wise to change the ValidateExternalMetadata property to False.  This prevents some pesky data type and file availability issues that can come up during validation and helps prevent accidentally changing data types.



Using RAW Files for Staging Data in SSIS

How to Read Raw Files in SSIS

Related posts:

  1. Connect Item on Import/Export Wizard
  2. 31 Days of SSIS – The Introduction
  3. Create Empty Raw File


Posted by Anonymous on 2 January 2011

Pingback from  Twitter Trackbacks for                 SQL Server Central, 31 Days of SSIS ??? Raw Files Are Awesome (1/31) - StrateSQL         [sqlservercentral.com]        on Topsy.com

Posted by Anonymous on 2 January 2011

Pingback from  Power Plant at Dusk | Buy Industrial Workbench

Posted by randy.leonard on 5 January 2011

Great article, but unfortunetly none of the images associated with this article were displayed.  Any idea why.

Posted by dbowlin on 5 January 2011

I have been aware of RAW files for some time now.  I have always heard they were faster in many cases.  Your article certainly helps my understanding of the configuration of RAW files.  I am looking forward to an article on the best times and places to use RAW files.  Thanks.

Posted by Jason Strate on 5 January 2011

@randy.leandard - try again.  All of the images are hosted on a remote site and I think that got killed this morning.  I may need to put the images up on flickr.

Posted by Jason Strate on 5 January 2011

@dbowlin Your welcome.  There are actually two of them on this that are coming up shortly.  Maybe tomorrow and the day after.

Posted by s.n.scott on 5 January 2011

After following the directions in this article i end up with an issue with the SSIS package, i  extract the data to RAW file as destination, but the source table contains a column with data type varbinary(max).

I am getting this error:

Error at Data Flow Task - TestSSIS [Attachments_Raw [8237]]: The column attachment_file cannot be used. The raw adapters do not support image, text, or ntext data.

Can anyone assist as i would like to follow the 31 days of ssis and getting stumped on day one is discouraging ;)

Posted by ChrisPapps on 5 January 2011


See data destination link in the fifth paragraph - BLOB data type not supported in raw file destinations. No surprises there.


Posted by s.n.scott on 5 January 2011

Thanks Chris i get the same error with varchar(max) data type also

Posted by chheatry on 5 January 2011

good one

Posted by Anil Maharjan on 6 January 2011

Hello Jason,

It's one of the nice blog post and i am dying to read and learn more about SSIS from these 31DaySSIS blog posts.

Really a nice work ..keep on.. :)


Anil Maharjan

Posted by Anil Maharjan on 6 January 2011

Hello all,

well i have a problem while reading the Raw file using Raw file reader tool .I got following error "Could not load file or assembly 'Microsoft.SqlServer.ManagedDTS, Version=, Culture=neutral, PublicKeyToken=89845dc8080cc91' or one of its dependencies"

what's the error trying to say ,i dont have any idea ,may be its installation or version problem though i am using SQl Server 2008 and Visual Studio 2008 .


Anil Maharjan

Leave a Comment

Please register or log in to leave a comment.