SQL Overview SSIS Package III - Full Package

  • David Bird

    SSCarpal Tunnel

    Points: 4669

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

    David Bird

  • DoubleD

    SSC-Addicted

    Points: 420

    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

    The attachment to this reply has been removed

    David Bird

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715107

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

    It's there now.

  • Rodney Landrum

    SSCommitted

    Points: 1898

    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: 1212

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

    thanks for sharing.

    jiulu

  • Darwin101

    SSC Veteran

    Points: 289

    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: 1898

    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 Veteran

    Points: 289

    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

    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

    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

    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

    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

    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

    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

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

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