question

Baker-9022 avatar image
0 Votes"
Baker-9022 asked Zoehui-MSFT edited

SSIS Visual Studio 2017 - How to delete duplicate records if a file is being processed again in foreach loop container

I have an SSIS package that has a foreach loop container with a data flow task inside which loops files inside a folder to store records of text files in a SQL table. I am required to add an "Execute SQL Task" before the data flow task which should deletes records if a file is being processed again to avoid duplicate records. My connection manager is an OLE DB. How would I be able to achieve this?

Any help would be greatly appreciated.

sql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered

How do you know that record A in the DB comes from file B that you're foreach loop is processing? That is the query you need to write, based upon your rules. Once you have that query put it into your SQL task as the WHERE clause of your DELETE command. Example (where you're using a parameter provided by SSIS task to specify the filename).

DELETE FROM MyTable WHERE FileName = @filename
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Zoehui-MSFT avatar image
0 Votes"
Zoehui-MSFT answered Zoehui-MSFT edited

Hi @Baker-9022,

If the table have duplicate records, you may use TSQL code to delete it via Execute SQL Task.

 DELETE
 FROM MyDuplicateTable
 WHERE ID NOT IN
 (
 SELECT MAX(ID)
 FROM MyDuplicateTable
 GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Or with this

  delete x from (
   select *, rn=row_number() over (partition by [name] order by id)
   from [0422]
 ) x
 where rn > 1;

Regards,

Zoe


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.