Looping through files

Question

Looping through files

quillis131

Hall of Fame

Points: 3706
More actions
April 14, 2011 at 11:09 am

#235668

I know looping through files is easy in SSIS. But struck up, need a idea to functiona package.
My package should load Data in files to the Table. There will be many files coming to folder say a.1,a.2,a.3,a.4,a.5,a.6. I should loop through all this files and make sure the table is loaded. ... as soon as this bunch of files loaded, i will archive them to Xdirectory.I can help myself untill this point.
Next time when i began this package it should perform a check, if the file is loaded and make sure it should skip that. All files that i am going to get will have an extension .number (.1).
Can anyone help me, in getting an idea

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

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

Sgedela SSC Veteran Points: 201 More actions · Answer 1

I think I understood it correctly. If i were you I will enter file name each time it import to a separate table, and at the begining of the package write script to check if the file name exists or not. If it exist then skip and go to next file.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

quillis131 (4/14/2011)
I know looping through files is easy in SSIS. But struck up, need a idea to functiona package.
My package should load Data in files to the Table. There will be many files coming to folder say a.1,a.2,a.3,a.4,a.5,a.6. I should loop through all this files and make sure the table is loaded. ... as soon as this bunch of files loaded, i will archive them to Xdirectory.I can help myself untill this point.
Next time when i began this package it should perform a check, if the file is loaded and make sure it should skip that. All files that i am going to get will have an extension .number (.1).
Can anyone help me, in getting an idea

If, after processing a file, you move it to an archive folder, there is surely no possibility that you will subsequently reprocess it and therefore I do not understand why the check is required?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Ysaias Portes-QUI Group SSCrazy Points: 2127 More actions · Answer 3

Create a separate table in SQL that stores the names of the files processed.

create table LoadedFiles(fID int identity not null, fileName varchar(25) not null,

loadDate datetime not null default getdate(), fileCreateDate datetime not null --stores the last write time of the file)

Add a column to the tables you are loading the files into called FileID int, then store the file ID for each row, so you know where this record came from.

Before you process a file, query the LoadedFiles table and make sure that the filename and the Last Write time of the file is not the same.

I agree that if you archive the file, then it will not be reloaded unless someone copies it back to your folder.

QUIGROUP- Need a Certified experienced DBA for a project or troubleshooting? Need help with programming, database recovery, performance tuning, ETL, SSRS or developing new databases? Contact us.. 1-786-273-9809

quillis131 Hall of Fame Points: 3706 More actions · Answer 4

Thank you all for writing reply appreciate it...

Now i have a business rule, where it has to load file in order. i will try to explain what exactly i am doing

Forloop:

step 1 : checking if file exists in table(xxyy) and set up a variable with boolean value (say if the file exists then the variable 1 assigned to true)

Step2 : loading data from file to staging table.( only file that doesn't exists in table (xxyy))

Step3: Loading table (XXYY) with file processed

Step4: Archiving file.

Couldn't figure out very exactly i can accomodate the business rule in my above mentioned logic. I get files in particular format with numbers at end (abcd854.1) as soon as this file loads up, the very next file(abcd854.2) should load up. If there is no file the logic should end and no processing of file should take place.

Thank you