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 Tuesday, January 5, 2010 2:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 7, 2014 10:20 AM
Points: 2, Visits: 65
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.
Post #842447
Posted Friday, June 11, 2010 2:30 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 3, 2011 7:31 AM
Points: 91, Visits: 423
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!
Post #936303
Posted Friday, March 18, 2011 12:04 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 7, 2014 2:26 PM
Points: 56, Visits: 100
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
Post #1080611
Posted Monday, March 5, 2012 8:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 20, 2012 3:30 PM
Points: 2, Visits: 13
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.
Post #1261611
Posted Wednesday, April 25, 2012 2:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 16, 2012 8:39 AM
Points: 1, Visits: 15
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
Post #1290280
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse