August 4, 2011 at 10:29 am
I have not used SSIS transfer job task before and I am looking to use it for part of a DR plan. I want to be able to copy jobs from the Prod server to the DR server HOWEVER, on the DR server there is not enough room for our data warehouse, so that database will not be on the DR server.
The problem then is that I do the following for setup:
Create new project to transfer job information.
Add "Transfer Jobs Task" to the project.
Settings:
Transfer all jobs = True (as they are subject to change or addition)
IfObjectExists = Overwrite
Now, when it gets to the third job, the job relies on a database for the data warehouse that is not present. It throws an error though I set Maximum Error Count proprerty to 20 it still fails the task. I want it to throw the error, but still keep transferring jobs to the destination server.
Is there a way to bypass this error and use a continue method to keep transferring jobs from one server to the other?
Also, I do not know where to start to do a dataaset comparison. After the task completes, I would like to look in a DBM database table on the DR server and the msdb.dbo.sysjobs table and do a comparison of jobs to enable in case of disaster. I want to read from one server (no linked servers setup between the two) and insert into a table on the DR machine of which jobs are set to enabled and which are disabled. Any starter pointers would be appreciated! I am new to SSIS.
August 4, 2011 at 11:27 am
So far as I know, you either need to transfer all jobs, or list out the ones you want.
You might be able to loop through a list of jobs, perhaps queried from msdb, and use an expression based on the current value in the list to dynamically transfer them. I haven't tried that, but it's probably worth looking into.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 4, 2011 at 12:25 pm
I was looking for an out of the box SSIS solution to make sure things were not complicated if I am not around for DR reset. I was hoping that this SSIS task couple just transfer a job, if failed, keep transferring jobs. I am looking at other means, but am curious about only the SSIS Transfer Jobs Task if there is a way around the error.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply