A user recently asked how to generate a file link using SSIS so that it could be inserted into the database. While creating an example for him, I figured I might as well go all out and make a blog about it. So, here it is.

I created a flat file called FileLinkExtract.txt and loaded it manually for my test file.

Listing 1: Contents of FileLinkExtract.txt

FileID,FilePath,FileName

1,C:\test\,File1.txt

2,C:\test\,File2.txt

3,C:\test\,File3.txt

4,C:\test\,File4.txt

5,C:\test\,File5.txt

Then, I created five files to reflect the above names. I have all of these files and the folder zipped up as well as the package that I created during testing. If you extract all of the items to c:\test, you should be able to test this solution by importing the dtsx (package) into a solution.

After creating the files, I fired up BIDS and created a new package in my SQL Server Integration Services project. I added a Data Flow to my Control Flow and double clicked it. I added a Flat File Source and pointed it to my c:\test\FileLinkExtract.txt stating that the first row had column headers. Next I added a Derived Column transform onto the Data Flow design surface. I connected my Flat File Source to my Derived Column (dercol) and added a new value to be available downstream. Here is the expression that I entered in my dercol:

Listing 2: Derived Column Expression

"file://" + REPLACE(FilePath,"\\","/") + FileName

I named the column FileLink and I saved my expression. I added a Union All (although the user I was helping would be writing this to a table) as a terminator and connected my dercol to it. Figure 1 below shows what my Data Flow looks like and Figure 2 shows the results when adding a Data Viewer.

Figure 1: Data Flow

Figure 2: Results

That’s all there is to it. Now, if you were to put the FileLink value into a Word document or something, you should be able to click it to open the file. You can download the files and package here. And if you don’t already, please be sure to follow me on twitter at @briankmcdonald. Also note that you can subscribe to an RSS feed of my blogs or find me using any of the below methods.