SQL query to display when my SSAS cubes were processed


Doing reporting on the processing times of SSAS cubes is actually surprisingly tedious so I figured I’d write a script for collecting this data and share it with you guys. Hopefully you’ll save some time:

SET NOCOUNT ON

DECLARE @SQL nvarchar(2000)
DECLARE @CubeName varchar(200)
DECLARE @ConnString varchar(2000) 
DECLARE @ConnString_part1 varchar(2000) = 'Data Source=servername\tabular;Initial Catalog='
DECLARE @ConnString_part2 varchar(2000) = ';User ID=domain\username;Password=xyzsomepasswordxyz'

DECLARE @Cubenames table (
	ID INT IDENTITY(1, 1),
	Cubename varchar(200)
)
DECLARE @Processingtimes table (
	ID INT IDENTITY(1, 1),
	Cubename varchar(200),
	Last_data_update datetime
)

--> Create a table with all the cube names. Connect to any of the cubes on the server for this query:
SET @ConnString = @ConnString_part1 + 'MySalesCube' + @ConnString_part2
SET @SQL = 
	'SELECT a.*  
	FROM OPENROWSET(''MSOLAP'', ''' + @ConnString + ''',
		''SELECT [CATALOG_NAME]
		FROM $system.dbschema_catalogs'') AS a;'
INSERT INTO @Cubenames (Cubename)
EXEC sp_executesql @SQL

--> Check which cubes that are collected
--> SELECT * FROM @Cubenames

--> Then iterate through each cube, connect to it and find the LAST_DATA_UPDATE for each of them
DECLARE cur CURSOR FOR
SELECT Cubename FROM @Cubenames

OPEN cur
FETCH NEXT FROM cur INTO @CubeName

