Afternoon all

@FaheemMitha Single-celled organisms can get contract rates in London - I've worked with a few. It's a combination of the silly money in the city and the chronic skill shortage.

What do you mean by "generate dynamic column"?

@ConcernedOfTunbridgeWells I'm obviously on the wrong continent. Are there any options for workng remotely?

Do we need so simple question?

I know the discussion about sending questions to SO and I prefer we have them stay here but this is such a simple join.

And look at all the other questions the OP has asked. They don't know how to join nor how to do a simple group by...

I think there should be such thing as "a question about SQL basics" and that should be off-topic here. We can argue about what is or isn't advanced SQL but surely there can be a point where everyone would agree that it's trivial. (And those who wouldn't would be those who don't have a clue.)

@FaheemMitha Not through the contract market. You would have to get a visa and show up in London.

xml or json?

To be or not to be?

@AndriyM There is a clause in the 'too localized' close reason: "...is not relevant to most of our audience..." which I think makes it's use valid for this sort of thing

What do you think @ypercube ^^^

I have already voted to close - with this option.

excellent - it does seem to me we need to draw the line somewhere

not everyone agrees though :(

@JackDouglas Seems fine to me. The only potential issue is we may not know for sure how many SQL beginners have become part of our audience. That clause sounds as if it means "not relevant to most of our intended audience".

@ConcernedOfTunbridgeWells Hmm. I figured.

@JackDouglas By the way, one of the things Leigh specifically said he was not suggesting was allowing questions that can be easily answered from documentation. I think he posted the question at the time when we had a different set of close reasons and, if I'm not much mistaken, one of them allowed us to close questions that could be easily answered from the manual.

May be it was "too localised"

So, I think there wouldn't be much disagreement with the part of our audience who agreed with Leigh if we went on applying "too localised" to, how shall I put it, exceedingly trivial questions.

@AndriyM that's fine, the community can close and reopen

it just precludes mod-action imo

the thing about Leigh's definition is that it is debatable if any specific SQL question can be answered from the docs or a simple search

I agree. Someone could still argue it might not apply here. "The docs give an example of A JOIN B, and I don't have A or B, I have C and D. There's no example for that in the manual."

party in the heap!

@billinkc Hey, have a question for you whenever you have a few minutes. No rush.

@James You rang?

@billinkc How happy are you with the project deployment model in SSIS 2012/2014? Do you still find it necessary to use the old package-level configurations in some cases?

I am an outlier, it seems, because I enjoy the project deployment model. Yes, we're redeploying EVERYTHING, from version control because I don't trust any of you. The setting of configurations and logging external to project development has also been a godsend because I struggled getting anyone to do it [right]

But no, have not encountered a case where I said "I wish I had a table/xml/environment/registry/parent to configure this thing". I take you're running into a case?

@billinkc Actually no -- just doing some studying and wondering if the project deployment model stuff works as well as the Fine Manual suggests. From your comments I take it that it's pretty good, but not popular for some reason?

The first bit of whinging I hear about is "why do I have to deploy everything, I just changed 1 package" To me, I see them treating an SSIS project as a .net project, your output/deliverable is this .ispac file. It's an assembly. You don't push a single c# file out in a patch, you provide an updated .dll It's now the same thing with projects. Changed one ssis package, cool, your quantum of deployment is this .ispac (zip file)

I think the root of the matter, people aren't certain of what's in production or it took some magical route to get there and by pushing all new bits there is somehow going to make old stuff break. So really, a process issue in my mind

Ah OK. So speaking of that, what platform do you like for source control with SSIS? I've heard lots of complaints about TFS integration with SSIS, but haven't tried it myself and don't remember the details.

You have the ability when you set up the SSISDB to specify retention rates, so you can go back N deployments if something went south. Heck, you can roll back to previous version, save that down and do an ever so pleasant package comparison

I hate tfs for version control

The checkout modify paradigm drives me crazy - oh, you wanted to look at the package, here we'll check it out

Nice, didn't know version history was a feature of SSISDB. Haven't gotten to that chapter yet. : )

But, there's nothing wrong per se with tfs as your backing store. I have a todo of setting up a CI process with ssis and tfs

At the last gig, our client has subversion which we were able to leverage the $Header$ property so that we could embed in the package's Description where/when this package came from

Why this is useful, the default reports from SSMS on your integration services tab, project or package level, will expose name and last deployed and description. I had originally wished for the version major/minor/build to be surfaced but once I got into the mindset of using the Description to link back to the actual point in version control, I no longer cared

The idea of having many configuration choices was nice but they all had their flaws. The new way is nice in that people don't have to think. It's there, use it, it just works. Plus, it natively handles encryption

@billinkc Excellent. Thanks for the detailed answer. Makes me regret not making it a question on DBA.se. I should get back to studying now.

No worries. Feel free to ping as questions arise

@billinkc The worst habit of TFS is checking out project files behind the scenes then dropping stuff off the projects when people check them back in. If you've got two conflicting checkouts with new contents there's a no-win situation and it's very easy for it to silently drop stuff on the floor. This is particularly toxic on database projects where it might not be obvious until you try to roll out a new environment. My experience is that you have to branch and merge everything.

Ugh, good to know but not what I'd want to hear about a version control system

@billinkc It works OK if you use branch/merge liberally - think one or more branches per developer. However, a lot of folks come off VSS and start trying to use it as if it was just VSS++. It works completely differently and treating it like VSS (using one trunk) doesn't work well at all.

Which describes my current client's approach

The downside of this is that any non-trivial TFS shop really needs a build nazi who knows how to use it properly. Essentially it creates yet another role for a specialised hierophant who's job it is to understand how a single tool works.

I sucked in the db model with SSDT and after 3 days of chewing on it, it gave up. So much, old, busted code in there. Crap entirely commented out. Known broken things called _ARCHIVE or some other such nonsense

But one I'm happy to pass off to another body because, it's a job I sure don't want

For all their faults, Makefiles are relatively transparent. You can see what it's going to do and what will get built.

@billinkc Unfortunately, that just empowers yet another petty bureaucrat to carve out their little empire.

The sun never sets on that empire

The problem with roles for 'tools guys' is that it tends to attract a certain type of person into the role. c.f. Informatica.

I have a theory that 'develpoper centric' tool sets are a good thing in that they keep tools guys' out of places where they can interfere with development projects.

@billinkc I'm still of the view that a versioned DB creation script and a series of version-version patching scripts is really the way to go for DB schemas. You can trivially, reliably and transparently roll out a target environment to any given version, and you can test the version upgrade process to validate that it actually does what it's supposed to by comparing an upgraded schema with a reference schema rolled out to the correct version.

I buy that. SSDT, for me, is just the mechanism for scripting all the objects into discrete entities. Use $VERSION_CONTROL to establish generations and I can then compare database to database or use the reference model in vcs. And while I'll might use the change scripts generated by that or any tool, you'd be a damned fool to blindly run the tool generated changes

@billinkc Yes, the main point for me is that you need a testing process for the change scripts themselves (which are quite likely to involve data migration if you have to retain the data). If nothing else, when you turn the scripts over to ops for deployment you want to be able to show that you've tested the script when they (inevitably) cock up the deployment and try to push the blame back onto you.

Present company excepted, of course. Did I mention that single-celled organisms can get contract rates in London? I've worked with a few.

On an unrelated note, setting up CUPS is still a pain in the rectum. I find myself yearning for the days of setting up custom ghostscript renderers via printcap.

Compiling custom builds with all the patented hinting and dithering algorithms enabled.

@FaheemMitha and it's a holiday weekend in the US and Canada, at least.

@ConcernedOfTunbridgeWells Why I gave up Linux on the desktop. Ain't got no time for that.

@James On some levels I prefer xfce or a trad window manager to Windows 7. Setting up trad unix infrastructure required you to understand it, but it was relatively straightforward and easy to understand. NIS (for example) was dead simple compared to OpenLDAP. CUPS is just obtuse.

Weird arithmetic. 10001 + 10 = 100011

I guess that happens when you think your ids as strings.

@AaronBertrand You are confused by datatype strictness. What's weird about?: ;-)

10001 = 1000+1  (the concat +)
1+10 = 11     (the arithmetic +)
1000+11 = 100011 (the concat + again)

@AaronBertrand Oh.

@ConcernedOfTunbridgeWells CUPs is not that bad. :-)