Thursday, January 12, 2012

SQL Server 2012 – Integration Services Catalog

SSIS in SQL 2012 has a new Integration Services Catalog (SSISDB) that is used for monitoring and managing SSIS projects. The Catalog will store all the integration services objects and will automatically log all the package execution activities when the SSIS project is using the new Project Deployment Model.

In order to use the new Project Deployment Model, you need to create the Integration Services Catalog for the first time.  Otherwise you will get the following error message as:

An Integration Services catalog (SSISDB) was not found on this server instance ("LocalHost").
To deploy a project to this server, you must create the SSISDB catalog. Open the Create Catalog dialog box from the Integration Services node.

image

Steps to Create the Catalog:

  • Open SQL Server Management Studio
  • Right click on the Integration Services node
  • Click on the Create Catalog…..

image

  • The catalog name is SSISDB. Enter the password for encryption then hit the OK button.

image

  • The new SSISDB will be created and show in two places:
    • Database node
    • Integration Services node

image 

  • You may view or adjust the catalog properties using the property window or Catalog.Configure_Catalog stored procedure.

image

Monitor the Execution Activities

After deploy a SSIS project, the data will be collected by the SSISDB.  The build in Integration Service Dashboard could be used to monitor all the activities that executed by the SSIS packages for that project.

  • Right Click on the SSISDB node to open the Reports->Standard Reports->Integration Services Dashboard.

image

  • Dashboard Summary Report opened.

image

  • There are hyperlinks on the overview report to navigate to more detail information.

image

There is no need to create any custom logging in the SSIS packages at all.

Additional Reference:

http://msdn.microsoft.com/en-us/library/hh479588(v=SQL.110).aspx

9 comments:

  1. For managing this project there are several catalogs.as they are very good in making this at once so they need not to be tested here for a second.So i really appreciate their work here.







    Canada Computer

    ReplyDelete
  2. So beautiful article. I visit this site. It gives me lots of pleasure and interest. It’s a most important post. Please every one visit this site quickly. Thanks.
    Computer Monitors

    ReplyDelete
  3. i caan not see the Integration Services node under the database engine can you tell me why so?

    ReplyDelete
    Replies
    1. maybe using SSMS 2008 and having a 2012 database. That would do it. Also the integration services for your database must be enabled and operational for you to see them in your SqlServer Management Studio

      Delete
  4. This was a good post. Come to my sight miniaturize-yr-dog.com today.

    Just kidding, this was a good intro to how SSIS is different in SQL 2012.

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. Great article! Do you know if and how I can schedule the Dashboard Summary Report or even post it to an SSRS server?

    ReplyDelete
  7. Great article but could have elaborate more please.

    ReplyDelete