SQL Clone
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
pittsburghcodemonkey
pittsburghcodemonkey
Forum Newbie
Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)Forum Newbie (8 reputation)

Group: General Forum Members
Points: 8 Visits: 71
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.
lk-681111
lk-681111
Old Hand
Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)Old Hand (353 reputation)

Group: General Forum Members
Points: 353 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!
SQL Crazy
SQL Crazy
Valued Member
Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)Valued Member (62 reputation)

Group: General Forum Members
Points: 62 Visits: 111
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
Debra True
Debra True
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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.
ahz
ahz
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

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