WIDBA

WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Wednesday, January 30, 2013

Disabing SSIS properties quickly courtesy of SSISDB

Today I am going to tackle something that came up recently at work - finding the error in an SSIS package and disabling the problematic step so the rest of the processing could continue.  (In a perfect world, the package would compensate for the "unimportant" step, but things happen when you have 100's of packages and release software faster than Java releases updates.)

The problem came up when a small file was not able to open and the step was buried inside a much larger SSIS package, so simply skipping the package was not prudent.

A few solutions came to mind:

  1. Try to figure out the root problem and fix that
  2. Open up BIDS and simply disable the step and run the package in its entirety.
  3. Open up the Job and navigate to the configuration for the step and override the property on the step to disabled.
  4. Fix the issue (change a precedent constraint and a property) and just go to production without a second thought to creating more problems :)
I am recovering DBA, so I went towards number 1 right out of the gate.  This proved to not be feasible given the importance of the step, the fact it involved people outside the team, etc.  2 was a very real possibility but I hate doing "out of band" things on production.  Number 3 ended up being the method I went with.  4 was the last resort - a DBA's nightmare and it turns out is probably common in small Agile shops.

So, I needed to figure out what I needed to disable, in this case I found the error message using SSMS and navigating through the Integration Services Catalogs -> Project -> Package -> Reports -> All Executions and seeing the error.  I then opened up the project in BIDS to find the path to the task that needed to be disabled.(Click on the task, there is a property that has the "path")  This tends to take awhile to accomplish.  Here is a much quicker way to get there.

Here is the SQL to query SSIS DB in SQL 2012.  I have no idea if this works on those old SQL versions.

DECLARE @project_name varchar(128) = 'BeersOfWisconsin'
DECLARE @package_name varchar(128) = 'LoadBreweryDim.dtsx'
DECLARE @DaysLookBack int = -7;

SELECT start_time,end_time,package_path, message,execution_path
FROM catalog.executions ex
INNER JOIN catalog.event_messages evt on ex.execution_id = evt.operation_id
WHERE ex.project_name = @project_name
AND ex.package_name = @package_name
AND ex.status = 4
AND message_type >= 100 -- as of now 110 is warning, 120 is Error, 100 is QueryCancel, 130 is TaskFailed, 140 is Diagnostic
AND start_time > DATEADD(Day,@DaysLookBack,CURRENT_TIMESTAMP)
ORDER BY execution_id DESC

Most of that is for information but the last column as an execution_path that in my case yielded exactly what I needed. (note bold portion). 

\BeersOfWisconsin\DimBreweryLoad\DimDistributor\DimDistributor - ReadFile
Doing a little surgery to this - I end up with what I need to change in the Configuration Property Override path.  

\Package\BeersOfWisconsin\DimBreweryLoad\DimDistributor.Properties[Disable]

Now simply set the Property Value = 1 and save the job and rerun.  

Note:  I have only done this as a back tracking exercise to solve the problem faster in the future, I am not sure if this method will work with all packages and container structures.

Remember to address the root cause and remove the property!

Saw this image the other day, some days they go hand in hand :)





No comments:

Post a Comment