Process text files according to it's content

Question

Process text files according to it's content

Shing

SSC Enthusiast

Points: 108
More actions
May 29, 2009 at 7:40 am

#210307

I need to loop through a list of text files in a directory, which i would use the Foreach loop container.
Then according to the contents of the file:
1) If the 2nd row of the file contains the string "ProductA" anywhere, then I need to create a new datecoded directory and move the file into that directory.
and copy the file to the Prodcessed_2 subdirectory.
2) If not, move the file to the processed_1 subdirectory.
This might be supereasy for the experience SSIS developer. However, i am newbie to the SSIS and VB. Any help or starter package would be greatly appreciated.
Thanks in advance!

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

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

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

This article[/url] might give you some ideas. It does not do what you want, but it shows how to use a script component to do some conditional processing of a file.

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

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

Once you have the for each loop enumerator working, add 1 script task and read the above link posted by Jack that explains how to read a text file. Get the value of the second line, do a regular search on it for the required text and set the boolean value to a package variable (say TextFound).

Inside for each loop, add 2 file system tasks (one for creating the directory and the other for the copying the file).

Then add 2 separate workflows (on success) to these two file tasks and edit the workflow

that connects to "Processed_2" directory to set evaluation operation property to "Expression and Constraint" and set the Expression to "@[User::TextFound] == True" (without double quotes) and for the other workflow set the same property to "@[User::TextFound] == False"

--Ramesh

Shing SSC Enthusiast Points: 108 More actions · Answer 3

Thanks a lot, Jack, for the example!

Ramesh, I can understand the steps you are describing, sounds great.

However, the example shows the splitting of the file into to separate files. Instead i only need to look up till the 2nd line of the file and exit. Also, I don't know how to pass back a varaible using C# or VB.net.

Is there another example somewhere? Or Is it possible to get help further?

Thank you so much!

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 4

Do you have to use SSIS for this? Or, can you use xp_CmdShell?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

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

Here is some code you can put in a Script Task that will find the value "ProductA" in the file and then set the Package Variable ProcessFile to either true or False.

Dim reader As System.IO.StreamReader = New System.IO.StreamReader(Dts.Variables.Item("FileName").Value.ToString)

Dim strLine As String = String.Empty

Dim intCounter As Integer = 1

Dim strItems() As String

Dim blnProcessFile As Boolean = False

Do While intCounter <= 2

strLine = reader.ReadLine()

If intCounter = 2 Then

strItems = strLine.Split(New String() {","}, StringSplitOptions.RemoveEmptyEntries)

If Array.IndexOf(strItems, "ProductA") -1 Then

blnProcessFile = True

End If

intCounter = intCounter + 1

Loop

Dts.Variables.Item("ProcessFile").Value = blnProcessFile

Dts.TaskResult = Dts.Results.Success

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

Shing SSC Enthusiast Points: 108 More actions · Answer 6

Shing

SSC Enthusiast

Points: 108

May 29, 2009 at 4:30 pm

#1002313

Jack, You are awesome! Thanks a million!!!