7

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

my destination tables should have output as

C:/source test1_20120116_035812 1234 tester USA

Community
  • 1
  • 1
0537
  • 993
  • 8
  • 15
  • 32
  • Could you be a little more clear on the overall picture of the problem you are trying to solve. I can't really tell from your question but it seems like you have a recurring set of imports you need done on the same files on a regular basis. If this is true - I just finished a large project which required this on multiple sites (servers\physical locations) - I came up with a method that works very well but I approached it a bit differently than you seem to be doing. – Jim Evans Jan 16 '12 at 14:14

2 Answers2

5

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

Derived columns

Demonstrating the derived column does work

Data flow via derived column

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)

Flat File custom properties

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]

Full path provided

billinkc
  • 59,250
  • 9
  • 102
  • 159
  • HI Billinkc, I tried to use the expression which u have given, whn running the package its throwing an error like [Derived Column [89]] Error: The "component "Derived Column" (89)" failed because truncation occurred, and the truncation row disposition on "output column "filename" (189)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component... – 0537 Jan 17 '12 at 05:05
  • I've updated my answer with pictures. The derived column does work based on my understanding of your environment. – billinkc Jan 17 '12 at 12:54
  • How can I use your code to work with mine? http://stackoverflow.com/questions/22969449/how-to-use-the-latest-file-in-a-folder-for-source – Si8 Apr 09 '14 at 17:58
  • Downvoter, if you could provide some feedback on what is unhelpful or unclear about this answer, the community would appreciate it – billinkc Dec 24 '14 at 21:22
3

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..

0537
  • 993
  • 8
  • 15
  • 32