Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using SSIS to Capture the File Name


Using SSIS to Capture the File Name

Author
Message
jdgonzalez@jdandrachel.com
jdgonzalez@jdandrachel.com
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 757
Comments posted to this topic are about the item Using SSIS to Capture the File Name
SwePeso
SwePeso
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2469 Visits: 3431
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"
Phil Brammer
Phil Brammer
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 640
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.
jdgonzalez@jdandrachel.com
jdgonzalez@jdandrachel.com
SSC-Enthusiastic
SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)SSC-Enthusiastic (183 reputation)

Group: General Forum Members
Points: 183 Visits: 757
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.
Phil Brammer
Phil Brammer
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 640
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.
A Joy
A Joy
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
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
Phil Brammer
Phil Brammer
SSC-Enthusiastic
SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)SSC-Enthusiastic (133 reputation)

Group: General Forum Members
Points: 133 Visits: 640
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#.
A Joy
A Joy
SSC-Enthusiastic
SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)SSC-Enthusiastic (100 reputation)

Group: General Forum Members
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...
jim Etheridge
jim Etheridge
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 211
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.
mmzysk
mmzysk
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search