Centralized SSIS solution to monitor failed SQL Server Agent Jobs across the enterprise


In my environment we have 3rd party tool to monitor sql jobs and send us alerts when a job fails.  Recently it's been acting weird and send us alerts after days.  So I've been asked for an alternative solution.

The link below basically tells  how to configure that SSIS Package but the steps are not clear to me on Step #2 where I need to add the dynamic OLE-DB connection to the package.  When I right click on 'connections manager' console I don't see the option for 'Dynamic_SQLServerConn OLE-DB connection'.  Can some one please modify that document with more detail information/steps please please.  I wish DBA could stay away from SSIS. :-(


Thanks in advance
Who is Participating?
Eugene ZConnect With a Mentor Commented:
one more:
if you follow this link instructions
you will see where from Dynamic_SQLServerConn
 came-- it can be named Dynamic_angel7170SQLServerConn

<Rename the ADO.NET connection as UtilityDB_ADOConnection.

Next, add the dynamic OLE-DB connection to the package, which allows us to connect to the different SQL Server instances. This connection is passed with SQLServer_Connection variable that contains the SQL Server instance name from the SSISServerList table, which was created earlier. Rename the connection to Dynamic_SQLServerConn
Carl TawnConnect With a Mentor Systems and Integration DeveloperCommented:
It means you have to first create an OLE DB Connection with a fixed server name. Once you have done that you need to look in the "Properties" window and edit the "Expressions" section to tell the connection to take its connection string property from a variable.
Eugene ZCommented:
#1 if your email cam with delay - it can be your Exchange server issue that needs t0o be addressed

#2 there are many solutions
using SCOM
When you use Operations Manager to monitor your SQL servers using the SQL Server Management Pack, there are some options that you will need to think about up front

#3 3rd party
Idera sql DM

#4  sql server multiserver administration

Automating Administration Across an Enterprise

Managing Events
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Eugene ZCommented:
btw: the link that you posted is for sql2012: it is sql2012 "launch SQL Server Data Tools"
if you need, we can dig it for you
Introduction to SQL Server Data Tools
angel7170Author Commented:

Now I got stuck on step 3:5 where I have to configure OLE DB Source with the select statement.   Error: Error: Msg 208, Level 16, State 1, Line 1
Invalid object name 'tempdb.dbo.SQL_AGENT_FAILED_JOBS'.

I thought tempdb.dbo.SQL_AGENT_FAILED_JOBS' would be on the tempdb anyways.
Please suggest…thank you!
angel7170Author Commented:
Never had done SSIS package beside import/export. So please be patient with me!
I successful completed the SSIS Package but before i can run this pack I understand that I have to insert data to SSISServerList table.  Can you explain the fields a little

Server Name: ServerName
IsTest: ?
Active: 1\online
Port: ?

Also, where/how/what do I configure to get email alerts?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.