[SSIS] Finding your connection strings in SQL Server 2008/R2

"Bad artists imitate - great artists steal" Picasso Banksy

I initially wasn't sure if this was worth posting - if you have the same problem, you've probably come across the same links.  But since I don't see a soup-to-nuts version, here goes.

Problem: you're migrating servers, and need to see what SSIS packages might be affected, so you need the connection strings for all of your SSIS packages.  You could open up each package and check the connections - but that's... inefficient.

If you're storing the packages on disk, you could write a short (power)shell script that would scan the SSIS package files.  But if it's in MSDB, you'd need to pull the package XML out of the system tables, then parse the XML.  This is for 2008/R2 (and if you're not using SSISDB in 2012).  In 2012, SSISDB holds the files differently, and I'm awaiting an answer from one of the MS people on the SSIS team (thanks to Jamie Thomson sqlblog.com/blogs/jamie_thomson/‎ for forwarding my tweet to him)

Code cribbed from somebody, possibly Feodor Georgiev  (I found two links, below)
http://sqlconcept.com/2011/12/06/extracting-ssis-package-definition-from-msdb/
https://www.simple-talk.com/content/print.aspx?article=1408

and the XML parse was a question I asked on StackOverflow.  Thanks to Davmos for the help!
http://stackoverflow.com/questions/16824772/sql-server-query-xml-node-dtsconnectionmanager-dtsname-in-t-sql/16825308?noredirect=1#comment24269324_16825308

	DECLARE @DTS_Packages TABLE
	(
	PackageName SYSNAME,
	PackageDescription SYSNAME,
	Creator SYSNAME,
	CreateDate DATETIME,
	PackageXML XML
	)
	INSERT INTO @DTS_Packages
	SELECT p.[name] AS [PackageName],
	[description] AS [PackageDescription],
	l.[name] AS [Creator],
	p.[createdate],
	CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
	FROM [msdb].[dbo].[sysssispackages] p
	JOIN sys.syslogins l
	ON p.[ownersid] = l.[sid];
	--you either need ;WITH or the previous statement needs the semicolon for the
	--WITH NAMESPACE... which precludes a CTE
	WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS dts)
	SELECT [@DTS_Packages].PackageName,
	[@DTS_Packages].PackageDescription,
	[@DTS_Packages].Creator,
	[@DTS_Packages].CreateDate,
	[@DTS_Packages].PackageXML,
	Con.Str.value('.', 'varchar(500)') --aka give me that exact node
	FROM @DTS_Packages
	CROSS APPLY PackageXML.nodes('//dts:Property[@dts:Name="ConnectionString"]') AS Con(Str)

view raw gistfile1.sql hosted with ❤ by GitHub

[Powershell] Grabbing and parsing the RSS feed for new SQL Server Releases

(Updated 2019/04/18 - MS changed the site, moved to TLS 1.2)

My boss asked me to scrape a web site for release/security info for SQL Server.  Okay.

First attempt required a stop at stackoverflow, where I was reminded about hashtables (Thanks again to Grainer!)  And after several hours, I had found the correct Microsoft blog for security updates and had this:

cls
$posts= @() # this basically resets the posts for multiple runs - broke my ISE and good before

[xml]$rss = Invoke-WebRequest http://technet.microsoft.com/en-us/security/rss/comprehensive

$rss.SelectNodes('//item') | % {
    $posts += New-Object psobject -Property @{
        Title = $_.Title.Trim()
        Thedate = [datetime]$_.date.Trim()
        Guid = $_.Guid.Trim()
        Content = $_.Encoded."#cdata-section".Trim()
    }
}

$posts | select-object # | where-object {$_.Title -like '*sql*' -or $_.Guid -like '*sql*'}
#and then insert into a table

Awesome!  I feel good - mission accomplished.  Worked on it at home (yes, nerd here, big shock).
Then the boss looks and says "no, I didn't want that, I wanted the patches and CUs".

Crap.  So let's go find a different blog.  Oh yeah, I have one specifically for SP/CUs!  (In google reader, naturally...RIP.  OT: go to Newsblur and CommaFeed!)

Fortunately, this blog is much easier to parse, so I don't need the hashtable.  Well, not a massive hashtable.  The second one below is specifically to rename the "Description" field so that I don't have a field that's a semi-reserved word. The first one below is to reformat it as a datetime.  Use Chad Miller's awesome scripts to turn it into a datatable and write to disk, and voila!  (Alternatively, you should be able to use my Extensible Powershell Repository, elsewhere on this blog, and just have a 2-line PS script that is invoked via the EPR.

Now, I just have to use that with a MERGE statement to add only the new records, and email me when a new record comes in. 

# CREATE TABLE SQL_Server_Releases_from_RSS
#(
# title varchar(200),
# link varchar(500),
# pubdate smalldatetime,
# descript varchar(MAX) --I really don't want a field named "description"
#)
# Scripts from http://blogs.technet.com/b/heyscriptingguy/archive/2010/11/01/use-powershell-to-collect-server-data-and-write-to-sql.aspx
. C:\sql_tools\out-datatable.ps1  #Chad Miller rocks!
. C:\sql_tools\write-datatable.ps1
. C:\sql_tools\add-sqltable.ps1


#the next line, fixing the TLS ("could not create SSL/TLS secure channel") fixed by Chandan Rai on https://stackoverflow.com/questions/41618766/powershell-invoke-webrequest-fails-with-ssl-tls-secure-channel

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

#web page changed by MS, yet again, around 2019/04/15. Also moved to TLS 1.2.

[xml]$rss = Invoke-WebRequest https://techcommunity.microsoft.com/gxcuf89792/rss/board?board.id=SQLServer -UseBasicParsing  #was http://blogs.msdn.com/sqlreleaseservices/rss.xml, changed by mdb on 2018/03/19

$datatable = $rss.rss.channel.item | select title, link, @{Name="pubdate";Expression={get-date ($_.date) -format "yyyy/MM/dd hh:mm"}}, @{Name="descript";Expression={$_."Description"}} |out-datatable

write-datatable -ServerInstance "sql_repository" -Database myrepository -TableName SQL_Server_Releases_from_RSS -data $datatable

[Extended Events] list of predicates that you can use to filter actions

I've been using XE lately to get a list of servers that connect to a server I'm going to upgrade (see http://thebakingdba.blogspot.com/2013/04/extended-events-what-servers-are.html).  That way I know what the downstream effects are beforehand.

However, these are busy servers - even with connection pooling, we can get hundreds of connections a minute. Which means even minimal logging will get big, fast.

So there are two ways to prevent that:

1. Predicates to filter before it hits the target
2. Using a histogram to store in buckets.

However, a histogram can only store one value and a count.  I haven't figured a way to combine the fields (and according to Jonathan Kehayias you can't), so I requested Microsoft add a multi-field histogram (which got closed as Won't Fix: http://connect.microsoft.com/SQLServer/feedback/details/785063/extended-events-a-better-histogram-multiple-value-fields)

So, back to  #1, and looking at Predicates. 

Fortunately, I found this blog post which has a lot of details about it:
http://blogs.msdn.com/b/extended_events/archive/2010/06/24/today-s-subject-predicates.aspx
(yup, a 2-year-old post that's now incredibly relevant)


Specifically,

SELECT name, description,
    (SELECT name FROM sys.dm_xe_packages WHERE guid = o.package_guid) package
FROM sys.dm_xe_objects o
WHERE object_type = 'pred_compare'
ORDER BY name


Which lead me to:

sqlserver.like_i_sql_ansi_string
sqlserver.like_i_sql_unicode_string


From the blog post:
"Calling a pred_compare directly follows this syntax:

package_name.pred_compare(field/pred_source, value)

[...]ADD EVENT wait_info (WHERE package0.greater_than_max_int64(duration, 500)
 "


Okay, I can do that.

 WHERE (
              (sqlserver.like_i_sql_unicode_string(sqlserver.client_hostname,N'laptop%'))
              )

which gives me everything from our laptops.  Close.  Can I do a NOT LIKE?

 WHERE (
              (NOT sqlserver.like_i_sql_unicode_string(sqlserver.client_hostname,N'laptop%'))
              )


Which, yes, works.  
So my query to look for logins not from our laptops, and excluding certain servers, looks like...

CREATE EVENT SESSION [Logins] ON SERVER
ADD EVENT sqlserver.login(
ACTION(sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.database_name)
WHERE ([sqlserver].client_hostname NOT LIKE '%-SERV-%')
              AND (NOT sqlserver.like_i_sql_unicode_string(sqlserver.client_hostname,N'laptop%'))
              )
ADD TARGET package0.ring_buffer(SET max_memory = 4096)

And, typing this in... I see I'm already excluding using a LIKE.  Why'd I go through all this effort again?  Um, knowledge, I guess?  : )

[SSIS 2012] Using SQL Sentry & custom code to send detailed email on failure

(update 2013/07/31 dealing with "cancelled")

This is a simple modification of http://thebakingdba.blogspot.com/2012/11/sql-server-2012-ssis-getting-useful.html, which sends an email with specific job failure info, rather than the nondescript "To view the details for the execution, right-click on the Integration Services Catalog, and open the [All Executions] report ", which is pretty freaking useless.

We use SQL Sentry, which makes this part simple (they have their own method of logging SSIS failures, but it has several prereqs I don't like - specifically, it changes the step from SSIS to CmdExec, and executes the parameter-laden string).  My original post/SP required you to add a job step to each job - this one gets set on the server.  Just add an SQL Sentry action ("SQL Server Job Agent: Failure") and add "Execute SQL", running the following code, and it takes care of every job on the server. 

exec yourdbname.dbo.ssis_job_failure_info_fromjobname @job_name = '<%ObjectName%>'

(where yourdbname is wherever you store the SP)

As a bonus, I use the SQL Sentry email SP, so it appears to come from SQL Sentry.  That could be useful for you, based on your filtering.

One note: if the job fails because of an SSIS timeout (see: http://connect.microsoft.com/SQLServer/feedback/details/725840/catalog-executions-getting-cancelled-without-logging-any-error), no email is sent. That's because, while the job failed, technically the package doesn't know it failed... or something like that.  Read the Connect item for more details.  Supposedly fixed in SP1, but haven't had it happen up to now. but if it happens to you, there are a handful of fixes available.

1. Add the 5 indexes as per http://www.ssistalk.com/2013/01/31/ssis-2012-catalog-indexing-recommendations/
2. Change Retention period as per: http://www.made2mentor.com/2013/02/setup-and-performance-issues-with-the-integration-services-ssis-2012-catalog/ 
1. Don't just set it to 90 if you have a lot of history! You need to iterate through days, running the maint job between each.
3. Change DB size settings as per same post
4. Ensure Database is in SIMPLE mode.
5. (possibly turn on snapshot isolation)
6. (I also turn on Async Statistics Update) 
7. http://support.microsoft.com/kb/2829948 - There is a fix from MS!   SP1 CU4 has a fix.  It says it just adds indexes; it must change the delete SP, since adding the indexes did NOT fix it for me.

And here's the modified SP:

	/*
	SQL Server Agent Job: Failure
	Execute SQL:
	exec yourdbname.dbo.ssis_job_failure_info_fromjobname @job_name = '<%ObjectName%>'
	If performance is lacking, add the following indexes to SSISDB, as these tables
	do cascading deletes and are FKs and also aren't indexed. Index script found
	on a MS connect item
	CREATE NONCLUSTERED INDEX [NCIX_operation_id]
	ON [internal].[event_messages] ([operation_id]);
	GO
	CREATE NONCLUSTERED INDEX [NCIX_operation_id]
	ON [internal].[operation_messages] ([operation_id]);
	*/
	/****** Object: StoredProcedure [dbo].[ssis_job_failure_info_fromjobname] Script Date: 5/2/2013 8:12:30 AM ******/
	SET ANSI_NULLS ON
	GO
	SET QUOTED_IDENTIFIER ON
	GO
	CREATE PROCEDURE [dbo].[ssis_job_failure_info_fromjobname] @job_name VARCHAR(500)
	AS
	DECLARE @full_ssis_command VARCHAR(4000),
	@job_step_id INT,
	@package_name NVARCHAR(520),
	@tableHTML NVARCHAR(MAX),
	@MailSubject VARCHAR(200),
	@job_id UNIQUEIDENTIFIER,
	@job_description NVARCHAR(1024),
	@job_category SYSNAME
	SET NOCOUNT ON
	--2013/05/06 first released version. thebakingdba.blogspot.com.
	--No token replacement; grabs from SQL Sentry which passes the job name.
	--Get all the other relevant details - description, category
	--2013/05/08 mdb changed package_name to nvarchar(520), as per the system table.
	-- Also removing the extraneous call to table, and lengthening message to 500.
	SELECT @job_id = job_id,
	@job_description = sysjobs.[description],
	@job_category = syscategories.name
	FROM msdb.dbo.sysjobs
	INNER JOIN msdb.dbo.syscategories
	ON msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
	WHERE sysjobs.name = @job_name
	--determine which job_step failed.
	SELECT TOP 1
	@job_step_id = step_id
	FROM msdb.dbo.sysjobhistory
	WHERE run_status <> 1
	AND step_id > 0
	AND job_id = @job_id
	ORDER BY instance_id DESC
	--now find the package name
	SELECT @full_ssis_command = command
	FROM msdb.dbo.sysjobsteps
	WHERE job_id = @job_id
	AND step_id = @job_step_id
	IF @full_ssis_command LIKE '%.dtsx%'
	BEGIN
	SELECT @package_name = RIGHT(LEFT(@full_ssis_command,
	CHARINDEX('.dtsx',
	@full_ssis_command) - 1),
	CHARINDEX('\',
	REVERSE(LEFT(@full_ssis_command,
	CHARINDEX('.dtsx',
	@full_ssis_command)
	- 1))) - 1)
	+ '.dtsx'
	END
	IF @full_ssis_command LIKE '%.dtsx%'
	BEGIN
	--goes in the error log, if you have one
	SELECT [message_time],
	[extended_info_id],
	[package_name],
	[message_source_name],
	[subcomponent_name],
	[package_path],
	[execution_path],
	LEFT([message], 400)
	FROM ssisdb.[catalog].[event_messages]
	WHERE [package_name] = @package_name
	AND event_name = 'OnError'
	AND operation_id IN (
	SELECT MAX(operation_id)
	FROM ssisdb.[catalog].[event_messages]
	WHERE [package_name] = @package_name)
	ORDER BY message_time ASC
	SELECT @MailSubject = 'Job Failure on ' + @@servername + ': '
	+ @job_name
	SET @tableHTML =
	'<html><body>Job Description: ' + ISNULL(@job_description, N'') + N'<br>'
	+ N'Job Category:' + ISNULL(@job_category, N'') + N'<br><br><br><hr>'
	+ N'<H3>Error for job ' + @job_name + '</H3>'
	+ N'<table border="1">' + N'<th>Message_Time</th>'
	+ N'<th>Extended_info_id</th>' + N'<th>Package_Name</th>'
	+ N'<th>Message_Source_Name</th>' + N'<th>subcomponent_name</th>'
	+ N'<th>package_path</th>' + N'<th>execution_path</th>'
	+ N'<th>message</th>'
	+ CAST((SELECT td = CONVERT(VARCHAR(20), message_time, 120),
	'',
	td = CONVERT(VARCHAR(10), ISNULL(extended_info_id,
	'')),
	'',
	td = CONVERT(VARCHAR(50), RTRIM(LTRIM(ISNULL(package_name,
	'')))),
	'',
	td = CONVERT(VARCHAR(50), RTRIM(LTRIM(ISNULL([message_source_name],
	'')))),
	'',
	td = CONVERT(VARCHAR(50), RTRIM(LTRIM(ISNULL([subcomponent_name],
	'')))),
	'',
	td = CONVERT(VARCHAR(50), RTRIM(LTRIM(ISNULL([package_path],
	'')))),
	'',
	td = CONVERT(VARCHAR(50), RTRIM(LTRIM(ISNULL([execution_path],
	'')))),
	'',
	td = CONVERT(VARCHAR(400), RTRIM(LTRIM(LEFT(ISNULL([message],
	''), 400))))
	FROM ssisdb.[catalog].[event_messages]
	WHERE [package_name] = @package_name
	AND event_name = 'OnError'
	AND operation_id IN (
	SELECT MAX(operation_id)
	FROM ssisdb.[catalog].[event_messages]
	WHERE [package_name] = @package_name)
	FOR
	XML PATH('tr'),
	TYPE
	) AS NVARCHAR(MAX)) + N'</table></body></html>';
	--PRINT @tableHTML
	IF @tableHTML IS NOT NULL
	BEGIN
	EXEC msdb.dbo.sp_sentry_dbmail_20 @recipients = 'dev@null.com',
	@subject = @MailSubject, @body = @tableHTML,
	@body_format = 'HTML';
	END
	END
	GO

view raw gistfile1.txt hosted with ❤ by GitHub