WHILE (@@FETCH_STATUS = 0)
	BEGIN
		SET @ConnString = @ConnString_part1 + @CubeName + @ConnString_part2
		
		SET @SQL = 
			'SELECT Cubename = ''' + @Cubename + ''', a.*  
			FROM OPENROWSET(''MSOLAP'', ''' + @ConnString + ''',
				''SELECT TOP 1 [LAST_DATA_UPDATE]
				FROM $System.MDSCHEMA_CUBES
				ORDER BY [LAST_DATA_UPDATE] DESC'') AS a;'
		INSERT INTO @Processingtimes (Cubename, Last_data_update)
		EXEC sp_executesql @SQL


		FETCH NEXT FROM cur INTO @CubeName
	END

CLOSE cur
DEALLOCATE cur

SELECT * FROM @Processingtimes

SSIS table usage – how to find which tables are used in which packages


When you have big SSIS projects it’s often problematic to have full control over which database tables are in use in which packages. So, to figure it out I created this “little” script that collects the names of every table in your database of choice, then searches through an entire directory (with subdirectories) for dtsx-files that contain this table name. It also runs somewhat fast; a total of 257 table names was matched with roughly 300 dtsx-files in 16 different folders in around 45 seconds. Export the results to Excel, add column header filters and you have a nice documentation of your table usage in a jiffy!

/*
Script to find table/view usage inside SSIS packages.
Will loop through every single table/view in the current database 
and find which SSIS-packages they are used in.
*/
 
 
SET NOCOUNT ON
GO
 
--> Database to search
USE MyDatabase
GO
 
--> Add name of folder containing .dtsx-files to search
DECLARE @DtsxFolder varchar(255) = 'C:\Temp\ssis\'
 
DECLARE 
 @cmd varchar(2000),
 @Table_Catalog varchar(255), 
 @Table_Schema varchar(255), 
 @Table_Name varchar(255), 
 @Table_Type varchar(255)
 
DECLARE @cmd_table table (
 id int identity(1, 1) not null, 
 cmd_output varchar(255)
 )
DECLARE @results_table table (
 id int identity(1, 1) not null, 
 Table_Catalog varchar(255), 
 Table_Schema varchar(255), 
 Table_Name varchar(255), 
 Table_Type varchar(255), 
 DtsxFile varchar(255)
 )
 
DECLARE cur CURSOR FOR 
 SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_SCHEMA, TABLE_NAME
 
OPEN cur
FETCH NEXT FROM cur INTO @Table_Catalog, @Table_Schema, @Table_Name, @Table_Type
 
WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @cmd = 'findstr /M /s /C:"' + @Table_Name + '" '+ @DtsxFolder + '*.dtsx' --> Add /s to search subfolders
 
 INSERT INTO @cmd_table
 EXEC xp_cmdshell @cmd
 
 INSERT INTO @results_table
 (Table_Catalog, Table_Schema, Table_Name, Table_Type, DtsxFile)
 SELECT @Table_Catalog, @Table_Schema, @Table_Name, @Table_Type, cmd_output
 FROM @cmd_table
 WHERE cmd_output IS NOT NULL
 
 DELETE @cmd_table
 
 FETCH NEXT FROM cur INTO @Table_Catalog, @Table_Schema, @Table_Name, @Table_Type
 END
 
CLOSE cur
DEALLOCATE cur
 
SELECT * FROM @results_table

Querying Active Directory / LDAP with SQL


Querying Active Directory or LDAP to obtain all user account is a little tedious, and the fact that you can only get 1000 records per query makes it a little difficult. Here is a script that will query AD for every combination of the three first letters of the account name, i.e. ‘AAA*’, ‘AAB*’, ‘ABA*’, ‘CAA*’…

Just remember to add the correct domain lame in the LDAP-query and you should be fine:

SET NOCOUNT ON

DECLARE @SearchTerm varchar(10)
DECLARE @RemoteQuery varchar(2000)
DECLARE @ErrCount int = 0
DECLARE @alphabet table (
Letter char(1)
)
INSERT INTO @alphabet (Letter)
VALUES
('0'), ('1'), ('2'), ('3'), ('4'), ('5'), ('6'), ('7'), ('8'), ('9'), ('A'), ('B'), ('C'), ('D'), ('E'),
('F'), ('G'), ('H'), ('I'), ('J'), ('K'), ('L'), ('M'), ('N'), ('O'), ('P'), ('Q'), ('R'), ('S'), ('T'),
('U'), ('V'), ('W'), ('X'), ('Y'), ('Z')


IF OBJECT_ID('tempdb..#AD_Users') IS NOT NULL
DROP TABLE #AD_Users

CREATE TABLE #AD_Users (
givenName varchar(255),
initials varchar(255),
sn varchar(255),
userPrincipalName varchar(255),
sAMAccountName varchar(255),
displayName varchar(255),
name varchar(255),
physicalDeliveryOfficeName varchar(255),
telephoneNumber varchar(255),
mail varchar(255)
)

DECLARE cur CURSOR FOR
SELECT
CONCAT(char1.Letter, char2.Letter, char3.Letter, '*')
FROM @alphabet char1
CROSS JOIN @alphabet char2
CROSS JOIN @alphabet char3
WHERE char1.Letter >= 'A'

OPEN CUR

FETCH NEXT FROM cur INTO @SearchTerm
WHILE (@@FETCH_STATUS = 0)
BEGIN

SET @RemoteQuery = '
SELECT
givenName, initials, sn, userPrincipalName, sAMAccountName, displayName, name, physicalDeliveryOfficeName, telephoneNumber, mail
FROM OPENQUERY( AD,
''SELECT givenName, initials, sn, userPrincipalName, sAMAccountName, displayName, name, physicalDeliveryOfficeName, telephoneNumber, mail
FROM ''''LDAP://domain.name''''
WHERE objectCategory = ''''User''''
AND sAMAccountName = ''''' + @SearchTerm + ''''''')'


BEGIN TRY
INSERT INTO #AD_Users
EXEC ( @RemoteQuery )
END TRY
BEGIN CATCH
SET @ErrCount = @ErrCount + 1
END CATCH

FETCH NEXT FROM cur INTO @SearchTerm
END

CLOSE cur
DEALLOCATE cur


SELECT * FROM #AD_Users

SELECT @ErrCount


 

Monitor progress of an SSIS Master package


Monitoring progress of an SSIS Master package (a package that runs other packages) is a painful process so I decided to take stab at writing a query that could pull it off by using the data found in the dbo.sysssislog tables.

For this script to work all packages must be set up with Log provider for SQL Server and it only works for “old school” package deployment mode. Output looks like this:2017-03-10 09_12_19-mRemoteNG - confCons.xml - 172.30.8.203 - Dev:

