Querying SSIS Parameter information from SSISDB

On your SSIS SQL Server you can run the following code against the SSISDB database to investigate parameter information stored in the SSIS catalog

There are multiple versions or the parameter stored in the interal.object_parameters table so this will get only the latest version of the each parameter/connection

The Query

/*
    Check package parameters and connection string settings
    for a project
*/
WITH projectVersions AS
(
    SELECT p2.name,p2.project_id, MAX(p1.project_version_lsn) versionlsn
    FROM internal.object_parameters p1
    INNER JOIN internal.projects p2 ON  p1.project_id = p2.project_id
    GROUP BY p2.name,p2.project_id
)
SELECT  CASE LEFT(params.parameter_name,3) 
        WHEN 'CM.' THEN
            'Connection Manager'
        ELSE
            'Package Parameter'     
        END as Area ,  
        params.object_name ,
        params.parameter_name ,
        params.parameter_data_type ,
        params.design_default_value ,
        params.default_value
FROM    SSISDB.internal.object_parameters params
        INNER JOIN projectVersions proj ON params.project_id = proj.project_id
WHERE   proj.versionlsn = params.project_version_lsn
--AND proj.name = 'EDI' -- change for another ssis project
ORDER BY 1

Example of the output

Area	object_name	parameter_name	parameter_data_type	design_default_value	default_value
Package Parameter	EDI	CommandString	String	C:\SSIS\	C:\SSIS\EDI
Package Parameter	EDI	ServerName	String	StockSource	NULL
Connection Manager	EDI	CM.EDI.ConnectionString	String	Data Source=x;Initial Catalog=x;Provider=SQLNCLI11;Integrated Security=SSPI;Auto Translate=False;	NULL

Find Diacritics (accent) marks in SQL Server

We had some issues where a downstream system was using usernames with diacritics in the URL. This was breaking the downstream system.
The following code may help you find diacritics in your data

/*
 Example script for how to find diacritical marks/accents in data
*/

/* Create some example data with and without accents */
DECLARE @exampledata TABLE (exampleString VARCHAR(50)) --has to be varchar
INSERT INTO @exampledata ( exampleString )
VALUES  (N'Peter'),(N'AURÉLIEN'),(N'JEREMY'),(N'kroužek'),(N'tomato'),(N'voël')

/*  Temp table for accent insensitive 
 Load with Accent insensitive collation */
DECLARE @insensitive TABLE (exampleString VARCHAR(50))
INSERT INTO @insensitive ( exampleString )
SELECT exampleString Collate SQL_Latin1_General_CP1253_CI_AI FROM @exampledata

/* Check for accent sensitive items */
SELECT  *
FROM    @exampledata s
WHERE   NOT EXISTS ( SELECT 1
                     FROM   @insensitive i
                     WHERE  i.exampleString = s.exampleString )
go

Create a table based on a temporary table schema

First of all, this is not really a good practice to do in your production environment but this is a script i created which allows you to create a composite table from a temporary table

This has quite a few limitations but generally gives you the basic structure of the table you want without keys, indexes, null contraints etc
This has been created to work with SQL Server 2012, if you need to make it work for older versions you will need to update the CONCAT function to use the old string concatenation
Replace your table names with the ones in the script

Heres the script

/*
 Intialise variables and set table names you want to create a table from
*/
DECLARE @columns VARCHAR(max)
DECLARE @createsql nVARCHAR(max)
DECLARE @tempTableName sysname
DECLARE @newTableName sysname
DECLARE @columnIterator int
DECLARE @columnName sysname
DECLARE @columnType VARCHAR(13)

SET @tempTableName = '#temp'
SET @newTableName = 'MYNEWTABLE'


/*
 Check the temp table exists
*/
IF OBJECT_ID(CONCAT('tempdb.dbo.',@tempTableName)) IS NULL
BEGIN
 PRINT 'Temp table does not exist'
 RETURN
END
 
/*
 Table variable to hold the columns
*/
DECLARE @cols TABLE ([TABLE_QUALIFIER] sysname, [TABLE_OWNER] sysname, [TABLE_NAME] sysname, [COLUMN_NAME] sysname, [DATA_TYPE] smallint, 
[TYPE_NAME] varchar(13), [PRECISION] int, [LENGTH] int, [SCALE] smallint, [RADIX] smallint, [NULLABLE] smallint, 
[REMARKS] varchar(254), [COLUMN_DEF] nvarchar(4000), [SQL_DATA_TYPE] smallint, [SQL_DATETIME_SUB] smallint, 
[CHAR_OCTET_LENGTH] int, [ORDINAL_POSITION] int, [IS_NULLABLE] varchar(254), [SS_DATA_TYPE] TINYINT
)

