SQL Overview SSIS Package III - Full Package

Question

Post reply

SQL Overview SSIS Package III - Full Package

David Bird

SSCarpal Tunnel

Points: 4669
More actions
January 21, 2008 at 11:40 pm

#378120

Comments posted to this topic are about the item SQL Overview SSIS Package III - Full Package
David Bird

Viewing 15 posts - 1 through 15 (of 64 total)

You must be logged in to reply to this topic. Login to reply

DoubleD SSC-Addicted Points: 430 More actions · Answer 1

I cannot see the code. Where should the attachment be?

Great article otherwise! Parts 1 and 2 were great too.

Thanks!

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 2

The attachment to this reply has been removed

David Bird

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 3

Sorry, my fault. David sent it and I must have forgotten to attach it.

It's there now.

Rodney Landrum SSCommitted Points: 1900 More actions · Answer 4

Well now. This is great stuff.

My goal certainly was to allow readers of my original article to modify, extend, enhance and utilize the SSIS package I had put together to collect information about the entire SQL infrastructure. I did not realize the exuberance might lead to reader's own articles based so closely on the original solution.

Glad to see it and this would be my first bibliographical reference in my DBA and writing career for SQL Server.

Of course, I would be remiss if I did not mention that my own enhancements to the solution, both SSIS and SSRS are combined in a follow up article in February 2008 SQL Server Magazine. In the article I cover numerous additions to my original SSIS solution, whose assumed name is now "SQL Snapper" for snapshot SQL Server data gathering and reporting. Sounds a little better than repositories and packages. The enhancements include my own version of disk space monitoring (without the requirement for OLE Automation), security reporting at many levels, and job schedule support for SQL 2000 and 2005. I also expand on connectivity to environments that require SQL authentication and provide many new reports that I use quotidianly.

Rodney Landrum

Author "Pro SQL Server 2005 Reporting Services" (Apress)

sunjiulu Ten Centuries Points: 1232 More actions · Answer 5

well done! a new way doing autodba job using SSIS.

thanks for sharing.

jiulu

Darwin101 SSC-Addicted Points: 400 More actions · Answer 6

Much thanks to Rodney and David!!!

I to am new to SSIS and now see it as a useful tool. Now.. How would I go about using these packages with servers that are on the network but not part of the domain? and to make things more fun each of these other servers use sql auth and have different passwords.

Any help would be greatly appreciated. :w00t:

Rodney Landrum SSCommitted Points: 1900 More actions · Answer 7

Hello dguillory,

Glad you like the solution. I cover SQL authentication for DMZ and such environments in the February issue of SQL Server Magazine. Essentially, it is just creating a new data source and storing the password for the SQL auth account. The trick is with running this under SQL Agent and having the appropriate package protection level set. I typically use store encrypted with user name and let the SQL Agent account service credentials decrypt the package and run successfully.

You will need to add seperate job steps tied to the data source, just dupicates, really, but they will run after the Windows authenticated portion doing the same steps.

Again...thanks for your feedback.

Rodney Landrum

Author "Pro SQL Server 2008 Reporting Services" (Apress)

Darwin101 SSC-Addicted Points: 400 More actions · Answer 8

Hi Rodney

I will watch the mail next week for my magazine and I will look for your book in May.

Thanks for the info and thanks for giving to the community. oh yea... Multi Script is a great tool, I would even come out of pocket myself for that one.

🙂

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 9

I found the package was missing the log used file size it was suppose to collect. The value being stored was NULL. It has been fixed in the package attached to this message.

David Bird

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 10

The SQL Overview package is missing a Truncate SQL statement for the table Jobs.

To add this to the package

1) Add an Execute SQL Task named "Truncate Jobs" to the Sequence Container "Truncate Tables"

2) Select the same connection information as the other truncate tasks

3) Use the SQL Statement "TRUNCATE Table Jobs"

Sorry for the missing task

David Bird

BigSam SSCrazy Points: 2146 More actions · Answer 11

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

David Bird SSCarpal Tunnel Points: 4669 More actions · Answer 12

What is the error you are getting?

What @@VERSION of SQL Server 2005 are you running?

The version of SQL Server I am using is Microsoft SQL Server 2005 - 9.00.3239.00 (X64) Standard Edition.

David Bird

BigSam SSCrazy Points: 2146 More actions · Answer 13

My bad. I though the job was failing on Step 1, but it was actually Step 5 - because I fat fingered the job name.

Thanks much for a great tool. I plan to work with it & see what other monitoring I can do with it.

BigSam SSCrazy Points: 2146 More actions · Answer 14

I encountered a problem getting the size of the files, when the database was mirrored in the Update Data Used Size step. I received this error message:

failed with the following error: "The database "MyDatabase" cannot be opened. It is acting as a mirror database.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection

I tried changing the portion of the query that gets the database names

from

SELECT name FROM master.dbo.sysdatabases

WHERE Status & 512 = 0

To

SELECT name FROM master.dbo.sysdatabases

WHERE DATABASEPROPERTYEX(name, 'status') = 'ONLINE'

Now I'm getting this error:

failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I know the new Select statement works, so how do I determine what portion of the query is failing? I cannot use the SQL Verify function in the Build Query tool.

I'm stumpted & looking for assistance.

Thanks