use msdb
go

DECLARE @ShowErrorMessages int = 0
DECLARE @MasterPackageName varchar(200) = 'MASTER_Stage'

DECLARE @id_min int = (SELECT MAX(id) FROM [BOS_NBDW_Config].[dbo].[sysssislog] WHERE event = 'PackageStart' AND source = @MasterPackageName)
DECLARE @id_max int = ISNULL((SELECT MAX(id) FROM [BOS_NBDW_Config].[dbo].[sysssislog] WHERE event = 'PackageEnd' AND source = @MasterPackageName AND id > @id_min), 999999999)

;WITH cte AS (
 SELECT 
 PackageStatus = CASE 
 WHEN event IN ('PackageStart','User:PackageStart') THEN '1 Running'
 WHEN event IN ('OnError','User:OnError') THEN '3 Error'
 ELSE '2 Completed'
 END,
 *
 FROM [dbo].[sysssislog]
 WHERE event in ('PackageStart','PackageEnd','User:PackageStart','User:PackageEnd', 'OnError','User:OnError')
 AND id BETWEEN @id_min AND @id_max
 )
SELECT 
 ID = MIN(ID), 
 source, 
 PackageStatus = MAX(PackageStatus),
 StartTime = MIN(StartTime),
 EndTime = CASE WHEN MAX(PackageStatus) = '1 Running' THEN NULL ELSE MAX(endtime) END,
 Elapsed = FORMAT(DATEADD(second, 
 CASE 
 WHEN MAX(PackageStatus) = '1 Running' THEN DATEDIFF(second, MIN(StartTime), GETDATE())
 ELSE DATEDIFF(second, MIN(StartTime), MAX(endtime))
 END, '1900-01-01'), 'd\d HH:mm:ss')
FROM cte
--WHERE source in (SELECT DISTINCT source FROM [dbo].[sysssislog] WHERE Event = 'PackageStart' and id >= @id)
GROUP BY source
--ORDER BY Elapsed desc
ORDER BY MIN(ID)

IF @ShowErrorMessages = 1
 SELECT DISTINCT
 [event]
 ,
 ,[starttime]
 ,endtime
 ,[message]
 FROM [BOS_NBDW_Config].[dbo].[sysssislog]
 WHERE event like '%error%'
 and id BETWEEN @id_min AND @id_max
 ORDER BY source

 

SSIS 2012 – easily copy environment variables to new servers or new environments


Following my investigations on SSIS 2012 and the issues mentioned in my previous blog post, create environment variables from project parameters, I created a script that easily lets you copy all the existing environment variables either to a new environment on the same server or to a new (or existing) environment on a completely different server.

Please note: this script will NOT make any changes to your environments by itself! It merely generates a sql script that has to be copy/pasted in to a SSMS query window and executed manually.

/*
This script will take all environment variables in an existing environment and make "insert" scripts 
out of them so you can easily deploy them to a new server or a new environment on the same server.

Script created by Henning Frettem, www.thefirstsql.com, 2013-05-28
*/

SET NOCOUNT ON

DECLARE 
	@folder_name nvarchar(200)				= 'ProjectFolderName',
	@environment_name_current nvarchar(200) = 'DEV',
	@environment_name_new nvarchar(200)		= 'TEST',
	@name sysname, 
	@sensitive bit, 
	@description nvarchar(1024), 
	@value sql_variant, 
	@type nvarchar(128)

PRINT 'DECLARE 
	@folder_id bigint,
	@environment_id bigint'

PRINT ''

--> Create folder if it doesn't exist and get folder_id
PRINT 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE name = N''' + @folder_name + ''')
	EXEC [SSISDB].[catalog].[create_folder] @folder_name=N''' + @folder_name + ''', @folder_id=@folder_id OUTPUT
ELSE 
	SET @folder_id = (SELECT folder_id FROM [SSISDB].[catalog].[folders] WHERE name = N''' + @folder_name + ''')'

PRINT ''

--> Create environment if it doesn't exist
PRINT 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environments] WHERE folder_id = @folder_id AND name = N''' + @environment_name_new + ''')
	EXEC [SSISDB].[catalog].[create_environment] @environment_name=N''' + @environment_name_new + ''', @folder_name=N''' + @folder_name + ''''

PRINT ''