INSERT INTO @cols
EXEC tempdb..sp_columns @table_name = @tempTableName


/*
 build the create table statement using the columns we know about
*/
SELECT TOP 1 @columnIterator = ORDINAL_POSITION,@columnName = COLUMN_NAME, @columnType = TYPE_NAME
from @cols
ORDER BY ORDINAL_POSITION

WHILE 1=1
BEGIN

 SET @columns = CONCAT(@columns,' ',@columnName,' ', @columnType, ', ')

 SELECT TOP 1 @columnIterator = ORDINAL_POSITION,@columnName = COLUMN_NAME, @columnType = TYPE_NAME
 from @cols
 WHERE @columnIterator < ORDINAL_POSITION
 ORDER BY ORDINAL_POSITION

 IF @@ROWCOUNT = 0
  BREAK

END

SET @columns = left(@columns,LEN(@columns)-1)
SET @createsql = CONCAT('CREATE TABLE ', @newTableName, '( ',@columns,') ')

/*
 Create the table
*/
EXEC sp_executeSQL @statement = @createsql
go

Finding common column names in tables for UNION queries

Sometimes if you have tables with a lot of columns which you are trying to union, it can be hard to identify the columns they have in common and you may get this error a lot

Msg 205, Level 16, State 1, Line 1

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target

 One of the nice things you can do using the INFORMATION_SCHEMA views in SQL Server 2008+ is to query the schema to find the columns in common

SELECT  COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS H
WHERE   TABLE_NAME = ''
        AND EXISTS ( SELECT 1
                     FROM   INFORMATION_SCHEMA.COLUMNS W
                     WHERE  TABLE_NAME = ''
                            AND W.COLUMN_NAME = H.COLUMN_NAME )

 This will return the column names that your two tables/views have in common with output like below

You can then use this output to generate a select list for your UNION queries

Expanding date out from a specified date range

Overview

The following script allows you to get one row for every date in a range with extra information attached to that date.

This is an alternative way of building a date table in sql. You could also use this in a view with the date range hard-coded

It is also a very efficient way of expanding out the dates as the CTEs are like macros rather than temp tables so it doesn't actually create millions of rows for every date possibility.

The original script I found on http://stackoverflow.com/questions/1378593/get-a-list-of-dates-between-two-dates-using-a-function which I have added to.

Script

DROP FUNCTION [dbo].[udfExplodeDates]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  Jeremy
-- Create date: Sep 2012
-- Description: Explodes out each day in a date range to have one row per day, with some extra info
--    Use CTEs to join onto itself to create millions of rows and then uses the row_number
--    to figure out how many days to go from the start date
-- =============================================
CREATE FUNCTION [dbo].[udfExplodeDates](@startdate datetime, @enddate datetime)
returns table as
return (
with 
 R0 as (SELECT 1 as n UNION ALL SELECT 1) -- 2 rows
,R1 as (SELECT 1 as n FROM R0 t1, R0 t2) -- 4 rows
,R2 as (SELECT 1 as n FROM R1 t1, R1 t2) -- 16 rows
,R3 as (SELECT 1 as n FROM R2 t1, R2 t2) -- 256 rows
,R4 as (SELECT 1 as n FROM R3 t1, R3 t2) -- 65536 rows
,R5 as (SELECT 1 as n FROM R4 t1, R4 t2) -- 65536*65536 rows = 4,294,967,296 rows
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM R5)

SELECT DATEADD(day,num-1,@startdate) as the_date, 
    DATENAME(dw,DATEADD(day,num-1,@startdate)) as day_of_week,
    day(DATEADD(day,num-1,@startdate)) as day_of_month,
    EOMONTH(DATEADD(day,num-1,@startdate)) as end_day_of_month --only available in SQL2012 onward
FROM nums
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1

);

GO

Example Output

Query all database files that have potential disk bottlenecks

Overview

Microsoft generally recommends you disk io latency for sql server should average under 20ms for data and 15ms for Logs.

This query should show you all database files on your SQL Server instance that exceed the recommended thesholds.

According to SQL Server Central [http://www.sqlservercentral.com/articles/ssc+clinic/74097/]

• Less than 10 milliseconds (ms) = very good
• Between 10-20 ms = okay
• Between 20-50 ms = slow
• Greater than 50-100 ms = potentially serious IO bottleneck
• Greater than 100 ms = definite IO bottleneck

T-SQL Script

/*
 For SQL Versions older than 2012.. replace
sys.dm_io_virtual_file_stats(NULL, NULL)
with
sys.dm_io_virtual_file_stats(-1, -1)

*/
IF OBJECT_ID('tempdb..##dbFiles') IS NOT NULL 
    DROP TABLE ##dbFiles
GO

/* 
 table to hold all database files 
*/
CREATE TABLE ##dbFiles (
      DBName SYSNAME ,
      name VARCHAR(200) ,
      physical_name VARCHAR(2000) ,
      type_desc VARCHAR(200) ,
      state_desc VARCHAR(200) ,
      FILE_ID INT ) 
GO

/* 
 get all the database files for the sql instance
 into a table
*/
EXEC sp_msforeachdb 'use [?]
      insert into ##dbFiles
      select ''?'' as [DBName],name , physical_name,type_desc, state_desc,file_id
      from sys.database_files'

/*
 FIND FILES THAT HAVE UNACCEPTABLE DISK LATENCY
 AND JOIN TO DATABASE_FILES TABLE TO GIVE FRIENDLY NAMES
 THE VIRTUAL FILE STATS ARE FROM WHEN SQL SERVER WAS LAST RESTARTED

  > 20ms data and > 15ms logs
*/
DECLARE @DataReadLatency_ms INT,@DataWriteLatency_ms INT
DECLARE @LogReadLatency_ms INT,@LogWriteLatency_ms INT

SET @DataReadLatency_ms = 20
SET @DataWriteLatency_ms = 20
SET @LogReadLatency_ms = 15
SET @LogWriteLatency_ms = 15

SELECT  DB_NAME(database_id) AS 'db' ,
        df.file_id ,
        io_stall_read_ms / NULLIF(num_of_reads,0) AS 'AVG READ TRANSFER/SEC' ,
        io_stall_write_ms / NULLIF(num_of_writes,0) AS 'AVG WRITE TRANSFER/SEC' ,
        size_on_disk_bytes ,
        df.name ,
        df.physical_name ,
        df.type_desc
FROM    sys.dm_io_virtual_file_stats(NULL, NULL) stat
        JOIN ##dbFiles df ON stat.file_id = df.FILE_ID
                             AND df.DBName = DB_NAME(stat.database_id)
WHERE   num_of_reads > 0
        AND num_of_writes > 0
  -- ONLY INCLUDE UNACCEPTABLE LATENCY AND NOT DIV/0
        AND (( 
     ISNULL((io_stall_read_ms / NULLIF(num_of_reads,0)),0) > @DataReadLatency_ms AND type_desc = 'ROWS'
    OR 
              ISNULL((io_stall_write_ms / NULLIF(num_of_writes,0)),0) > @DataWriteLatency_ms AND type_desc = 'ROWS'
            )
   OR
   ( 
     ISNULL((io_stall_read_ms / NULLIF(num_of_reads,0)),0) > @LogReadLatency_ms AND type_desc = 'LOG'
    OR 
              ISNULL((io_stall_write_ms / NULLIF(num_of_writes,0)),0) > @LogWriteLatency_ms AND type_desc = 'LOG'
            ))
             
-- clean up
DROP TABLE ##dbFiles

Example of results returned by the script

Dropping active connections before restoring a database

When restoring test environment databases sometimes you just want to ignore all the current connections to the database and just restore it. 

You will normally see the message:

Msg 3702, Level 16, State 3, Line 1
Cannot drop database “AdventureWorks” because it is currently in use.

Some people use the approach of running and sp_who2 to see the active connections to that database and then run the KILL <SPID> command to drop the connections. This can be a bit tedious especially if there are a lot of connections and you need to kill each spid individually

To get around this you can drop the existing connections to your database using the command 
ALTER DATABASE [<YourDatabaseNameHere>] 
SET SINGLE_USER WITH ROLLBACK IMMEDIATE

T-SQL - AdventureWorks Example

The following is an example of dropping the AdventureWorks connections and then restoring the database

-- this command will drop the existing connections to the database
USE [master]
GO
ALTER DATABASE [AdventureWorks] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

-- normal restore command
RESTORE DATABASE [AdventureWorks] 
FROM  DISK = N'D:\Backups\AdventureWorks.bak' 
WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO