SSIS – How to prevent a Task failure from failing an entire package

May 11, 2015

Sometimes you will have an SSIS package which contains volatile task where failure is sometimes allowable – for example if I file doesn’t exist you can not rename it, but you might not want the whole package to fail as a result. Here’s how to continue on failure without failing the package.

1. Create an On error handler for the task
2. Expose System Variables in the variables pane
3. Set the propagate variable value to FALSE

source: http://www.timmitchell.net/post/2013/08/05/continue-package-execution-after-error-in-ssis/

get OS process memory usage

May 7, 2015

exec xp_cmdshell ‘C:\Windows\System32\WindowsPowerShell\v1.0\Powershell.exe get-process -computername XXX ^| ft name,WorkingSet64 ‘

tempdb: DBCC SHRINKFILE: Page could not be moved because it is a work table page.

April 18, 2015

If you dont mind losing all your cached query plans try the following

DBCC SHRINKFILE: Page 4:11283400 could not be moved because it is a work table page.

DBCC FREESYSTEMCACHE (‘ALL’)

I have not needed to follow up with the following command but apparently it could help

DBCC FREEPROCCACHE

After killing all user sessions and issueing freesystemcache all I was able to shrink tempdb

Changing SSIS package program name

April 15, 2015

If you want to change the Program name for an ssis package you can do so in the connection string properties of the connection manager.

Open the connection manager, go to the ‘All’ page and update the Application Name property.

You might want to do this so to help assign the package to a resource workload group.

do not want header in ssrs export

March 16, 2015

Problem: do not want header row in a particular SSRS export, but want it in others

Solution: Add a new export type eg “csv – no header”

Locate the rsreportserver.config

Add the following to extension section of the file

CSV No Header

true

for other formats find the relevant “with header” section and create a new entry based on the above xml

see: http://stackoverflow.com/questions/11531896/how-to-export-a-csv-without-header-in-ssrs

steps taken to discover source of account lockout

February 26, 2015

*microsoft network monitor with authentication filter to capture pre authentication failures (program files)
*eventcombmt to parse security logs (success and failure) on domain controller event code 644 675 4771 4625 which revealed pre authentication failures and source machine, (logs stored in c:\temp, app in desktop\lockout)
*lockout status to unlock account, (located on desktop\lockout)

used to above to track the lockouts to a server. temporarily disabled kerberos pre authentication until we can work out what on the server is causing the lockouts

view database permissions

February 20, 2015

SELECT princ.name principal_name ,
princ.type_desc principal_type ,
perm.permission_name ,
perm.state_desc permission_type ,
obj.name object_name ,
s.name schema_name ,
obj.type_desc object_type
FROM sys.database_principals princ
LEFT JOIN sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN sys.objects obj ON perm.[major_id] = obj.[object_id]
LEFT JOIN sys.schemas s ON s.schema_id = obj.schema_id

Create a Gantt chart effect in SSRS

February 19, 2015

I want to create something like this:

Capture2

http://pnarayanaswamy.blogspot.co.uk/2010/09/range-bar-chart-gantt-chart-using-ssrs.html

Narayana Swamy Palla’s Blog_ Range Bar Chart (Gantt Chart) using SSRS 2008

example rdl, change extension to rdl

Database is stuck ‘In Recovery’ with an unacceptably long delay before it is available and you dont care about any pending rollbacks completing.

February 19, 2015

Stop the sql server service
Rename the log file of the database that is in recovery
start the service

alter database XXX set emergency, single_user
alter database xxx rebuild log on (NAME = ‘log file name’, filename = ‘G:\Logs\log file name.ldf’)
alter database xxx set online, multi_user

the database is back online and the rollback has been partially completed

sort tempdb session usage by size

February 10, 2015

select * from tempdb.sys.dm_db_session_space_usage
order by internal_objects_alloc_page_count desc


Follow

Get every new post delivered to your Inbox.