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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carl TawnSystems 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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Eugene ZCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.