Deriving filename/location in SSIS

Question

Deriving filename/location in SSIS

imani_technology

SSCrazy Eights

Points: 8200
More actions
May 11, 2010 at 12:22 pm

#156943

I am loading a text file. I need to extract that input text file's filename and location and then insert that information into the "filename" column of a SQL Server table. Is SSIS 2005 capable of this? If so, how?

Viewing 15 posts - 1 through 15 (of 17 total)

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

divyanth SSCarpal Tunnel Points: 4303 More actions · Answer 1

You can do that.. assuming that you have a variable which stores the complete path of the file like C:\foldername\foldername\filename.. if so, then create another variable and use substring and Findstring functions to extract the filename and then use it in the derived column of your data flow task.. If you can post your complete path, i can help you with the expression to extract the filename

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 2

Good advice! Now how can I automatically derive the path and filename of the file?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

If you are loading a static text file, you already know its name - so you're not doing that, I'm guessing.

If you are loading multiple dynamically named text files, I'm guessing you're using a Foreach loop. This loop also allows you to map the 'filename being processed' to a variable. It's all done for you.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 4

I believe it is one file, but the name is dynamic. Could the loop work if there is technically only one file?

emily-1119612 SSCarpal Tunnel Points: 4166 More actions · Answer 5

The Foreach loop will look at a folder, optionally subfolders, and files of a certain pattern if you are using wildcarding.

If only one file meets the criteria then yes the Foreach loop will work on one file.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 6

Read this article:

http://sqlblog.com/blogs/jamie_thomson/archive/2010/03/31/filenamecolumnname-property-flat-file-source-adapter-ssis-nugget.aspx

If you are using the Flat File Source, you can directly store the filename in a column. If you only need the filepath and the filename in seperate columns, then you can use a derived column to split it out.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 7

imani_technology

SSCrazy Eights

Points: 8200

May 14, 2010 at 4:56 pm

#1167831

This will be excellent for me. Thank you very much.

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 8

I have another question. This method stores the file name. What if I want to get other file information such as file size, file date, etc.?

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

I don't think that SSIS has built-in features to get that sort of information, so I think you'll have to script in .NET to obtain that info.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 10

I have two questions:

1) Is the Multiple Flat File Connector exclusive to 2008 or is it also available in 2005?

2) How exactly do I get the Foreach Loop to send the filename to the Flat File Connection?

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 11

1) It is only for 2008 or higher (unless it will be tossed away, as this blog post is hoping for: http://sqlblog.com/blogs/jamie_thomson/archive/2010/05/11/five-things-ssis-should-drop.aspx)

2) Create a variable that will hold the filename found by the container. In the For Each Loop editor, go to the Variable Mapping tab and map the variable you created to index 0.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 12

Just out of curiosity, I noticed that my source filenames have different lengths. How do I derive just the file name (no extension and no path) when the filenames have different lengths?

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 13

You need to split the file name into strings. You can use searches to find the slash and the period and get the substring between them.

Some hints:

http://www.sqlservercentral.com/Forums/Topic679052-148-1.aspx

http://sqlblog.com/blogs/andy_leonard/archive/2009/01/30/an-introduction-to-the-ssis-expression-language.aspx

imani_technology SSCrazy Eights Points: 8200 More actions · Answer 14

Back to the original Foreach Loop, I followed these steps:

1. Added the Foreach Loop Container and added the existing Data Flow Task to it.

2. Set the Container's Enumerator configuration to retrieve the Name and extension.

3. Clicked on Variable Mappings and set the User::varDealName to an index of 0.

4. Went to the Properties of the File Connection.

5. Clicked on Expressions. Set Property to ConnectionString and Expression to User::varDealName.

6. Received an error message: "There was no expression to compute. An attempt was made to compute or get the string of an empty expression."

What am I doing wrong?