Allow non admin users to connect to SSIS remotely

August 26, 2014

http://msdn.microsoft.com/en-us/library/aa337083.aspx

When a user without sufficient rights attempts to connect to an instance of Integration Services on a remote server, the server responds with an “Access is denied” error message. You can avoid this error message by ensuring that users have the required DCOM permissions.

If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.
Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.

Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.
Select the DCOM Config node, and then select SQL Server Integration Services 11.0 in the list of applications that can be configured.

Right-click on SQL Server Integration Services 11.0 and select Properties.

In the SQL Server Integration Services 11.0 Properties dialog box, select the Security tab.
Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.

In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.

Click OK to close the dialog box.

Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.

Close the MMC snap-in.

Restart the Integration Services service.

What is filling up the transaction log?

August 14, 2014

SELECT a.transaction_id ,
DB_NAME(database_id) dbase_name,
database_transaction_begin_time ,
database_transaction_log_record_count ,
database_transaction_log_bytes_used ,
database_transaction_log_bytes_reserved ,
b.session_id
FROM sys.dm_tran_database_transactions a
LEFT JOIN sys.dm_tran_session_transactions b ON a.transaction_id = b.transaction_id
ORDER BY database_transaction_log_record_count DESC

Optimizing (replacing) recursive cte’s

July 24, 2014

Recursive cte’s do not perform well when you are dealing with millions of records.

The following approach is not particularly elegant but performs so much better.

–Create table to store results

CREATE TABLE #RESULTS(ID INT NOT NULL, PARENTID UNIQUEIDENTIFIER NOT NULL, CHILDID UNIQUEIDENTIFIER, LEVEL INT IDENTITY(1,1) NOT NULL);

SET IDENTITY_INSERT #RESULTS ON;

INSERT #RESULTS(ID,PARENTID,CHILDID,LEVEL)
SELECT ID,PARENTID,CHILDID,1
FROM #SOURCE
WHERE PARENTID=@START;

WHILE @@ROWCOUNT>0
INSERT #RESULTS(ID,PARENTID,CHILDID,LEVEL)
SELECT S.ID,S.PARENTID,S.CHILDID,SCOPE_IDENTITY()+1
FROM #RESULTS AS R
JOIN #SOURCE AS S
ON S.PARENTID = R.CHILDID
WHERE LEVEL = SCOPE_IDENTITY();

The scope indentity function tells us the value of the last identity that was inserted within the current scope.
The while will discontinue when no more rows are inserted.
You could achieve similar results using an int variable rather than an identity field if you prefer

create a sql server linked server with a name different to the server that is being linked to

July 1, 2014

Provider = sql server native client
datasource = server name
catalog = database

key is not to use server type = sql server in the gui

delete file older than a given date with xp_delete_file

May 28, 2014

example

EXECUTE master.dbo.xp_delete_file 0,N’C:\SQLServer\Backup’,N’bak’,’2014-05-27T17:29:04′

this will delete all files in the specified location with a .bak extension which are older than the specified date

trouble shooting resource semaphore waits

April 29, 2014

The following query will show where the waits are and if other groups have unused minimum amounts assigned

SELECT name ,
active_memgrant_count ,
active_memgrant_kb ,
memgrant_waiter_count ,
min_memory_percent ,
max_memory_percent
FROM sys.dm_resource_governor_resource_pools
ORDER BY pool_id

querying extended event event file (xel)

April 28, 2014

the following select string will query an even file for the following sample xml

SELECT q.duration,q.database_id,FILE_ID,file_type,total_size_kb,size_change_kb,timestamp,name,file_name,username
FROM (
SELECT duration=e.event_data_XML.value(‘(//data[@name="duration"]/value)[1]‘,’int’)
, database_id=e.event_data_XML.value(‘(//data[@name="database_id"]/value)[1]‘,’int’)
, file_id=e.event_data_XML.value(‘(//data[@name="file_id"]/value)[1]‘,’int’)
, file_type=e.event_data_XML.value(‘(//data[@name="file_type"]/value)[1]‘,’varchar(max)’)
, total_size_kb=e.event_data_XML.value(‘(//data[@name="total_size_kb"]/value)[1]‘,’int’)
, size_change_kb=e.event_data_XML.value(‘(//data[@name="size_change_kb"]/value)[1]‘,’int’)
, file_name=e.event_data_XML.value(‘(//data[@name="file_name"]/value)[1]‘,’varchar(max)’)
, username=e.event_data_XML.value(‘(//action[@name="username"]/value)[1]‘,’varchar(max)’)
, TIMESTAMP=e.event_data_XML.value(‘(//@timestamp)[1]‘,’datetime2(7)’)
, name=e.event_data_XML.value(‘(//@name)[1]‘,’varchar(max)’)
, *
FROM (
SELECT CAST(event_data AS XML) AS event_data_XML
FROM sys.fn_xe_file_target_read_file(‘\\server_name\d$\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\aaa_0_130385763224100000.xel’, NULL, NULL, NULL)

)e
)q

 

<event name=”database_file_size_change” package=”sqlserver” timestamp=”2014-03-06T12:00:04.561Z”>
<data name=”duration”>
<value>11111</value>
</data>
<data name=”database_id”>
<value>1</value>
</data>
<data name=”file_id”>
<value>1</value>
</data>
<data name=”file_type”>
<value>0</value>
<text>Data file</text>
</data>
<data name=”is_automatic”>
<value>true</value>
</data>
<data name=”total_size_kb”>
<value>111111</value>
</data>
<data name=”size_change_kb”>
<value>1111</value>
</data>
<data name=”file_name”>
<value>FILE_NAME</value>
</data>
<data name=”database_name”>
<value />
</data>
<action name=”username” package=”sqlserver”>
<value>USERNAME</value>
</action>
</event>

 

 

 

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

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


Follow

Get every new post delivered to your Inbox.