October 3, 2009 at 11:20 am
Comments posted to this topic are about the item Using SSIS to Capture the File Name
October 5, 2009 at 2:23 am
For the first part, isn't it just easier to use the "FileName" property of load-component?
N 56°04'39.16"
E 12°55'05.25"
October 5, 2009 at 6:54 am
Peso-284236 (10/5/2009)
For the first part, isn't it just easier to use the "FileName" property of load-component?
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.
October 5, 2009 at 6:59 am
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.
October 5, 2009 at 7:06 am
J.D. Gonzalez (10/5/2009)
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.
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.
October 6, 2009 at 7:03 am
This article should probably specify it only works 'as is' with VS2005.
There are a number of variances in VS2008
The 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
October 6, 2009 at 7:08 am
A Joy (10/6/2009)
This article should probably specify it only works 'as is' with VS2005.There are a number of variances in VS2008
The 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
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#.
October 6, 2009 at 9:00 am
Thanks for that, also found how to resolve the Collection screen variance, choose a different enumerator and then back to File enumerator...
October 7, 2009 at 5:19 pm
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.
October 27, 2009 at 10:23 am
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.
January 5, 2010 at 2:58 pm
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.
June 11, 2010 at 2:30 pm
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!
March 18, 2011 at 12:04 pm
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
March 5, 2012 at 8:23 am
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.
April 25, 2012 at 2:58 pm
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply