SSIS How to get a File Name

Question

SSIS How to get a File Name

mxwebb

SSC Eights!

Points: 886
More actions
July 16, 2008 at 3:18 pm

#195497

What I am trying to do is:
Go to directory that has a bunch of Flat files.
I need to pull the data from each file and insert it into SQL table.
Each file has a different Name.
In that file Name I need to strip out a piece of Data.
The format of the file name are: D30CL0079A-M.pcx, D30CL0082A-M.pcx
I need to strip out the two digits in front of the A.
Do a lookup on those two digits in table to get a Code
Then insert that code with each line/Record in that file.
Then move on to next file.
That was hard to explain.
So Capture File Name
Strip File Name of t

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Andrew Gothard-467944 Hall of Fame Points: 3928 More actions · Answer 1

mxwebb (7/16/2008)
What I am trying to do is:
Go to directory that has a bunch of Flat files.
I need to pull the data from each file and insert it into SQL table.
Each file has a different Name.
In that file Name I need to strip out a piece of Data.
The format of the file name are: D30CL0079A-M.pcx, D30CL0082A-M.pcx
I need to strip out the two digits in front of the A.
Do a lookup on those two digits in table to get a Code
Then insert that code with each line/Record in that file.
Then move on to next file.
That was hard to explain.
So Capture File Name
Strip File Name of t

In overview:

In a foreach loop container, set to For Each file in the directory your files will be stored in.

Set the variable to hold your filename in the variable mappings for the container.

In a script task, pass in this variable as readonly, and one to hold your 2 digit ref as readwrite and us vb.net to extract the characters based upon your criteria

Use a SQL task to return the value you're after from the database via a stored proc, input parameter the variable with your 2 chars, output mapped to a variable of appropriate type for your return value.

Use this variable in a transform data task used to move the data from source to target

hth

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Pragmatic Works has a free custom SSIS component called File Properties Task where you can pass the file path variable obtained in the for each loop and one of the properties is the filename. You put this in a variable and then you can use variable.Substring(0, 2).

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

mxwebb SSC Eights! Points: 886 More actions · Answer 3

Ok I still need help ASAP!

I have the foreach loop container, set to For Each file in the directory your files will be stored in!

I create a Variable to hold file name and the two digit Ref Number and variable to hold the code after I do a look up on the Two digit Ref.

I am confused on the following!!!

In a script task, pass in this variable as readonly, and one to hold your 2 digit ref as readwrite and us vb.net to extract the characters based upon your criteria

I understand have created the varialbe how do I create Script to except Parmaters?

HELP

mxwebb SSC Eights! Points: 886 More actions · Answer 4

Man this is driving me crazy.

Ok I added a Script under a Flat File Source

Now what I need to do in this Script is get the file Name Strip out two digits.

When I open the script All I have to work with is the Records for the Flat file Records.

I want to work on the for each loop and get file name for which it is currently working on.

Do my striping of the two digits.

Then do a look up

Then do a derived column and add a new field to record with the code I got from the look up.

HELP HELP!

kristen-711568 Old Hand Points: 391 More actions · Answer 5

this blog may point you in the right direction

http://bi-tch.blogspot.com/2008/02/ssis-synchronousasynchronous-script.html

Kristen Hodges
b. http://bi-tch.blogspot.com

shailendrachauhan Valued Member Points: 55 More actions · Answer 6

if you want to find out the newly created file, follow the below link

http://www.abhyast.com/abhyastcom/post/SSIS-Tips-and-Tricks-Part-2-e28093-Detect-Newly-Created-Files-in-a-Folder-Using-Script-Task.aspx

Mitch Small SSC-Addicted Points: 465 More actions · Answer 7

Jack Corbett (7/17/2008)
Pragmatic Works has a free custom SSIS component called File Properties Task where you can pass the file path variable obtained in the for each loop and one of the properties is the filename. You put this in a variable and then you can use variable.Substring(0, 2).

+1 !