December 29, 2018 at 8:43 am
Hi All,
We are migrating databases to brand new AOT cluster. Now the task is to take care of all the 150plus sql agent jobs which use executable and migrate them to new environment and then design the SSDT package, create ssisdb, migrate all executable s to ssis package followed by testing: Since I am new to SSIS and need help with following questions.
1. Installed SSDT tools to design the package.
2. What will be my design look like?
3. How do we migrate all the executable s?
4. How does the ssis package takes care of the 150plus agent jobs?
5.* What is the benefit of using the ssis package to take care of the jobs instead of SQL AGENT?
Thank you!
December 29, 2018 at 9:13 am
sizal0234 - Saturday, December 29, 2018 8:43 AMHi All,We are migrating databases to brand new AOT cluster. Now the task is to take care of all the 150plus sql agent jobs which use executable and migrate them to new environment and then design the SSDT package, create ssisdb, migrate all executable s to ssis package followed by testing: Since I am new to SSIS and need help with following questions.
1. Installed SSDT tools to design the package.
2. What will be my design look like?
3. How do we migrate all the executable s?
4. How does the ssis package takes care of the 150plus agent jobs?
5.* What is the benefit of using the ssis package to take care of the jobs instead of SQL AGENT?Thank you!
Here's my quick thoughts, got some strong opinions on the subject as I've done this many times and currently doing a migration of few thousand jobs.
😎
1. Installed SSDT tools to design the package.
Install Visual Studio and the SSDT which match the version of the SSIS service you are using, any mismatch will cause grief! Keep in mind that one can generate SSIS packages using SSIS, an opportunity to move to a metadata/instruction level!
2. What will be my design look like?
Keep everything as simple as possible and preferably keep all logic in a single T-SQL layer. SSIS has variety of functionalities, but it serves best as a data orchestration tool as most transformations and manipulations are more efficient on SQL Server. There are some exceptions, address those when you counter those possible problems.
3. How do we migrate all the executable?
Look into how you can extract the logic from each executable if you cannot do an automated conversion.
4. How does the ssis package takes care of the 150 plus agent jobs?
Work on a metadata level and do not try to maintain 150+ packages. Adopt or create a framework that can generate the packages from user friendly configurations and business rules, recommend that you look into BIML by Varigence.
5.* What is the benefit of using the ssis package to take care of the jobs instead of SQL AGENT?
This entirely depends on the nature of the jobs!
December 29, 2018 at 11:00 am
Wow!. I get a bigger picture now. One of the other things I will be doing is to create SSISDB and wondering which we would need that?
December 29, 2018 at 11:09 am
sizal0234 - Saturday, December 29, 2018 11:00 AMWow!. I get a bigger picture now. One of the other things I will be doing is to create SSISDB and wondering which we would need that?
The SSISDB can be useful, but maintaining tens or hundreds of packages and jobs isn't. Think of what you can do with the code (automation) and not what you can do in the code.
😎
I'm not convinced that an SSISDB is the right deployment platform, something that you should research and plan before you utilize it.
December 29, 2018 at 12:27 pm
Sure, I will research about the SSISDB.
Couple things:
Since now the migration is complete, I see there was sql agent previously on ( old system) and new instance does not have that. It does have Sql server integration services installed. So I should be able to create SSISDB catalog there once we finalize.
The looked into old jobs and few of them use SP proc, few executable. Do these SP proc's have to be created on new instance as well?
About executable do we have to just copy and paste them from source to destination or it is a different process? Would you be able to share any article or links which help me?
Thanks again
December 29, 2018 at 12:40 pm
sizal0234 - Saturday, December 29, 2018 12:27 PMSure, I will research about the SSISDB.
Couple things:
Since now the migration is complete, I see there was sql agent previously on ( old system) and new instance does not have that. It does have Sql server integration services installed. So I should be able to create SSISDB catalog there once we finalize.The looked into old jobs and few of them use SP proc, few executable. Do these SP proc's have to be created on new instance as well?
About executable do we have to just copy and paste them from source to destination or it is a different process? Would you be able to share any article or links which help me?Thanks again
I'll try to respond as soon as I can, unfortunately I'm up to my neck in work and cannot do any more than this short answer. Couple of points, find out how things work, what they are doing and most importantly, why!
😎
I'll be back in the new year when I've "restored" my life (delivered the project)
December 29, 2018 at 12:47 pm
Big Thank you! for taking time to respond of your busy schedule. Let me start working on your advice.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply