SQLServerCentral / Discuss Content Posted by J.D. Gonzalez / Article Discussions / Article Discussions by Author / Using SSIS to Capture the File Name / Latest Posts

RE: Using SSIS to Capture the File Name

ahz — Wed, 25 Apr 2012 14:58:29 GMT

OK SO i GOT THE @[User::fileName] but in the Derviced column transformation editor I want it to change it to only grab the name of the file and NOT include the '.txt'.. so each file is only 5 characters long from position one... how to cut it only pick up first 5 characters from fileName

RE: Using SSIS to Capture the File Name

Debra True — Mon, 05 Mar 2012 08:23:30 GMT

I followed these instructions to a T and as long as I specify a specific file name for the flat file connection, it will succesfully go through all of the remaining files. My problem is that this needs to be an automated task to pick up all files under the specified subdirectory with *.csv extensions. When I try to run this loop again with differently named *.csv files, it fails and says that it cannot find the filename xxxxx.csv which was originally specified in the flat file connection. I am not totally new to SSIS, but haven't used this type of structure before. Also, my scripting skills are next to zero. Please help.

RE: Using SSIS to Capture the File Name

SSIS Crazy — Fri, 18 Mar 2011 12:04:09 GMT

Has anyone tried the sample project in Sql server 2008? It seems I have too many issues getting it to work. Just wondering if the file system task has to be connected or just have to be left in the foreach loop container? Any kind of help is appreciated.Thanks

RE: Using SSIS to Capture the File Name

lk-681111 — Fri, 11 Jun 2010 14:30:42 GMT

I know this is months late, but I just wanted to say how incredibly helpful this was to me. It's really hard to find actual step-by-step instructions for this sort of thing rather than bits and pieces here and there. Thanks!

RE: Using SSIS to Capture the File Name

pittsburghcodemonkey — Tue, 05 Jan 2010 14:58:15 GMT

I was able to get this to execute locally through business intelligence development studio but when I was scheduling this on our SQL server the job kept on failing. After looking at the script provided in the article where it states: "Dts.Variables("fileName").Value = System.IO.Path.GetFileName(Dts.Variables("fileName").Value.ToString())" In order to get this to work on the server I simply had to remove the "System.IO.Path.GetFileName" leaving me with: "Dts.Variables("fileName").Value = (Dts.Variables("fileName").Value.ToString())"After making that change I was able to sucessfully use this SSIS package from the server.

RE: Using SSIS to Capture the File Name

mmzysk — Tue, 27 Oct 2009 10:23:57 GMT

I am fairly new to SSIS but using this example I have set up a package that loops through files on an ftp site, adds a few derived columns and imports the data from each file into an intermediate sql table, then archives the file. The source files on the ftp site are csv, and the site is hosted internally, so I'm able to specify the path in the file enumerator like \\servername\ftp\clientname\folderwherefiles are. I first set this up on my local machine and it ran correctly every time, getting all the files. Then I imported it to the production sql server and when I try to run it from the packages area (not a job or a proc) it starts to run, but then gives me an error it can't find the file. I'm guessing it must be something to do with rights?? Also now when I go back to my local copy, since shutting down vs and reopening it, the package no longer works correctly, yet I swear I haven't changed anything. It highlights the for each loop and the load data in red and output has, "processing of ....csv started" then says "can not open the data file ..". It's got the correct name of the file in the output window, which it wouldn't have unless it DID find the file since this name is being stored dynamically in a variable, so I really don't know what the problem might be. Pathing? Rights?I'm the only one here really digging into SSIS yet, so any help would be appreciated.

RE: Using SSIS to Capture the File Name

jim Etheridge — Wed, 07 Oct 2009 17:19:00 GMT

When I copy the script code into the script task, I get the following message: "The task is configured to pre-compile the script, but binary code is not found. Please visit the IDE..."What does this mean. I do not know VD or .net, so I'm sure I'm screwing up the code installation someway.

RE: Using SSIS to Capture the File Name

A Joy — Tue, 06 Oct 2009 09:00:20 GMT

Thanks for that, also found how to resolve the Collection screen variance, choose a different enumerator and then back to File enumerator...

RE: Using SSIS to Capture the File Name

Phil Brammer — Tue, 06 Oct 2009 07:08:03 GMT

[quote][b]A Joy (10/6/2009)[/b][hr]This article should probably specify it only works 'as is' with VS2005.There are a number of variances in VS2008The ForEachLoopEditor requires some additional steps and the .net script doesnt parse (afraid Im a straight SQL bod, so not familiar enough with the syntax to work out what has altered)Couple of buttons are renamed as well but meh they were obvious[/quote]The script works fine, you just need to chose VB.Net as the language in the script properties before you invoke the script editor. The default is C#.

RE: Using SSIS to Capture the File Name

A Joy — Tue, 06 Oct 2009 07:03:28 GMT

This article should probably specify it only works 'as is' with VS2005.There are a number of variances in VS2008The ForEachLoopEditor requires some additional steps and the .net script doesnt parse (afraid Im a straight SQL bod, so not familiar enough with the syntax to work out what has altered)Couple of buttons are renamed as well but meh they were obvious

RE: Using SSIS to Capture the File Name

Phil Brammer — Mon, 05 Oct 2009 07:06:17 GMT

[quote][b]J.D. Gonzalez (10/5/2009)[/b][hr]This is great feedback. It's actually a philosophical discussion here at the office. My personal reason for not just grabbing the file name only is that I use the same variable in the file system task to move the file. What I've found is that when I use the file name and extension only is that I now need to create a variable that holds the file path to use during the file system task.[/quote]Your thinking is backwards. Use the fully qualified path and store that in a variable as you have done. Then use that variable as the foundation for other things like the File System Task, the Script Task, etc...However, the flaw is when you overwrite the variable with just the file name + extension and use that variable in the Data Flow as the expression on the Flat File Connection Manager object.Instead, you should create two package-level variables: FilePathFull and FileName (you can change the names, of course, to suit your needs)Populate FilePathFull with the fully-qualified path from the foreach loop. Then in the script task, populate FileName with just the file name + extension. Then use FileName in the Derived Column component in the data flow, and use FilePathFull as the expression on the Flat File Connection Manager object.

RE: Using SSIS to Capture the File Name

J.D. Gonzalez — Mon, 05 Oct 2009 06:59:29 GMT

This is great feedback. It's actually a philosophical discussion here at the office. My personal reason for not just grabbing the file name only is that I use the same variable in the file system task to move the file. What I've found is that when I use the file name and extension only is that I now need to create a variable that holds the file path to use during the file system task.

RE: Using SSIS to Capture the File Name

Phil Brammer — Mon, 05 Oct 2009 06:54:14 GMT

[quote][b]Peso-284236 (10/5/2009)[/b][hr]For the first part, isn't it just easier to use the "FileName" property of load-component?[/quote]Yes, indeed. My thoughts exactly. The problem with this write-up is that there should be a second variable to store the file name so that the original variable populated by the foreach loop can still be used on the expression for the flat file connection manager.You always want the expression on the flat file connection manager to be fully qualified. In this write-up, it is not. I agree with the approach to get just the file name to use in a data flow, I just don't agree in its use on the flat file connection manager.If you really want to use relative paths, then simply choose the "file name + extension" option in the foreach loop and avoid the script task altogether.

RE: Using SSIS to Capture the File Name

SwePeso — Mon, 05 Oct 2009 02:23:47 GMT

For the first part, isn't it just easier to use the "FileName" property of load-component?

Using SSIS to Capture the File Name

J.D. Gonzalez — Sat, 03 Oct 2009 11:20:47 GMT

Comments posted to this topic are about the item [B]Using SSIS to Capture the File Name[/B]