Here is a useful query for getting a list of failed jobs from SQL
Server. If you are responsible for tracking and logging job failures, this administrative work can be overwhelming in a busy week. This query will list all jobs that
failed during a given time period. It uses a function that I created,
convert_run_datetime.
<code>
select a.name, b.step_id, b.step_name,
max(master.dbo.convert_run_datetime(b.run_date,b.run_time)) max_rundt,
min(master.dbo.convert_run_datetime(b.run_date,b.run_time)) min_rundt,
count(*) number_of_failures
from sysjobhistory b, sysjobs a
where master.dbo.convert_run_datetime(b.run_date,b.run_time)
between ’07/24/2006 8:00:00 am’ and ’07/31/2006 9:00:00 am’
and b.run_status = 0
and b.step_id > 0
and a.job_id = b.job_id
group by a.name, b.step_id, b.step_name
</code>
powered by performancing firefox
Tags: Other, SQL Server, SQL Tricks
Loading your target incrementally offers a huge performance benefit over running full truncate/reloads, but there is a danger of missing inserts or updates in your source system. It can take hours or days to track down the source of these problems (if it can be done at all!) and problems are generally not found until the customer picks up on it.
So anything you can do to verify the target data with the source is good for your business. You can do simple rowcounts, but that’s an unreliable test. For example, if you’re missing one inserted row and one deleted row, your rowcount test will still pass.
A good checksum technique is a lightweight alternative that can be run asyncronously or run after the load is complete. In this example, I am querying a SQL Server target and using ODBC to connect to an Oracle source to get a list of keys that are not syncronized.
SELECT T.key, S.key, T.checksum, T.num_rows, S.checksum, S.num_rows
FROM (SELECT key, count(*) num_rows,
sum(len(char_col)+len(num_col)+num_col+...) checksum
FROM target_tablegroup by key) T
FULL OUTER JOIN
OPENQUERY(source_server,'
SELECT key, count(*) num_rows,
sum(length(char_col)+length(num_col)+num_col+...) checksum
FROM source_table
GROUP BY key') S ON T.key = S.key
WHERE S.num_rows <> T.num_rows
OR S.checksum <> T.checksum
Tags: ETL, Integration Services, Other
I don’t burn ISO images onto disk very often, but every time I do it seems like I spend an inordinate amount of time downloading, installing, and trying to use several utilities before I finally find one that works.
The search is over. ISO Recorder is free, fast, and easy to use. Dead easy. Thank you Alex Feinman. What a fantastic tool.
I recently wrote that I switched all my domains over from SRLNet to Dreamhost.com. I closed the account on 10/11 by jumping through all the hoops to file a ticket on their support system. When I received a bill 11/1, I replied to the automated email, and BANG! Instant response.
The reply was that dreamhost offers terrible support, oversells their servers, and their sites frequently go down. I already knew all this. You know why, because dreamhost told me. He told me that I might be saving money but that I would be losing support and performance.
At the time, I didn’t realize I was talking to the owner of the company. I thought it was just some support jockey. But I still wanted to be helpful and give him some feedback about his company — hoping that the information might make its way up the chain. So, I replied:
…For the record, your assumption that cost is my primary motivation is
wrong. I prefer their control panel. I like the subversion support. I
have received great support via their wiki and I am entertained by
their blog. SRLNet is missing all of these.
Who knows what was controversial about this, but the response indicates that I pushed some buttons. (Maybe he just hates dreamhost.)
cPanel is an extremely user friendly control panel, we’re here 24/7/365
to offer you our personal support. So you’re basically moving to a
terrible hosting provider because you like their blog.
The condesending demeanor … totally free of charge.
I didn’t get a chance to catch Michael Ault’s presentation at the recent TCOUG, but I heard it was a good talk. Thank you Michael for putting the slides online.
Tags: Data Warehousing, Oracle
The holy grail of a former .NET web developer is how to combine the techniques of agile and test-driven development with ETL tools and data volumes. Mark Rittman shows that he is a brother-in-arms with my quest.
Tags: Data Warehousing, ETL
This excellent piece in DMReview deliberates on some criteria for selecting (or justifying) an ETL tool. We are struggling with this right now. Our shop uses a combination of Informatica and SQL Server DTS for our ETL jobs. With Oracle Warehouse Builder 10gR2 and SQL Server Integration Services both released as viable alternatives, we need to justify our continued six-figure annual investment in Informatica. Is it worth it?
Tags: ETL, Informatica, Integration Services