Hey, Kristof ! – Page 2 – Help for people that read …

Do you know how to solve “The package file is missing from the project directory”

Post author By Kristof
Post date 2021-05-28

Well yes, actually I do.

This happens when you rename a dtsx file outside of the project (ie, in the file system) and then load the package in a project. The project will not complain because it has the right file name but … the package itself knows it’s not the same. So open the package and press F7 or right-click the package in the solution explorer and click ‘view code’.

If you scan down a few lines, you wil see

DTS:ObjectName=”old_package_name_without_extension”

Change the value to reflect the new name (without the .dtsx extension) and you’re all set.

I need a script to shrink all the log files on my database server

Post author By Kristof
Post date 2021-04-08

Ah, I can help with that. The script below will generate a script (scriptception!). So make sure to press ctrl-t before executing the script, so you can get the results in text format. Then, you can copy and paste the result text into a new window and run the script. It will shrink and then truncate all the log files, freeing up space.

Please note that shrinking log files is only useful if you are in a pinch or have run an extraordinary script. Usually, your log files will be around the correct size and shrinking them is only a temporary solution. Expanding the size of the log file and/or buying more disk space is a more lasting solution.

--8<----------------------------------------------------------------------------
/*

PRESS CTRL-T BEFORE RUNNING TO GET THE RESULTS AS TEXT.
Copy the resulting text and run it in a new window

Author:			Kristof Elst
Description:	Shrinks all log files - first moves data to beginning of file
				Then truncates file - releases all free space at the end of the file
Version:		1.0.0
Date:			2020-09-21

*/
--8<----------------------------------------------------------------------------

SET NOCOUNT ON

