SSIS Add Filename During File Load

When loading files into staging tables using SSIS, it is often helpful to include the filename during the load so you can verify where the data came from.  This is easily accomplished with a buried option which in my opinion should be better spotlighted called the Advanced Editor for modifying flat file sources.

Often when loading data from another system into a data warehouse, someone will ask “where did this data come from?”.  You can say, it just came from some file that was given to you or you can be a data professional and give them the exact filename that was used.

Below is a table that gets populated from a file from Accounts Receivable showing new accounts.  It would be nice to know which file that they supplied us that was used to load them into the database.

fn1

 

First some background on our current ETL process.  Below is a simple foreach loop that loads multiple csv files that are located in a specific directory.  And you saw above that our table had 3 columns.

 

fn3

 

First thing we need to do is modify our table to include a new field name.  Below I name the field “SourceFileName”, but you can pick any name.  I also made the data type NVARCHAR.   You can make it varchar, but then you will need to do a data type conversion anyway to get it to load.

fn13

 

Now go into the data flow tab and right click the data source for flat file accounts or whatever yours may be named for the source file.  Then click Show Advanced Editor.

 

fn4

 

This brings up the advanced editor.  Click the Component Properties tab.  At the bottom there are options for custom properties.  For the FileNameColumnName field, put in your column name that you added to the database.

fn5

 

You can use any name, but I like to match my fields with the names in the database so I use the same name: SourceFileName that we added above.

fn6

 

Now double click the destination.

fn10

 

Click the Mapping option on the left side.  You can see the destination column SourceFileName has an ignore input column.

fn11

Change the input column to SourceFileName which is what we added in the advanced editor for flat file above.  Your input column could be named different from what I used below.

fn12

 

I truncated the database table and reran the SSIS package again.

fn9

 

Looking at the table again, we can see the filename for each row got loaded into the SourceFileName field.

 

fn14

 

Using this feature, you can now better document  your ETL processes.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s