SSIS Error – the path for isserverexec.exe cannot be found

Recently been working with getting multiple SQL Instances on one machine to work. When I uninstalled an old one it affected the latest one. Then all of a sudden my ETL jobs failed. It did not make sense at all seeing as I removed a different instance.

Got the following error when trying to run my ETL jobs or when trying to deploy the jobs:
“the path for isserverexec.exe cannot be found”

Looking for solutions online, they recommend going into the registry. Which didn’t work for me.

I ended up using the install file and running it to add features to an existing connection. And there is was Integration services was not installed for the instance that I was getting the error with.

Installed SSIS on the current instance that needed it and all worked perfectly after that and the jobs ran as perusal.

Hope this helps someone else in the future.

Email Job Execution History in HTML Format

Knowing if your jobs have succeeded or failed can be quite handy. Especially when it comes to knowing whether your backups are failing or if you have ETLs that are failing. You can choose to set up email notifications on each job, although if you have multiple servers you could be receiving a lot of email throughout the day.

There is a simple script where you can email out the job history in a HTML table format for all the jobs executed on a server in one email. You can then set this up on each server and have one mail per server for you to check every day. This script can be scheduled as a job that executes a T-SQL query.

First things first, you need to make sure you have database mail set up on the server and have a default account set. You can use your company’s SMTP server settings, or if you are doing this for yourself, you can use Gmail. There are many more to use besides Gmail.

For this post I shall be setting up the database mail settings with Gmail’s SMTP server details.

Part 1: Setting up database mail
Expand the Management node in Object Explorer. Right-click on Database Mail and select Configure Database Mail.
Step1

Click next on the Wizard start screen and by default the Set up Database Mail radio button will be selected. You can click next on this window to go through the steps of setting up Database Mail.
Step2
Step3

Next you will set up a Profile name for your database mail. I have chosen Job Execution mail to make it relevant for what it will be used for.
Step4

An SMTP account will need to be added. You will select Add on the same New Profile window.

Next complete the mail account details. I have set mine up to use my Gmail account. If you are using your company’s SMTP mail server details, you will need to find out the details and the port number to use. In my experience I have used Anonymous authentication when using a company SMTP server. Once everything is filled in you will click on OK.
Step5

You shall see your newly created profile for your mails. Then select next.
Step6

In the next window you need to manage your profile security. You shall see the profile name you just created. You will need to set this at the default profile for the sp_send_dbmail to use when sending out the emails. Then click on next.
Step7

In the next window you can configure system parameters. I choose to leave the settings that has been recommended by Microsoft SQL Server and click next.
Step8

A completion wizard will appear with a summary of everything that will be created and you can click on Finish.
Step9

Once all has executed you shall see green ticks next to each process that was completed. Finally you can click close and the database mail is now set up.
Step10

Now that the database mail is set up you can you can use the script to email out your job execution history in HTML format.

Part 2: HTML email script
The first section of the script is where you set up your HTML table and place your query for what you want to email out. You can also style the table to have background colours and set padding (you might need to brush up on some CSS).

At the end of the table set up you will set up who you want this to be emailed to, the subject, body and body format.

To test you can run this from within a new query window in SSMS. Later on if you choose you can set this up as a SQL job to email out once day or as frequently as you prefer.
EmailQuery
You can download the script for the Email Jobs Query here.

Involve me and I learn

Scripting Multiple SQL Server Agent Jobs

Thought I would share this useful tip on how to script multiple SQL Server Agent jobs into one script in order to execute it on another server.

For example, you have ETLs and backups scheduled on a DEV server and you want to replicate the jobs onto the PROD server. If you have one or two jobs it would be easy to manually create them. When you have a quite a few more jobs that need to be replicated it can be quite tedious to manually create each one on the other server.

There is a simple trick for you to script out multiple jobs from SSMS and run them on another server.

Step 1:
Connect to the server where the jobs are located that you want to replicate. Expand the SQL Server Agent node in Object Explorer and click on the Jobs node and push F7 on your keyboard.
Step1

Alternatively, you can access the Object Explorer Details by clicking on View on the top Menu bar and selecting the second option Object Explorer Details
Step1.1

This will open up Object Explorer Details
Step1.2

Step 2:
Next you will select all the jobs that you would like to replicate. You can do this by holding Ctrl in on your keyboard and clicking on each job. Then right-click on one of the highlighted jobs and select Script job as -> Create To -> New Query Editor Window

Step2

Step 3:
This will script out all the jobs into one window. You will then need to do a find and replace to change the server name to the name of the server you will be executing this on.
Step3

You can do this by highlighting the server name. In this case BIDEV and pressing Ctrl + H on your keyboard which will open up the Find and Replace dialogue box. Next you will enter the name of the server you want to run the script on and click Replace All.
Step3.1

A pop up will appear to tell you how many occurrences were replaced.
Step3.2

Step 4:
The last step will be to copy and paste the script onto the server you would like to replicate the jobs on. Alternatively, you can save the script and copy the script over to the server you would like to run it on and open it up in SSMS, then click execute once open.

2660-inspirational-quotes-about-learning