Showing posts from December, 2016

65335 Factorial


Beware the Errors of SSIS

SSIS is a bit of a touchy subject where I work, and the more I use it, the more I sort of understand why.

While SQL Server is my background and I consider myself an expert in TSQL (not like, MVP level or anything), I don't do too much in SSIS. We have a home grown architecture which, in many ways, mimics functionality SSIS can do, and, for our purposes, usually works better and comes with fewer headaches. That said, there are some operations SSIS is simply hands down the best tool for the job. But I didn't come here to talk to you to day about when to or not to use SSIS. No dear reader, I come with a warning/complaint about how it surfaces error.

The description of how it surfaces errors is "not very well". It's undoubtedly something you get better at teasing out over time, but I think largely because of the breadth of systems it needs to interact with, surfacing meaningful errors for specific stages can often seem to have absolutely nothing to do with the error …

Big Crazy Linked Server Query

I got bored and was fiddling around with a statement I'd been doing manually in several steps to try to make it as easy to use (not read) and as quick as possible. The rundown is this:

    I have a query I want to run which spans two databases.

    The relevant schema for these two servers is as follows:

-- Server 1     -- Has about 500m rows     create table dbo.orders     (         order_id varchar(55) primary key clusterd,         Data varchar(8000) -- just a placeholder to show that there's other stuff in here     )     -- Server 2     create table dbo.orderLog     (         order_id varchar(55) primary key clustered,         PayloadId int unique,         OtherData varchar(8000) -- Placeholder for other columns.        --Not that it matters, but this data is different than the data on server 1     )     -- Has about 20 million rows     create table     (         RecordId int identity(1,1) primary key clustered,         PayloadId int,         TriggerDate dateti…