--> Get the environment_id
PRINT 'SET @environment_id = (SELECT environment_id FROM [SSISDB].[catalog].[environments] WHERE folder_id = @folder_id and name = N''' + @environment_name_new + ''')'

PRINT ''

--> Making cursor because mapping of sql_variant datatype is different than the normal datatypes
DECLARE cur CURSOR FOR 
	SELECT c.name, c.sensitive, c.description, c.value, c.type
	FROM [SSISDB].[catalog].[folders] a
		INNER JOIN [SSISDB].[catalog].[environments] b
			ON a.folder_id =  b.folder_id
		INNER JOIN [SSISDB].[catalog].[environment_variables] c
			ON b.environment_id = c.environment_id
	WHERE a.name = @folder_name
		AND b.name = @environment_name_current

OPEN cur
FETCH NEXT FROM cur INTO @name, @sensitive, @description, @value, @type

PRINT 'DECLARE @var sql_variant'
PRINT ''

WHILE (@@FETCH_STATUS = 0)
	BEGIN
		PRINT 'SET @var = N''' + CONVERT(nvarchar(max), @value) + ''''

		PRINT 'IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environment_variables] WHERE environment_id = @environment_id AND name = N''' + @name + ''')
			EXEC [SSISDB].[catalog].[create_environment_variable] @variable_name=N''' + @name + ''', @sensitive=' + CONVERT(varchar(2), @sensitive) + ', @description=N''' + @description + ''', @environment_name=N''' + @environment_name_new + ''', @folder_name=N''' + @folder_name + ''', @value=@var, @data_type=N''' + @type + ''''

		PRINT ''

	FETCH NEXT FROM cur INTO @name, @sensitive, @description, @value, @type
	END

CLOSE cur
DEALLOCATE cur

SSIS 2012 – create environment variables from project parameters


A little late in the game perhaps but I just recently started looking in to SQL Server 2012 and especially SSIS, and there were some shortcomings (in my opinion) in regards to the configurations. The new deployment model with versioning and everything is utterly fantastic (!!), and making project parameters is easy peasy. However, when it comes to making the project parameters configurable across different environments it’s a different story. Assuming you already know the deal about the SSISDB catalog and the environments, I feel that the actual creation of the environment variables is very cumbersome. They are usually a very close replica of the design time project parameters, so to make the creation of the variables easier on myself I created a script that takes all the project parameters of a deployed project (deployed to your dev machine is sufficient), check if an environment variable with the same name exists and if it doesn’t then create it with the design time value.

Important: changes to the project parameters are only detected once the project is deployed to the Integration Services catalog!

/*
This script will take all project parameters with their design time values in your SSIS-project and create
an "insert script" for making them in to SSIS environment variables. It will NOT make any changes to
your databases!
  
Script created by Henning Frettem, www.thefirstsql.com, 2013-05-28
*/
  
DECLARE
    @folder_name nvarchar(200)      = 'ProjectFolderName',
    @environment_name nvarchar(200) = 'DEV',
    @parameter_name varchar(200),
    @design_default_value sql_variant,
    @sensitive bit, 
    @description nvarchar(1024), 
    @data_type nvarchar(128)
 
--> Checking that the folder exists
IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[folders] WHERE name = @folder_name)
    BEGIN
        RAISERROR('Provided folder name does not exist in the Integration Services catalog. Create the folder first, then run the script again', 16, 1)
        RETURN
    END
 
PRINT 'DECLARE 
    @folder_id bigint,
    @value sql_variant,
	@folder_name nvarchar(200)      = ''' + @folder_name + ''',
	@environment_name nvarchar(200) = ''' + @environment_name + ''''
 
PRINT ''
 
PRINT '--> Need folder_id for ''' + @folder_name + ''' 
    SET @folder_id = (SELECT folder_id FROM [SSISDB].[catalog].[folders] WHERE name = @folder_name)'
  
PRINT ''
 
PRINT '--> Create ' + @environment_name + ' environment in ''' + @folder_name + '''-folder if it does not exist
    IF NOT EXISTS (SELECT 1 FROM [SSISDB].[catalog].[environments] WHERE folder_id = @folder_id AND name = @environment_name)
		BEGIN
			EXEC [SSISDB].[catalog].[create_environment] @environment_name = @environment_name, @folder_name = @folder_name
			PRINT ''CREATED: Environment ' + @environment_name + ' in folder ' + @folder_name + '''
		END
	ELSE
		PRINT ''EXISTS: Environment ' + @environment_name + ' exists'''
 
PRINT ''
  
--> Create cursor for all project parameters
DECLARE cur CURSOR FOR
    SELECT a.parameter_name, a.design_default_value, a.sensitive, a.description, a.data_type
    FROM [SSISDB].[catalog].[object_parameters] a
        INNER JOIN [SSISDB].[catalog].[projects] b
            ON a.project_id = b.project_id
        INNER JOIN [SSISDB].[catalog].[folders] c
            ON b.folder_id = c.folder_id
    WHERE c.name = @folder_name
        AND SUBSTRING(a.parameter_name, 1, 3) <> 'CM.'
    ORDER BY a.parameter_name
  
OPEN cur
FETCH NEXT FROM cur INTO @parameter_name, @design_default_value, @sensitive, @description, @data_type
  
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        PRINT '--> Creating variable ''' + @parameter_name + ''' if it doesn''t exist'
        PRINT ' SET @value = N''' + CONVERT(nvarchar(max), @design_default_value) + ''''
        PRINT '
            IF NOT EXISTS (SELECT 1 
                            FROM [SSISDB].[catalog].[environments] b 
                                INNER JOIN [SSISDB].[catalog].[environment_variables] c
                                    ON b.environment_id = c.environment_id
                            WHERE b.folder_id = @folder_id 
                                AND b.name = @environment_name
                                AND c.name = ''' + @parameter_name + ''')
				BEGIN
					EXEC [SSISDB].[catalog].[create_environment_variable] 
						@variable_name = ''' + @parameter_name + ''', 
						@sensitive = ' + CONVERT(varchar(2), @sensitive) + ', 
						@description = ''' + @description + ''', 
						@environment_name = @environment_name, 
						@folder_name = @folder_name, 
						@value = @value, 
						@data_type = ''' + @data_type + '''
					PRINT ''CREATED: Environment-variable ' + @parameter_name + '''
				END
			ELSE
				PRINT ''EXISTS: Environment-variable ' + @parameter_name + ''''

		PRINT ''
  
    FETCH NEXT FROM cur INTO @parameter_name, @design_default_value, @sensitive, @description, @data_type
    END
  
CLOSE cur
DEALLOCATE cur

Querying msdb for job history


I recently had the need to create an SSRS report showing the status of some jobs from the 10 last days, so I wrote this script that I thought could come in handy to others as well. The contents should be pretty self explanatory except maybe for the RowNum column which is there to separate each iteration of the job:

WITH cte_jobhistory AS (
		--> Creating a cte only to make the columns run_date and run_time into a "datetime-friendly" format  
		--  so it can be used for filtering
		SELECT 
			RunDateString = SUBSTRING(CAST(run_date as VARCHAR(20)), 1, 4) + '-' +  SUBSTRING(CAST(run_date as VARCHAR(20)), 5, 2) + '-' + SUBSTRING(CAST(run_date as VARCHAR(20)), 7, 2),
			RunTimeString = SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 1, 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 3, 2) + ':' + SUBSTRING(RIGHT('000000' + CAST(run_time as VARCHAR(20)), 6), 5, 2),
			*
		FROM msdb.dbo.sysjobhistory
		)
SELECT 
	a.name,
	RowNum = ROW_NUMBER() OVER (PARTITION BY a.name, b.step_id ORDER BY instance_id DESC),
	b.step_id,
	b.step_name,
	b.message,
	RunStatus = CASE b.run_status 
		WHEN 0 THEN 'Failed'
		WHEN 1 THEN 'Succeded' 
		WHEN 2 THEN 'Retry' 
		WHEN 3 THEN 'Cancelled' 
		WHEN 4 THEN 'In Progress' 
		END,
	b.run_duration,
	b.RunDateString,
	b.RunTimeString
FROM msdb.dbo.sysjobs a
	INNER JOIN cte_jobhistory b
		ON a.job_id = b.job_id
WHERE CONVERT(datetime, b.RunDateString) > GETDATE()-10
ORDER BY a.name ASC, b.instance_id DESC