@swasheck Please. No perl here. Wash your mouth out.

@ConcernedOfTunbridgeWells worse than Perl. i'm still fighting this friggin' XML

@swasheck Ah. XML. Now you have two problems.

@JNK ... so if i use this

CROSS APPLY x.event_data.nodes('//event/data[@name="showplan_xml"]/value/SP:ShowPlanXML') p(d)

do i get at elements like this?

p.d.value('(/SP:BatchSequence/SP:Batch/SP:Statements/SP:StmtSimple/@StatementSu‌​btreeCost)[1]','decimal(23,13)'),

I think you should be able to

if you just want to get VALUES like that

i'm more confused than an amish electrician

@ConcernedOfTunbridgeWells did you get @ypercube's message earlier?

@JackDouglas Yes. Shall we organise another drinkies?

would be good to put another date in the diary

Perhaps @Phil might like to join us if he feels up to it. Maybe staying a couple of days in sunny Kent would help?

@JackDouglas Maybe around the end of this month or the beginning of September?

hey @billinkc the SSISDB is a giant pain in the ass

@bluefeet Hush your mouth! That is the best thing ever for managing integration services

Not a high bar to clear, really

Idiot didn't turn on logging? Great, we have to modify the package which means it has to go through QA and then through the change control board again. F that

mmmmmm bar

@bluefeet She says as if he designed the damn thing.

@swasheck When are we doing lunch?

Same story with configuration. Numbskull didn't turn it on and now the package in production is trying to pull data from dev.

@MikeFal you tell me

Tomorrow

@bluefeet Tell me of your pain

At Slattery's

11:30

bahahahahahahahah

ok. works for me.

@billinkc what isn't my pain at this moment would be the better question

VtC, lacks sufficient detail ;)

haha

well we had ssis packages that were being executed via xp_cmdshell

that has been replaced with the ssisdb and I find it to be pain to work with

I have had nothing but the contrary to your experience

Were you doing something exceptional with xp_cmdshell invocations? Given its restrictive nature of double quoting things, I have trouble imagining it

@billinkc remind me to tell you the crazy stuff I'm doing with xpcmdshell right now

I just don't see an easy way to tell the success of a package

PS it also involves powershell, xml, and service borker

@bluefeet From where? A SQL Agent job or just generally, "this execution"

@billinkc we are creating an execution via [SSISDB].[catalog].[create_execution]

and then start it via [SSISDB].[catalog].[start_execution]

@JNK ... imma punch a kitten here soon.

<- too dumb for XML

I can't figure out how to tell the execution succeeded or failed

@swasheck still not working?

;with xmlnamespaces ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS SP)
select
	--q.p.value('.','nvarchar(255)') estimated_rows
	x.file_name,
	x.event_data,
	x.event_data.value('(/event/@timestamp)[1]','datetime2(3)') timestamp,
	s.pl.query('*') plan_xml
	--p.d.query('*'),
	-p.d.value('(/SP:BatchSequence/SP:Batch/SP:Statements/SP:StmtSimple/@StatementSubtreeCost)[1]','nvarchar(255)'),
	--p.d.value('(@Version)[1]','float') cost
	--,xe.event_data
	--from (
	--	select top 10 file_name, event_data

just gives me NULL for the costing ... oh well. it was a nice thought

is there a way for me to query these same tables?

i'm just dumping to another table

@JNK do you have an email address?

Perfect, so that generates an operation_id. Depending on whether you ran it synchronously or asynchronously you're either polling or waiting for the process to complete

actually

You can then run query(ies) against ssisdb.catalog.operation_messages to see what's going on (or look at the pretty reports or the dashboard itself).

You're also welcome to some of my scripts dropbox.com/sh/e92pfgoipketj88/AACRo2rP3k4lSHKWpERHgewAa/SSIS

give me a sec

you can ping me on tweeter

@JNK gist.github.com/swasheck/4d08362f72c38a23e96e

there's the xml file

perfect

one sec

@swasheck what ever happened to this project ?

@Kermit gave up

@JNK so ... i'd like to get all attributes for all StmtSimple nodes (in addition to the other crap that you helped me get)

ok

and here's what i'm currently trying (without success);

;with xmlnamespaces ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS SP)
select
	--q.p.value('.','nvarchar(255)') estimated_rows
	x.file_name,
	x.event_data,
	x.event_data.value('(/event/@timestamp)[1]','datetime2(3)') timestamp,
	s.pl.query('*') plan_xml,
	p.d.query('*'),
	p.d.value('(@StatementSubtreeCost)[1]','nvarchar(255)')


	from
	(
		select *
		from event_files
	) x
--cross apply x.event_data.nodes('//event/data[@name="estimated_rows"]/value') q(p)
CROSS APPLY x.event_data.nodes('//event/data[@name="showplan_xml"]/value') s(pl)

;with xmlnamespaces ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS SP)
select
	--q.p.value('.','nvarchar(255)') estimated_rows
	--x.file_name,
	--x.event_data,
	@x.value('(/event/@timestamp)[1]','datetime2(3)') timestamp,
	s.pl.query('*') plan_xml,
	--p.d.query('*'),
	p.d.value('@StatementSubTreeCost[1]','nvarchar(255)')
	--p.d.value('(@Version)[1]','float') cost
	--,xe.event_data
	--from (
	--	select top 10 file_name, event_data
	--	from event_files
	--) xe
	--cross apply
	--into event_files

ran against an @x xml with your xml from git in it, gives me the value fine

rinse and repeat for other attributes you want

hm. ok. thanks

sometimes values get hairy when you have a long ass path in them

@ConcernedOfTunbridgeWells Sounds good to me :)

if you put the long path in the cross apply it's a little easier to debug

@JNK nobody likes a hairy ass

@JNK you're quite the magician

@billinkc thanks for sharing that! The one to send email looks really useful.

@swasheck not really, I just had to deal with a lot of XML before

I honestly can't tell you WHY yours didn't work

@billinkc have you tried Jamie Thomson's SSIS reporting thingy?

@Gonsalu You misspelled awful but you're welcome

but I just knew it wasn't the way I would do it because I hate retyping long paths

hahahah

@Gonsalu I like it in concept. Last two places didn't allow me to change anything in the SSISDB (they wanted it in mint condition) so creating the custom schema and all that was out

@billinkc makes sense. I'm thinking of giving it a spin. I can do some changes to SSISDB in the place I'm now, and I feel that the integrated reports are a bit slow sometimes; I wonder if that reporting pack will be any better

Are you patched up?

ssistalk.com/2013/01/31/…

SP1 CU11 or something

I know that some missing indexes were created on some hotfix, but I still feel it's kind of slow...

@billinkc yeah, it's mentioned in that blog post

@billinkc do you use these files as templates in SSMS?

@ConcernedOfTunbridgeWells Sounds good?

@Gonsalu I do billfellows.blogspot.com/2013/03/ssms-templates.html

@billinkc sweet, thanks!

wozniak.ca/what-orms-have-taught-me-just-learn-sql

The Phil is back from his depression. Beware!

@billinkc if I download your SSMSTemplates folder from dropbox as a .zip file, Chrome thinks it's malicious and blocks it heh

@billinkc probably because of the .bat files

Maybe it is...

@Phil welcome back

@Phil welcome. good to see you here again.

@Gonsalu probably because it's from @billinkc

Been a shitty month :/ But I'm on the mend

sounds like it, but glad you're recovering

@Lamak probably :-)

@Phil how's that [ex]girlfriend?

@Kermit dude. really?

what? did i miss something?

Seems Zane's been moonlighting as TsSkTo

@kermit started seeing someone else a week after! I suspect foul play

@Phil thats a good thing right?

@swasheck it's ok.

k

Not for him :D

Poor b'stard

hahahahahahah

Was supposed to go to Legoland tomorrow. Gutted about that. Was looking forward to it more than the kid! I'll go alone another time :)

Everyone in my office is drooling over my Surface 3

@MikeFal won't that ruin it?

It's not an iPad though :P

@Phil In the US at least, there are adult only hours. Otherwise, you have to have a child with you to enter

@MikeFal looks fancy

@billinkc really? What has the world come to? :s

@Phil it's not that bad. Just get some candy or tell the lad/lass you lost your puppy and need their help and poof, you have a child. Wait a minute, someone's at the door

Thank you

@Phil I used to live just up the road from Legoland up 'til about 3 weeks ago. Never actually got around to going.

Where have you moved to?

olde english, please