SQL Overview SSIS Package III - Full Package

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

    David Bird

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

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


  • The attachment to this reply has been removed

    David Bird

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

    It's there now.

  • 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)

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

    thanks for sharing.


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

  • 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)

  • 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.


  • 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

  • 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

  • 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.


  • 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

  • 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.

  • 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


    SELECT name FROM master.dbo.sysdatabases

    WHERE Status & 512 = 0


    SELECT name FROM master.dbo.sysdatabases


    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.


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

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