Find a file with a wildcard, ForEach Loop or Script Task?

Question

Find a file with a wildcard, ForEach Loop or Script Task?

smacumber 77733

Ten Centuries

Points: 1288
More actions
June 29, 2010 at 11:06 am

#157042

Currently I have this written in a Script Task as works if my file name is the same but its not.
In my package the first thing I need to do is find a file that starts with "DirectSat28" and is a text file. The full file name varies each week, could be "DirectSat28 - 06212010 thru 06272010" or other date ranges.
Once this file is found I need to do a find and replace script and then re-save it. (I have this part down in the script).
Then my package imports the text file into a SQL table.
Next the package executes SQL Task.
After that is done then I will want to move the file into another folder.
What is the best route.
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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

Tim Mitchell SSCoach Points: 15681 More actions · Answer 1

You could do this using a script task, but it's probably easier to use a ForEach Loop, configured to use the Foreach File Enumerator. You'd specify the directory from which to access the file, and optionally use wildcard characters (such as "DirectSat28*.txt") to access any file matching that pattern.

You'd need to have a process that also either renamed or moved the file as it's processed, so you don't keep processing the same files over and over again. This could also be done within the ForEach Loop using a Filesystem Task.

hth,

Tim

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices

smacumber 77733 Ten Centuries Points: 1288 More actions · Answer 2

I have a DataFlow that I want to take the file name found in the ForEach Loop and pass to the connection manager for the source, how do I do that?

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 3

Reg : Moving file to another folder, you can do any of the following steps

1. simplest one, use execute process task,

set executable property as Move.exe / xcopy.exe /Copy.exe (Give full path of the file including file name)(Move will remove source and Copy/Xcopy will still leave ur source as it is)

and set Arguments property as SourceFileName+" "+TargerfolderName , you can use expression too

Method 2:

use script task, use System.IO name space

Method 3:

Use File system task, define source connection and target connection

and your 2nd question:

Take file name from ForEach Loop to variable, use that variable in source connections ConnectionString property, build via expression

pls let us know if you need some more info on this, thx.

RB

smacumber 77733 Ten Centuries Points: 1288 More actions · Answer 4

Got the For Each Loop Container to pass the variable FileName to the Data Flow Task and works like a charm!

Thanks!

Tim Mitchell SSCoach Points: 15681 More actions · Answer 5

Sounds like you found the right setup. Yes, based on what you described, the For Each Loop works very well... you can create a variable to store the name of the file currently being processed, and then use that variable in a property expression to process the file and move or delete it when done.

Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices