1

I have an SSIS package that first looks at a table in the database for flat files that have been ingested. If they have a status of 1, they are ready to be first archived, and then imported into a database table.

I am using a For Each Loop to Archive the Files and then import them into the database and then mark them as processed in the source table.

For some reason, the archive file task is creating duplicate files on SOME files, but not all. And then when this happens, duplicate data gets inserted into the database.

We have a similar package that does the same thing, yet it does not create duplicate files or database entries.

Does anyone know anything off the top of their head? Please let me know if I need to provide more details

Control Flow

Thom A
  • 88,727
  • 11
  • 45
  • 75
Netta G
  • 53
  • 1
  • 5
  • 1
    I would guess that your Execute T-SQL Task `EST - Get Files to Process` is returning the name of a file more than once, and hence the file is being processed more than once. – Thom A Mar 20 '19 at 21:40
  • @Netta G you should provide the SQL commands used in the Execute SQL Tasks (Get Files to Process + Mark File as Processed) – Hadi Mar 20 '19 at 21:52

2 Answers2

0

A better way of doing what this package does is making sure all the files that you need are in a folder/subfolders and then using for each loop read file after file and save file name in a variable then load data into database and then archive using file system task with additional date and time concatenated to the end of the original file name so there won't be any duplicates.

Miroslav Glamuzina
  • 4,472
  • 2
  • 19
  • 33
Sai Abhiram Inapala
  • 359
  • 1
  • 9
0

Thank you, I found out that because the job was on a load balanced server and there wasn't a step to check which server was primary, it was running the SQL job at duplicate times.

Netta G
  • 53
  • 1
  • 5