Archive for March, 2014

Delete files over 24 hours old via SSIS

March 27, 2014

The following should be pasted into a for each file enumerator, the file name is passed to the script

Dim fi As System.IO.FileSystemInfo

fi = My.Computer.FileSystem.GetFileInfo(Dts.Variables(“file_name”).Value.ToString)

If (DateDiff(“h”, Now.Date, fi.CreationTime)) >= 24 Then
fi.Delete()
End If

Advertisements

Drop all temp tables

March 27, 2014

Rather than list DROP TABLE1,2,3….n.

 

 

DECLARE @d_sql NVARCHAR(MAX)
SET @d_sql = ”

SELECT @d_sql = @d_sql + ‘DROP TABLE ‘ + QUOTENAME(name) + ‘;

FROM tempdb..sysobjects
WHERE name like ‘#[^#]%’
AND OBJECT_ID(‘tempdb..’+QUOTENAME(name)) IS NOT NULL

IF @d_sql <> ”
BEGIN
PRINT @d_sql
EXEC( @d_sql )
END

UK Heat Map using SSRS

March 20, 2014

download a shape file, there is one available here https://www.sharegeo.ac.uk/handle/10672/51?show=full

extract all contents to a directly, not just the .shp file

create a dataset which contains postcodes and summarized data, simple example:

SELECT ‘WV’ AS post_code, 1 AS count
UNION
SELECT ‘CF’ ,5

Drag a map report item to your new report
select esri shapefile and browse to your downloaded file
Set the map resolution to smallest size (i find that the report will fail if I use best quality)
set the position and zoom of the map
select color analytical map

tick the box next to label, select postcode from the dropdown box

change field to visualize to sum(count)

shape file:shape file

Interrogate AD group membership

March 13, 2014

To find which Groups a user is a member of

exec xp_logininfo‘DOMAIN\jlester’,‘all’
To list all members of a group
exec xp_logininfo‘DOMAIN\GROUP’,‘members’