24 Apr

SSISDB: The Right-Click

rightI just wanted to touch on these very quickly.  I’d completely ignored them, and, of course, got tagged by the measureUp practice test.  This will be a quick overview of the options that you may, or at least I had, forgotten.

Active Operations

Actively running operations such as deployment, validation, and package execution.



Allows you to run reports.  There are two-kinds, default and custom.  The default reports are:

  • Integration Services Dashboard
  • All Executions
  • All Validations
  • All Operations
  • All Connections

I would be stunned if this is asked on the test, but, who knows.


  • Clean Logs Periodically:  If it’s set to True logs that are older than the retention period are deleted.
  • Retention Period (days):  If the “Clean Logs Periodically” property is True, this is the number of days that logs are saved.
  • Server-wide Default Logging Level: Details that are logged.
    • None: No logging.
    • Basic: All SSIS event types except OnProgress and OnCustomEvent are captured.
    • Performance: OnError events and component phases are captured.
    • Verbose: All SSIS events.
  • Maximum Number Of Version per Project: Number of versions per project that are retained.
  • Periodically Remove old Versions: Removes versions when the total number exceeds “Maximum Number Of Version per Project”.
12 Apr

SSISDB Security

SSISDB is that folder over on the left, in SSMS, under “SSIS Integration Catalogs”.  Security here isn’t all that different from other places in SQL Server, in the sense that it has roles, and permissions, but, it is different.  To access permissions you navigate into the SSISDB folder, right-click on a folder or object, select Properties and then the Permissions tab on the left.

The permissions tab allows you to Grant or Deny permissions but you can also Revoke permissions with a stored procedure (gonna hope that doesn’t show up).  There are up to 9 permissions depending on where you are in the hierarchy.

NameWhat I hope it does.
ReadCan read the object's properties but not for other objects contained in the object.
ModifyCan modify the object's properties but not for other objects contained in the object.
Manage PermissionsCan assign permissions to objects.
Create ObjectsCan create objects in the folder. (Folder only)
Modify ObjectsCan modify objects in the folder. (Folder only)
Execute ObjectsCan execute objects in the folder. (Folder only)
Read ObjectsCan read objects in the folder. (Folder only)
Manage Object PermissionsCan manage permissions on all objects in the folder.
(Folder only)
ExecuteExecute all packaged in the project.
The source for this table is the Microsoft 70-463 Training Kit.

One thing to note is that packages inherit permissions from their containing project and are not assigned to them directly.

Any SSISDB user can deploy projects.  And any objects created by a user (including environments and operations, but mainly projects) can read, modify or execute (projects only) that object.

Don't push the Red Button!

Don’t push the Red Button!

There is a single role for the SSISDB catalog: ssis_admin.  It’s your typical admin role in that it can do anything to any project in the catalog.  The default user for this role is the user who created the catalog.

Finally, users must be granted access to the SSISDB catalog before they can use it.  Upon creation no users will have access to it.

My primary source was the Microsoft 70-463 Training Kit.  I have to be fair and admit that I’m developing more respect for that book.  Parts of it are actually quite good and this is a topic that I couldn’t find in the msdn.  I’m going to have to do another review on that book if this keeps up.

07 Apr

SSIS: Executing A Project With SQL Agent

This is the last post on project deployment.  It continues the last two days posts:

SSIS: Deploying A Project And Executing It In SSMS

SSIS: Parameters With Expressions (it got messy)

If you’ve done both of those, this will be easy.  First, make sure you have a file to move because the package requires that.  For me, that means adding junk.txt to C:\testData.  You can use that or whatever you configured in the “Parameter Deployment Demo” project.

Step 1: Enable SQL Agent.

Hopefully it’s either running or you just need to right-click and hit Start.

Step 2: Add a New Job To SQL Agent.

This is what I did for a quick demo.  It’s not mandatory that you do this exactly.  You can sing “My Way” and it’ll all be fine.

  1. Right-click SQL Agent, click New and Click Job.
  2. Name it something that makes you happy, I used “It Makes Me Happy”.
  3. Go to the Steps tab and click New.
    1. Step Name: First And Only (or something you would like better).
    2. Type: SQL Server Integration Package.
    3. Run as: SQL Server Agent Service Account.
    4. Package Source: SSIS Catalog.
    5. Server: The server the catalog is on.
    6. Package: Click the ellipse and navigate to SSISDB > ParameterTest > Project Deployment Demo > myFileMove.dtsx.
    7. Click OK three times.

There are more options but we  just want to run this package one-time so I’m skipping the other options.

Step 3: Run the job.

  1. Click on the Jobs folder under SQL Agent.
  2. Find the “It Makes Me Happy”, or whatever you called it, and click Start Job.

If everything worked like it should have, the project will have run, and the backup folder should contain your file.

And, for now, that’s all I’m writing about in regards to deploying and executing projects.