CODEWITHSUNDEEP
sql-servercsvssisetldts
Hikari
Hikari

Reputation: 3947

SSIS Foreach Loop process only files that weren't processed yet

I'm developing a SSIS 2012 DTS, it must ETL data from csv to DB.

csv files will be provided every day, the date it's published will be available on the filename: filename_YYMMDD.csv.

Of cource it can't process the same file more than once so that data don't duplicate on DB. The issue is that I can't delete or rename files as I process them. I must process files I haven't yet and leave old ones alone.

I'm thinking on using a log table to store the date of files as I process them. In the begining of the FLC, I query DB for the date of current file. If the date isn't found on log table, FLC runs the ETL. If the date is found, FLC skips to next file.

Is it possible? Should I do it with a Script Task? How can I send a continue command from the Script Task to the FLC loop? Or can I do it somehow with a Execute SQL Task?

Upvotes: 0

Views: 806

Answers (1)

benjamin moskovits
benjamin moskovits

Reputation: 5458

I did exactly that using a log table for all the files I successfully processed. Make sure that you are doing this in a transaction where you update the log table after the csv file is processed. You may find it handy to include, in the log table, the number of rows processed and how long it took to process the file.

Upvotes: 1

Related Questions