Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««34567»»»

SQL Overview SSIS Package III - Full Package Expand / Collapse
Author
Message
Posted Friday, June 12, 2009 10:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 6, 2013 12:08 PM
Points: 8, Visits: 85
David - I wanted to thank you very much for sharing this package. It is very helpfull.

I am having one problem though. I have both SQL2K and 2K5 boxes and it collects all of the data for all of the servers in to the SQL_Servers list which is great but in the Disk_Space table it does not populate the 2K5 servers in to this table. I thought I read that the XP_Fixeddrives that the owner changed on 2K5 to sys from dbo. Do you have anythoughts about this?

Scott
Post #734004
Posted Friday, June 12, 2009 12:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 6, 2013 12:08 PM
Points: 8, Visits: 85
Please disregard my post because once I learned how to read I found the text "requires OLE Automation enabled on 2005".

Doh!
Post #734081
Posted Monday, June 15, 2009 7:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 8:24 AM
Points: 3, Visits: 65
SQL_Overview SSIS package for SQL 2008

Hi,
I have been using the SQL_Overview SSIS package specifically SQL_OVERVIEW_Part3 containing SQL_Overview_Package.dtsx in SQL 2005 quite some time with no issues. I copied the package and Database over to my SQL server 2008 server, Ran the Visual Studio Conversion Wizard which ran the SSIS Package Upgrade Wizard,
I try to migrate it to a SQL server 2008 server that claims to have completed successfully. However, when I open the package itself, it just hangs.

Do you have a converted one for SQL 2008 that works well?

thanks,

YOak
Post #734987
Posted Monday, June 15, 2009 9:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 7:43 AM
Points: 184, Visits: 1,007
I was successful in using SQL Server 2008 "SQL Server Business Intelligence Development Studio" to open the SQL 2005 solution/package and migrating it to SQL Server 2008.

I got some warnings about error counts. Looking at the package after the migrating, I fixed some mapping issues. I ignored the truncation issues.

I ran this all on a local instance of SQL Server 2008 Standard edition SP1 10.0.2531.


David Bird

My PC Quick Reference Guide
Post #735115
Posted Monday, June 15, 2009 10:06 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 6, 2013 12:08 PM
Points: 8, Visits: 85
David when I open the package on a 2005 Dev server it runs but when I try to run it as a job I get the following errors.

Message
Executed as user: OTGDEV04\SYSTEM. ...on 9.00.4035.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:44:20 PM Error: 2009-06-12 13:44:24.83 Code: 0xC0202009 Source: SQL_Overview_Package Connection manager "MultiServer" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'FNBN\OTGDEV04$'.". End Error Error: 2009-06-12 13:44:24.94 Code: 0xC00291EC Source: Execute XP_MSVER Execute SQL Task Description: Failed to acquire connection "MultiServer". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: 2009-06-12 13:44:40.69 Code: 0xC0202009 Source: SQL_Overview_Package Connection manager "MultiServer" Description: SSIS Error Code DTS_E_OLEDB... The package execution fa... The step failed.
Post #735127
Posted Monday, June 15, 2009 10:17 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 7:43 AM
Points: 184, Visits: 1,007
The SQL Agent is running the package as 'FNBN\OTGDEV04$'

This is a local server id which I will assume does not have access to the instance you are trying to connect to. Try configuring SQL Agent to run with an id with the proper authority. Or you can grant 'FNBN\OTGDEV04$' the authority on each instance.



David Bird

My PC Quick Reference Guide
Post #735133
Posted Monday, June 29, 2009 8:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 5, 2009 4:23 AM
Points: 1, Visits: 14
Hi,

I tried to implement SQL Overview Part 1,
SQL Overview SSIS Package II - Retrieving SQL Error Log and SQL Overview SSIS Package III - Full Package. But finally when run the SQL Agent Job
DBA-SQL Overview - Was successful one
DBA-SQL Overview - Report Instances Recently Started Job -- Failed stating Invalid object name 'rep.SQL_Overview_Last_Run_Date'. [SQLSTATE 42S02] (Error 208). The step failed.
DBA-SQL Overview - Report Table Refresh Errors Job -- Failed stating Invalid object name 'SSIS_Errors'. [SQLSTATE 42S02] (Error 208). The step failed.

Please let me know, what do I need to do to correct this errors. Any help will be highly appreciated.


Post #743638
Posted Wednesday, August 5, 2009 5:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, May 2, 2011 1:31 AM
Points: 1, Visits: 20
BigSam (6/25/2008)
I'm getting an error in s01-Job Last Run Datetime. It's a syntax error near '(' in this code. I'm not familiar with the $(ESCAPE_NONE(JOBID)) portion of the statement.

-- Get Job Name
SET nocount ON
DECLARE @JobName sysname
SELECT @JobName = [name] FROM msdb.dbo.sysjobs
WHERE Job_id = CONVERT(uniqueidentifier, $(ESCAPE_NONE(JOBID)))
PRINT '>'+@JobName+'<'


I really like what you've done & want to make this work for me too.

Thanks


I also have this error, even when I past the statementinto a Query window. I'm using SQL 2008 64btis standard edition.
Seems that he does no like the $(ESCAPE_NONE(JOBID))) part.

Any idea?

Nice article by the way.
Post #765351
Posted Friday, October 16, 2009 2:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 11, 2009 12:35 PM
Points: 2, Visits: 21
Hi,

I am just trying to use the sql script for the disk space (I have everything else I need except for disk space)
I have tried a couple different things but am coming up short.. Can someone please give me the script to run in my SSIS package that will find the disk space on all of my servers (I have all of them foreachloop) together already.
I am just not sure on what tables need set up and what the script is.

Please help!

Thanks

Post #804541
Posted Monday, November 9, 2009 11:00 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, May 4, 2011 7:02 AM
Points: 65, Visits: 265
David - first off, want to thank you for this awesome series of articles on Server Overview. I built an Overview package and its been running for about 3 months. Its been fantastic and it's made the DB team look good on several occasions already by being proactive instead of reactive about server and db issues. We love it!
My question is, do you know if there is a way to add a check for seeing when a cluster has a failover? Perhaps a ServerProperty or some way to query a Last Failover Date or something like that? Any thoughts would be helpful....thanks!




Dave Coats
Post #815972
« Prev Topic | Next Topic »

Add to briefcase «««34567»»»

Permissions Expand / Collapse