Every once in a while you need to do a bulk change to your sql server jobs. In our case this happened as a matter of changing the location of where our ssis package files and configurations reside. The crux of what we needed to do is change the directory location from a local store to a dfs location. For example we needed for the ssis package to go from:
C:\ssispackages\package1.dtsx
To
\\dfsshare\ssispackages\package1.dtsx
With over 200 packages and 3-5 steps in each opening each job and each step to change can be a couple days of tedious work. Rather then spinning on this it's much easier to simply do a bulk update of you jobs steps using the sysjobsteps table. If we needed to make the change above the command would look something like this.
update sysjobsteps
set command =
replace(command, 'C:\ssispackages\package1.dtsx', '\\dfsshare\ssispackages\package1.dtsx')
No comments:
Post a Comment