Semi-major Win For Me Today
-
So we have this SSRS report that is supported via several SQL jobs that do exactly the same thing for a specific set of facilities, each with their own ID. Each job is comprised of about a dozen steps representing a mic of SSIS packages and plain old sql. As you might guess, each job has its own facility id hard-wired into each of its steps. On Friday, I was tasked with adding support for an additional facility ID (and adding support for a new column in the source data). Today, I generalized all of it by creating stored procs that accept the facility ID as a parameter (to replace the sql steps), and a single ssis import package that accepts the facility id on the command line. The result is a single job that performs work for all desired facility id's (five steps per facility ID), and it only takes about five minutes to add support for additional facility IDs instead of the better part of two hours to create and test a whole new set of packages and associated SQL agent. The real silver lining is significantly eliminating the possibility of copy/paste errors when creating the steps.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
So we have this SSRS report that is supported via several SQL jobs that do exactly the same thing for a specific set of facilities, each with their own ID. Each job is comprised of about a dozen steps representing a mic of SSIS packages and plain old sql. As you might guess, each job has its own facility id hard-wired into each of its steps. On Friday, I was tasked with adding support for an additional facility ID (and adding support for a new column in the source data). Today, I generalized all of it by creating stored procs that accept the facility ID as a parameter (to replace the sql steps), and a single ssis import package that accepts the facility id on the command line. The result is a single job that performs work for all desired facility id's (five steps per facility ID), and it only takes about five minutes to add support for additional facility IDs instead of the better part of two hours to create and test a whole new set of packages and associated SQL agent. The real silver lining is significantly eliminating the possibility of copy/paste errors when creating the steps.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013Just don't tell anyone, you'll have a 2 hour break every time a new facility is added.
-
So we have this SSRS report that is supported via several SQL jobs that do exactly the same thing for a specific set of facilities, each with their own ID. Each job is comprised of about a dozen steps representing a mic of SSIS packages and plain old sql. As you might guess, each job has its own facility id hard-wired into each of its steps. On Friday, I was tasked with adding support for an additional facility ID (and adding support for a new column in the source data). Today, I generalized all of it by creating stored procs that accept the facility ID as a parameter (to replace the sql steps), and a single ssis import package that accepts the facility id on the command line. The result is a single job that performs work for all desired facility id's (five steps per facility ID), and it only takes about five minutes to add support for additional facility IDs instead of the better part of two hours to create and test a whole new set of packages and associated SQL agent. The real silver lining is significantly eliminating the possibility of copy/paste errors when creating the steps.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013Quote:
In every job that must be done There is an element of fun You find the fun and snap! The job's a game And every task you undertake Becomes a piece of cake A lark! A spree! It's very clear to see that A spoonful of sugar helps the medicine go down The medicine go down, the medicine go down Just a spoonful of sugar helps the medicine go down In a most delightful way
:-\
-
So we have this SSRS report that is supported via several SQL jobs that do exactly the same thing for a specific set of facilities, each with their own ID. Each job is comprised of about a dozen steps representing a mic of SSIS packages and plain old sql. As you might guess, each job has its own facility id hard-wired into each of its steps. On Friday, I was tasked with adding support for an additional facility ID (and adding support for a new column in the source data). Today, I generalized all of it by creating stored procs that accept the facility ID as a parameter (to replace the sql steps), and a single ssis import package that accepts the facility id on the command line. The result is a single job that performs work for all desired facility id's (five steps per facility ID), and it only takes about five minutes to add support for additional facility IDs instead of the better part of two hours to create and test a whole new set of packages and associated SQL agent. The real silver lining is significantly eliminating the possibility of copy/paste errors when creating the steps.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013good job, John.
-
So we have this SSRS report that is supported via several SQL jobs that do exactly the same thing for a specific set of facilities, each with their own ID. Each job is comprised of about a dozen steps representing a mic of SSIS packages and plain old sql. As you might guess, each job has its own facility id hard-wired into each of its steps. On Friday, I was tasked with adding support for an additional facility ID (and adding support for a new column in the source data). Today, I generalized all of it by creating stored procs that accept the facility ID as a parameter (to replace the sql steps), and a single ssis import package that accepts the facility id on the command line. The result is a single job that performs work for all desired facility id's (five steps per facility ID), and it only takes about five minutes to add support for additional facility IDs instead of the better part of two hours to create and test a whole new set of packages and associated SQL agent. The real silver lining is significantly eliminating the possibility of copy/paste errors when creating the steps.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013 -
So we have this SSRS report that is supported via several SQL jobs that do exactly the same thing for a specific set of facilities, each with their own ID. Each job is comprised of about a dozen steps representing a mic of SSIS packages and plain old sql. As you might guess, each job has its own facility id hard-wired into each of its steps. On Friday, I was tasked with adding support for an additional facility ID (and adding support for a new column in the source data). Today, I generalized all of it by creating stored procs that accept the facility ID as a parameter (to replace the sql steps), and a single ssis import package that accepts the facility id on the command line. The result is a single job that performs work for all desired facility id's (five steps per facility ID), and it only takes about five minutes to add support for additional facility IDs instead of the better part of two hours to create and test a whole new set of packages and associated SQL agent. The real silver lining is significantly eliminating the possibility of copy/paste errors when creating the steps.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013That's really amusing. I got an email from some code regarding a very similar issue and how to fix it. I didn't quite buy the fix, it seemed odd. [Refactoring a Reporting Services Report with Some SQL Magic](http://www.codemag.com/Article/1805051/Refactoring-a-Reporting-Services-Report-with-Some-SQL-Magic?utm\_source=devweek06132018&utm\_medium=newsletter&utm\_campaign=sm-articles) And congrats for the win. I often experience management kicking and screaming when I suggest similar optimizations.
Latest Article - Building a Prototype Web-Based Diagramming Tool with SVG and Javascript Learning to code with python is like learning to swim with those little arm floaties. It gives you undeserved confidence and will eventually drown you. - DangerBunny Artificial intelligence is the only remedy for natural stupidity. - CDP1802
-
So we have this SSRS report that is supported via several SQL jobs that do exactly the same thing for a specific set of facilities, each with their own ID. Each job is comprised of about a dozen steps representing a mic of SSIS packages and plain old sql. As you might guess, each job has its own facility id hard-wired into each of its steps. On Friday, I was tasked with adding support for an additional facility ID (and adding support for a new column in the source data). Today, I generalized all of it by creating stored procs that accept the facility ID as a parameter (to replace the sql steps), and a single ssis import package that accepts the facility id on the command line. The result is a single job that performs work for all desired facility id's (five steps per facility ID), and it only takes about five minutes to add support for additional facility IDs instead of the better part of two hours to create and test a whole new set of packages and associated SQL agent. The real silver lining is significantly eliminating the possibility of copy/paste errors when creating the steps.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013The biggest food distributor in xxxx (with some 90 "divisions"; 100,000+ service workers, and billions in sales), duplicates all the job streams and "data schemas" (VSAM files!) every time they create a new division ... which happens every few months. The only change is the division "identifiers". 90+ duplicate job streams running every day. The "head" responsible for this is a 30+ year "company" man.
"(I) am amazed to see myself here rather than there ... now rather than then". ― Blaise Pascal
-
That's really amusing. I got an email from some code regarding a very similar issue and how to fix it. I didn't quite buy the fix, it seemed odd. [Refactoring a Reporting Services Report with Some SQL Magic](http://www.codemag.com/Article/1805051/Refactoring-a-Reporting-Services-Report-with-Some-SQL-Magic?utm\_source=devweek06132018&utm\_medium=newsletter&utm\_campaign=sm-articles) And congrats for the win. I often experience management kicking and screaming when I suggest similar optimizations.
Latest Article - Building a Prototype Web-Based Diagramming Tool with SVG and Javascript Learning to code with python is like learning to swim with those little arm floaties. It gives you undeserved confidence and will eventually drown you. - DangerBunny Artificial intelligence is the only remedy for natural stupidity. - CDP1802
I empathize with you about management kicking and screaming when you suggest optimizations. Why do you suppose that happens? ;)
David A. Gray Delivering Solutions for the Ages, One Problem at a Time Interpreting the Fundamental Principle of Tabular Reporting
-
So we have this SSRS report that is supported via several SQL jobs that do exactly the same thing for a specific set of facilities, each with their own ID. Each job is comprised of about a dozen steps representing a mic of SSIS packages and plain old sql. As you might guess, each job has its own facility id hard-wired into each of its steps. On Friday, I was tasked with adding support for an additional facility ID (and adding support for a new column in the source data). Today, I generalized all of it by creating stored procs that accept the facility ID as a parameter (to replace the sql steps), and a single ssis import package that accepts the facility id on the command line. The result is a single job that performs work for all desired facility id's (five steps per facility ID), and it only takes about five minutes to add support for additional facility IDs instead of the better part of two hours to create and test a whole new set of packages and associated SQL agent. The real silver lining is significantly eliminating the possibility of copy/paste errors when creating the steps.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013John Simmons / outlaw programmer wrote:
The real silver lining is significantly eliminating the possibility of copy/paste errors when creating the steps.
So what are the government beauro crats supposed to do all day?
CQ de W5ALT
Walt Fair, Jr., P. E. Comport Computing Specializing in Technical Engineering Software
