Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

FileLinking Using SSIS

 

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

data flow -sqlbigeek, brian k mcdonald

 

Figure 2: Results

Results - brian k mcdonald sqlbigeek

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.

 

 

 

sqlbigeek

 

Brian K. McDonald, MCDBA, MCSD
Business Intelligence Consultant

Email: bmcdonald@SQLBIGeek.com

Blogs: SQLBIGeek | SQLServerCentral | BIDN Articles | BIDN Blogs | SQLServerPedia

Twitter: @briankmcdonald

LinkedIn: http://tinyurl.com/BrianKMcDonald

 

  

 

 

Comments

Posted by Anonymous on 1 July 2011

Pingback from  Dew Drop – July 1, 2011 | Alvin Ashcraft's Morning Dew

Posted by Anonymous on 1 July 2011

Pingback from  Dew Drop – July 1, 2011 | Alvin Ashcraft's Morning Dew

Posted by Jason Brimhall on 1 July 2011

Another very useful trick for the BI world.  Thanks.

Posted by Anonymous on 2 July 2011

Pingback from  Dew Drop – July 2, 2011 | Alvin Ashcraft's Morning Dew

Posted by Brian K. McDonald on 7 July 2011

Thanks Jason!

Leave a Comment

Please register or log in to leave a comment.