March 19, 2021 at 7:49 am
Hi there, everyone.
I have a bit of a pickle. I need to move all our SSIS jobs from one server to another. The problem is all the databases that are used / queried also reside on the same server. One of the jobs has a step that runs a SQL query inside that actual job step which essentially queries a database on the source server, but that table will not reside on the new SSIS server. How can I change this / do this? Does this make sense?
March 19, 2021 at 3:45 pm
If it is in an SSIS package (ie the job step calls an SSIS package and is NOT calling direct TSQL), you should have no issues as the SSIS package needs to be configured to tell the SSIS service which server to talk to.
If it is direct TSQL in the job step, you are going to need to do something to make that work. Options include migrating the direct TSQL to an SSIS package (probably the fastest method, but also SSIS can be fussy when schemas change), linked server (bit risky as you need to have RPC OUT enabled in order to run remote stored procedures, but if the account it runs as is restricted enough, you should be safe), or have good timing. By good timing I mean you have job A on the SSIS server that runs and lets say it takes 5 minutes to complete. Then on server B you have a job that starts 5 minutes after job A is started. This works as long as you can handle the timing OR can put in some "wait" condition (such as job A starts on server A and it calls an SSIS package. Step 1 of the SSIS is to update a "JobRunning" table on server B to have a value of 1 in it. Once job A finishes, it updates that JobRunning table to be a 0. Then Job B can start any time after job A and step 1 of Job B is to check if JobRunning is 1. If it is, wait 30 seconds and check again. Once JobRunning is 0, then you can run Job B.
Problems with that last method is that it can get complicated if you need to go back and forth multiple times.
What I would do - replicate everything in a test environment and give it a shot and see if it works or breaks.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
March 23, 2021 at 8:59 am
Thank you for your response. I end up adding it as a step inside the SSIS package. This is a legacy project that got handed to me. I should've thought about it, but sometimes having another set of eyes on it gives a fresh perspective, even if it's something small. THANK YOU.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply