Task to trigger a flag..
-
Hello
Does anyone know how i can trigger a field from '0' to '1', once a package has been sucessfully executed.
I have a field that says export, with flags 0 and 1, the package extracts records flagged 0, and once extracted by the package, the particular recored extracted needs to updated and flagged to 1.
Does anyone know how i can do this?
-
You can add an Execute SQL Task before your package completes that executes the following code:
update dbo.YourTable set
export = 1
where
export = 0;
😎
-
if it were me, i would probably copy the row identifiers of the data being extracted into a separate, temporary data destination in the data flow, along side your main extraction.
then, use a join to flag all records that have actually been successfully extracted once the data flow has finished. then drop the data destination (or clear it out ready for next time).
this way you are sure you are only flagging records as being extracted when you know they have actually been extracted.
tom
Life: it twists and turns like a twisty turny thing
-
will this be done with ssis and with what particular task will i be using?
-
yes. to separate the out the row identifiers use a multicast transformation in the data flow task and send just the identifiers (whatever makes up your primary key) to a data destination (probably a table in the source database).
once the data flow task has finished, run an execute sql task to either run the update sql or run a stored proc in the source database that will do the updating.
the script will be something like:
[font="Courier New"]update aset extracted = 1 -- or use getdate() if a date/time stamp is more useful
from
[i]your source table[/i] a
inner join
[i]your row identifiers table[/i] b
on a.[i]row identifier[/i] = b.[i]row identifier[/i][/font]
Then finish off with an execute sql task that will clean up the row identifiers table so that it is ready for use for next time (i would probably do this at the beginning of the process as well, just to make sure).
tom
Life: it twists and turns like a twisty turny thing
-
may seem a bit pricky, but do you have a graphical representation of it?
-
sorry, no, i'm a bit tied up at the moment to knock something together.
which aspect are you struggling with?
Life: it twists and turns like a twisty turny thing
-
on where to place the task within the data flow
-
when you say task do you mean the multicast transformation? it doesn't really matter where, but i would have it as the last step before the data destination. this will give you two streams: send one to the main output and send the other to your row identifier holding table.
Life: it twists and turns like a twisty turny thing
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply