question

jhowe avatar image
jhowe asked

ssis mark as processed best practise?

Hello I have a stored proc that extracts some data that i need to export into csv but need to mark as processed so am not sending same rows every week... I'm thinking of creating a SSIS package and having a scheduled job run it once a week... Just curious as to what the best practise is process wise of doing this i.e. component layout etc. as i've not done it before? Thanks for your help!
sql-server-2008ssissql-server-2008-r2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
You can use an `UPDATE` statement with `OUTPUT` clause instead of simple `SELECT`. Assume, you have table with column `ID, Col1, Col2` and `Exported` which is flag which identifies Exported records. Then you can simply use a below statement to read date in `OLE DB Source` UPDATE t SET Exported = 1 OUTPUT INSERTED.ID, INSERTED.Col1, INSERTED.Col2 FROM aTable t WHERE Exported = 0
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

jhowe avatar image jhowe commented ·
Hi I went in a different direction, in my main extract file I used a subquery select bla bla where ID not in a audit table i've created and then used a multicast to send rows to my flat file and audit table. I like your solution of Update combined with OUTPUT and that will work for a slightly different scenario so will mark as answerwed thanks for your help!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.