Inserting the File Name into a column,

Question

Post reply

Inserting the File Name into a column,

David.Parks

Valued Member

Points: 62
More actions
October 12, 2010 at 2:45 pm

#227831

I have a package that loads multi flat files from a directory. Each file is named "Business Area - Date (e.g. DDA 10012010). The files do not contain a date field. I'd like to take the "File Name" and insert it into a column while executing the package. Would this be accomplished during a Foreach Loop container? How can I easily capture the file name and insert it into a column? I have considered using the script editor, which seems doable, but was wondering if there is a cleaner method?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pakman Old Hand Points: 302 More actions · Answer 1

Hi David,

What's your SQL platform?

Enable XPCmdShellEnabled for 2008 in Facets

WayneS SSC Guru Points: 95461 More actions · Answer 2

David.Parks (10/12/2010)
I have a package that loads multi flat files from a directory. Each file is named "Business Area - Date (e.g. DDA 10012010). The files do not contain a date field. I'd like to take the "File Name" and insert it into a column while executing the package. Would this be accomplished during a Foreach Loop container? How can I easily capture the file name and insert it into a column? I have considered using the script editor, which seems doable, but was wondering if there is a cleaner method?

Okay, first you probably should have posted this in the SSIS forums instead.

Yes, you would use a ForEach Container, based on files in a directory. The file name gets put into a variable.

In the data flow task, add a derived column that uses the variable. You now have the file name into a column that can be used in your destination.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

David.Parks Valued Member Points: 62 More actions · Answer 3

Thank you for your reply. I've created the variable and will look into the dervied column method.

David.Parks Valued Member Points: 62 More actions · Answer 4

I found a setting in the package which captures the filename and path. I was able to capture the date from the filname using an operator on the derived column.

WayneS SSC Guru Points: 95461 More actions · Answer 5

So, is this getting you your desired results?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2