how to load extracted file name into sql server table in SSIS

Question

i have 3 csv files in a folder which contains eid, ename, country fields, and my 5 csv files names are test1_20120116_034512, test1_20120116_035512,test1_20120116_035812 etc.. my requirement is I want to take lastest file based on timne stamp and modified date, which i have done. Now i want to import the extracted file name into destination table..

my destination tables contains fields like,

filepath, filename, eid, ename, country

I have posted regarding this before in the same site i got an answer for extracting filename, now i want to load the extracted FileName into destination table

• Import most recent csv file to sql server in ssis

my destination tables should have output as

C:/source test1_20120116_035812 1234 tester USA

Answer 1

In your DataFlow task, add a Derived Column Transformation. The value of CurrentFile will be the fully qualified path to the file. As you only want the file name, I would look to use a replace function on that with the base folder and then strip the remaining slash. This does not strip the file extension but you can add yet another call to REPLACE and substitute an empty string

• Derived Column Name: filename
• Derived Column:
• Expression: REPLACE(REPLACE(@[User::CurrentFile], @[User::RootFolder], ""), "\\", "")

The above expects it to look like

• CurrentFile = "C:\source\test1_20120116_035812.csv"
• RootFolder = "C:\source"

Edit

I believe you've done something in your approach that I did not do. You should see a warning about possible truncation but given the values discussed in this and the preceding question, I don't believe the 4k limit on expressions will be of concern.

Displaying the derived column

Demonstrating the derived column does work

I will give you a +1 for providing an approach I wasn't aware of, but you'll still need to add a derived column to match your provided format (base path name)

Full path is provided from the custom properties. Use the above REPLACE section to remove the path info except use the column [FileName] instead of @[User::CurrentFile]

Answer 2

I tried to get the filename through the procedure which Billinkc has given, but its throwing me error stating that filename column failed becaue of truncation error..

Any how i tried different approach to load file name into table.

steps i have used
1. right click on flat file Source and click on show advanced edito for Flat file
2. select component Properties tab
3. Inside that Custom Properties section ---> it has a property FileNameColumnName
I have assigned Filename to that column property like

FileNameColumnName----> FileName thats it, am able to get the filename into my destination table..