SELECT
      'USE [' + DB_NAME(database_id) + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + msf.name + N''' , 0, NOTRUNCATE)'  + CHAR(13) + CHAR(10) 
	+ 'GO'
FROM 
	sys.master_files msf
WHERE 
	msf.database_id > 4
and	msf.type = 1

SELECT 
      'USE [' + DB_NAME(database_id) + N']' + CHAR(13) + CHAR(10) 
    + 'DBCC SHRINKFILE (N''' + msf.name + N''' , 0, TRUNCATEONLY)'  + CHAR(13) + CHAR(10) 
	+ 'GO'
FROM 
	sys.master_files msf
WHERE 
	msf.database_id > 4
and	msf.type = 1

--8<----------------------------------------------------------------------------

PRESS CTRL-T BEFORE RUNNING TO GET THE RESULTS AS TEXT.

Copy the resulting text and run it in a new window

Author: Kristof Elst

Description: Shrinks all log files - first moves data to beginning of file

Then truncates file - releases all free space at the end of the file

Version: 1.0.0

Date: 2020-09-21

--8<----------------------------------------------------------------------------

SET NOCOUNT ON

SELECT

'USE [' + DB_NAME(database_id) + N']' + CHAR(13) + CHAR(10)

+ 'DBCC SHRINKFILE (N''' + msf.name + N''' , 0, NOTRUNCATE)' + CHAR(13) + CHAR(10)

+ 'GO'

FROM

sys.master_files msf

WHERE

msf.database_id > 4

and msf.type = 1

SELECT

'USE [' + DB_NAME(database_id) + N']' + CHAR(13) + CHAR(10)

+ 'DBCC SHRINKFILE (N''' + msf.name + N''' , 0, TRUNCATEONLY)' + CHAR(13) + CHAR(10)

+ 'GO'

FROM

sys.master_files msf

WHERE

msf.database_id > 4

and msf.type = 1

How do I get a report on the execution of an SSIS Package

Post author By Kristof
Post date 2021-04-08

If you want an execution report of an SSIS package, you can use the code below. It will generate a dataset for the last execution report of the SSIS package you want.

I think the toggles speak for themselves but just in case, here’s a little overview.

PackageName: Yes, this is the name of the package you want a report on
Verbose: This will show as much info as possible
ShowValidationMessages: Shows validation messages
ShowErrorMessages: Shows error messages. Turn all the other SHOW options off and this on to only see error messages
ShowInfoMessages: Shows info messages.
ShowAuditMessages: Shows audit messages
ShowLatestMessagesFirst: Shows the latest messages first

--8<----------------------------------------------------------------------------
/*

Author:			Kristof Elst
Description:	Shows all messages of a certain type for an SSIS package
				Useful on environments where you can't check the execution reports
Version:		1.0.0
Date:			2020-09-21

*/
--8<----------------------------------------------------------------------------

USE SSISDB

DECLARE @PackageName NVARCHAR(MAX) = 'somepackage.dtsx'
DECLARE @LastOperationId BIGINT = (SELECT MAX(operation_id) FROM catalog.event_messages WHERE package_name = @PackageName)
DECLARE @Verbose BIT = 1
DECLARE @ShowValidationMessages BIT = 1
DECLARE @ShowErrorMessages BIT = 1
DECLARE @ShowInformationMessage BIT = 1
DECLARE @ShowAuditMessages BIT = 0
DECLARE @ShowLatestMessagesFirst BIT = 1

SELECT
	event_message_id,
	operation_id,
	message_time,
	elapsed_time = FORMAT(CAST(message_time AS DATETIME) - CAST(LAG(message_time, 1) OVER (ORDER BY operation_id) AS DATETIME), 'HH:mm:ss:ms'),
	message_type,
	message_source_type,
	message,
	extended_info_id,
	package_name,
	event_name,
	message_source_name,
	message_source_id,
	subcomponent_name,
	package_path,
	execution_path,
	threadID,
	message_code
FROM
	catalog.event_messages EVM
WHERE
	operation_id = @LastOperationId
AND	(	@Verbose = 1
	OR	(
			(
				@ShowValidationMessages = 1
			AND message_type in (10,20)
			)
		OR	(
				@ShowErrorMessages = 1
			AND message_type in (120)
			)
		OR	(
				@ShowInformationMessage = 1
			AND message_type in (40)
			)
		OR	(
				@ShowAuditMessages = 1
			AND message_type in (60)
			)
		)
	)
ORDER BY
	operation_id, 
	event_message_id * IIF(@ShowLatestMessagesFirst=1,-1,1)

--8<----------------------------------------------------------------------------

Author: Kristof Elst

Description: Shows all messages of a certain type for an SSIS package

Useful on environments where you can't check the execution reports

Version: 1.0.0

Date: 2020-09-21

--8<----------------------------------------------------------------------------

USE SSISDB

DECLARE @PackageName NVARCHAR(MAX) = 'somepackage.dtsx'

DECLARE @LastOperationId BIGINT = (SELECT MAX(operation_id) FROM catalog.event_messages WHERE package_name = @PackageName)

DECLARE @Verbose BIT = 1

DECLARE @ShowValidationMessages BIT = 1

DECLARE @ShowErrorMessages BIT = 1

DECLARE @ShowInformationMessage BIT = 1

DECLARE @ShowAuditMessages BIT = 0

DECLARE @ShowLatestMessagesFirst BIT = 1

SELECT

event_message_id,

operation_id,

message_time,

elapsed_time = FORMAT(CAST(message_time AS DATETIME) - CAST(LAG(message_time, 1) OVER (ORDER BY operation_id) AS DATETIME), 'HH:mm:ss:ms'),

message_type,

message_source_type,

message,

extended_info_id,

package_name,

event_name,

message_source_name,

message_source_id,

subcomponent_name,

package_path,

execution_path,

threadID,

message_code

FROM

catalog.event_messages EVM

WHERE

operation_id = @LastOperationId

AND ( @Verbose = 1

OR (

(

@ShowValidationMessages = 1

AND message_type in (10,20)

)

OR (

@ShowErrorMessages = 1

AND message_type in (120)

)

OR (

@ShowInformationMessage = 1

AND message_type in (40)

)

OR (

@ShowAuditMessages = 1

AND message_type in (60)

)

ORDER BY

operation_id,

event_message_id * IIF(@ShowLatestMessagesFirst=1,-1,1)

Can you show me how to kill whomever is hogging my SQL Server?

Post author By Kristof
Post date 2021-03-15

Yes. This script will find the process that is burning the most IO and CPU time. The first column will contain a KILL ??? command. If you copy that value and paste it into a new query window and run it, it will kill the command with the highest CPU and IO time. Carefully! This will kill the process so your database may not like this but at least you will regain control of your system.

To effectively run this against a system that is being hogged, open a connection from a different machine (ie, not on the machine itself).

SELECT
	TOP (1)
	command = 'KILL ' + CAST(prc.spid AS VARCHAR(30)),
	prc.status,
	DatabaseName = DB_NAME(prc.dbid),
	Domain = LEFT(prc.loginame, PATINDEX('%\%', prc.loginame) - IIF(PATINDEX('%\%', prc.loginame) > 0, 1, 0)),
	LoginName = SUBSTRING(
							prc.loginame,
							PATINDEX('%\%', prc.loginame) + IIF(PATINDEX('%\%', prc.loginame) > 0, 1, 0),
							999
						),
	prc.open_tran,
	LastCommand = txt.text,
	prc.hostname,
	prc.blocked,
	prc.cmd,
	CPUTime = prc.cpu,
	DiskIO = prc.physical_io,
	LastBatch = prc.last_batch,
	prc.program_name,
	prc.request_id
FROM
	sys.sysprocesses AS prc
	OUTER APPLY sys.dm_exec_sql_text(prc.sql_handle) AS txt
WHERE
	prc.nt_username > ''
AND prc.status = 'runnable'
ORDER BY
	CPUTime DESC, DiskIO DESC;

SELECT

TOP (1)

command = 'KILL ' + CAST(prc.spid AS VARCHAR(30)),

prc.status,

DatabaseName = DB_NAME(prc.dbid),

Domain = LEFT(prc.loginame, PATINDEX('%\%', prc.loginame) - IIF(PATINDEX('%\%', prc.loginame) > 0, 1, 0)),

LoginName = SUBSTRING(

prc.loginame,

PATINDEX('%\%', prc.loginame) + IIF(PATINDEX('%\%', prc.loginame) > 0, 1, 0),

999

prc.open_tran,

LastCommand = txt.text,

prc.hostname,

prc.blocked,

prc.cmd,

CPUTime = prc.cpu,

DiskIO = prc.physical_io,

LastBatch = prc.last_batch,

prc.program_name,

prc.request_id

FROM

sys.sysprocesses AS prc

OUTER APPLY sys.dm_exec_sql_text(prc.sql_handle) AS txt

WHERE

prc.nt_username > ''

AND prc.status = 'runnable'

ORDER BY

CPUTime DESC, DiskIO DESC;

How do I make a countdown timer in DaVinci Resolve?

Post author By Kristof
Post date 2021-02-17

Well, you will need a Fusion component and an expression.

Go to Edit mode
Find the Effects window to the left, open Toolbox and click effects
Drag the Fusion Composition to an empty track

Right-click the Fusion Composition and choose “Open in Fusion Page”

Drag a text block (T) onto the canvas. Select the text block and turn on the inspector if it is not already on. Then, right click the text Styled Text and add an expression. The expression is the following.

“-” .. string.format(“%02d”,floor((comp.RenderEnd-time+24)/24/60)) .. “:” .. string.format(“%02d”,ceil((comp.RenderEnd-time)/24)%60)

Let’s deep dive into this expression and explore the parts that make up the expression

“-” ..

since this is a countdown timer, it will go backwards. I used this for a timer that displays how long is left so I preceded the timer with a minus sign. The parentheses around the minus sign tell Fusion that it’s a piece of text and the two dots tell Fusion to glue this text to whatever comes next

string.format(“%02d”,floor((comp.RenderEnd-time+24)/24/60))

string.format(#%02d”, aNumber) will format the aNumber to have a leading zero. This means it will always have two digits. If the value is smaller than 10, it will display a leading zero

floor((comp.RenderEnd-time+24)/24/60)

floor will take the numeric expression and round it down
comp.RenderEnd returns the end timecode of the current clip
time returns the current timecode
we divide by 24 because the project has 24 frames per second
we divide by 60 because we want to have the minutes and there are 60 seconds in a minute
we add +24 to the time because we are counting down. That means that when the next minute has already started and the seconds flip back to 00, we still need to show the previous minute. This is the best way to do it that I know of

.. “:” >>

this glues the colon to the minutes part and the seconds part

ceil(((comp.RenderEnd-time)/24)%60))

ceil rounds the number in the expression up. So if there are 25 frames left, then it will show 2 seconds because 25/24 is 1.somethingsmall. If we round that up, it becomes 2. The seconds will only drop to 0 when there are 0 frames left.
%60 divides the expression by 60 and returns the remainder. What this basically does is remove all the passed minutes from the counter and keep only the seconds.