July 16, 2008 at 3:18 pm
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
July 17, 2008 at 7:15 am
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
July 17, 2008 at 7:33 am
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
July 17, 2008 at 1:36 pm
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
July 17, 2008 at 2:29 pm
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!
August 3, 2008 at 10:29 pm
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
September 28, 2010 at 11:55 am
if you want to find out the newly created file, follow the below link
October 15, 2010 at 7:28 am
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 !
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply