How can I automate to run DTS right after replication?

[Miriam]

Hello,

Does somebody knows is it possible to automate the run of DTS package right after replication is finished?

I am replicating the stored procedures from my load server to production servers. If any changes in any stored procedure have been done I have to reinitialize all subscribers and run the snap-shot for all stored procedures.
I found out that the permission missing on a production every time after changes. So, I created the DTS package, which have GRANT permission for all my users to execute. But for now I run it manually.
Is it possible to automate that process?

Thank you in advance
Miriam

 

[jweber9]

You could add another step in the replication job as the last step. Then when replication is done you could execute the DTS package. The step would need to be an ActiveX Script type and the language would be vbscript. Below is an example:

Function Main()

dim oDTS
dim sServer
dim sUser
dim sPwd
dim LogFile
dim Path
dim bTrusted
bTrusted = 256 'Means you are using authentication

sServer="Your Server Name"
sUser="User id if not using authentication"
sPwd="pwd if not using authentication"
set oDTS = getobject("","DTS.Package&quot
oDTS.loadfromsqlserver sServer, sUser, sPwd, bTrusted,,,,"Name of DTS package"

oDTS.execute
End Function

Hope this helps

 

[Miriam]

Thanks a lot, I will try.

Can you please answer on my additional questions:
Do I have to add it in REPL-Distribution job as additional step for each subscriber? If yes, does it mean what this DTS will run EVERYTIME the replication ran?
Do I have to add it after "Run agent" step and before "Detect nonlogged agent shutdown"?

Thank you in advance,
Miriam