Fixing TCX files with the Garmin Trackpoint Time bug

Something that’s bugged me and perhaps some of the Garmin 1000 owners in the Strava community for a while is the failure of certain activities to sync.

As an avid triathlete, data analyst and technologist, one side of the sport I love is there is loads of data!

Frequently though, I return from a ride, upload the activity only to find that it won’t transfer to Strava. This is due to the trackpoint times for some inexplicable reason switching to July 4th 2019 half way through the file.

I’d manually corrected the file a few times, but eventually I’d had enough and put together a PowerShell script to do the work for me.

Continue reading “Fixing TCX files with the Garmin Trackpoint Time bug”

Include HTML in a SSIS Configuration File

This stumped me for a while, but a useful pointer from a colleague reminded me how this can be achieved…

Quite simply as the configuration file is XML we can use CDATA so that the content won’t be parsed. Here’s an example below:

<!-- Package replaces {SQLResults} with an HTML table showing Count by User for the last loaded date -->
<Configuration ConfiguredType="Property" Path="\Package.Variables[User::EmailBody].Properties[Value]" ValueType="String">
<ConfiguredValue>
<![CDATA["
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
</head>
<body>
<div style="background-color:#fff;padding:20px;">
<table cellpadding="0" cellspacing="0">
<tr>{Message}</tr>
<tr>{SQLResults}</tr>
</table>
</body>
</html>
"]]>
</ConfiguredValue>
</Configuration>

Using the CDATA attribute inside the XML nodes will allow you to add HTML or any other XML tags inside your configuration file.

The Developer DBA toolkit

Fellow developers are often surprised at how much I use command line and xp_cmdshell. The reason for this is two fold:

  1. It saves a lot of time because it means that you don’t always have to remote on to the server to view debugging information or execute local commands. e.g. Is XYZ service running? Who’s logged on to the box? Run that SSIS package.
  2. You can view results from a different security context which is often the key for successfully debugging connectivity issues.

I always keep a script just for the purpose which I consider to be an essential part of any database developer/DBA toolkit. This is a selection of tasks that crop up again and again and will often save you a lot of time. Especially if you use xp_cmdshell.

This post list a selection of tasks that come up again and again together with the necessary command lines to speed up the process!
Continue reading “The Developer DBA toolkit”

Isn’t it ironic

It’s quite ironic that being an ETL and database developer by trade, it has actually been easier to upgrade this website manually, literally copying and pasting each of the blog entries.

SSIS which is the integration tool I have on my current machine, has no easy way to insert data into a MySQL database. It can be done, but it means setting up a foreach container and mapping recordset parameters to an Execute SQL task. Far too much effort for a one off upgrade.

ASP.Net – Sending Email Asynchronously

I’ve spent a lot of time over the last few months building a website, and as always, there is much help and information available on the internet. Something I struggled with however was the sending of emails… How can I send an email from my site without blocking the calling process? “Use sendAsync!” I hear people shouting. Having tried this I can say that it doesn’t help where asp.net is concerned. I implemented it myself, and whilst it is almost seamless on a web server with fast connectivity to the smtp box, what I found is there are occasions where this just isn’t fast enough, and the user experience suffers. Here’s what I have done to resolve this… I hope it saves someone the head scratching that I went through 😉

Continue reading “ASP.Net – Sending Email Asynchronously”