Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using SSIS to Capture the File Name Expand / Collapse
Author
Message
Posted Saturday, October 3, 2009 11:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:44 PM
Points: 181, Visits: 694
Comments posted to this topic are about the item Using SSIS to Capture the File Name
Post #797415
Posted Monday, October 5, 2009 2:23 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 11:02 AM
Points: 2,397, Visits: 3,419
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"
Post #797706
Posted Monday, October 5, 2009 6:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:39 AM
Points: 109, Visits: 606
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.
Post #797840
Posted Monday, October 5, 2009 6:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 8:44 PM
Points: 181, Visits: 694
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.
Post #797844
Posted Monday, October 5, 2009 7:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:39 AM
Points: 109, Visits: 606
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.
Post #797852
Posted Tuesday, October 6, 2009 7:03 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 9:12 AM
Points: 100, Visits: 138
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
Post #798472
Posted Tuesday, October 6, 2009 7:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 20, 2014 10:39 AM
Points: 109, Visits: 606
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#.
Post #798477
Posted Tuesday, October 6, 2009 9:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 9:12 AM
Points: 100, Visits: 138
Thanks for that, also found how to resolve the Collection screen variance, choose a different enumerator and then back to File enumerator...
Post #798598
Posted Wednesday, October 7, 2009 5:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 20, 2014 7:58 AM
Points: 7, Visits: 181
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.
Post #799620
Posted Tuesday, October 27, 2009 10:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 5, 2010 12:12 PM
Points: 1, Visits: 7
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.
Post #809448
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse