Tuesday 29 November 2011

Change encrypted password in data loader CLI

One of the processes I run regularly is a batch file calling Apex Data Loader to export a number of objects from my Salesforce org. I use these files to populate a fresh backup of the Salesforce data, and when my password changed recently I needed to generate a new encrypted string to use in the proces-conf.xml file. However, even though the encryption process is fairly simple, the documentation provided isn't clear.

Wednesday 9 November 2011

Salesforce Date fields are really DateTime fields

Migrating data into Salesforce takes up a lot of my time, and I use Apex Data Loader to carry out most of the heavy work. Usually I test migrate into my client's sandbox and then migrate into their production instance. However, one recent client insisted that their own support team carry out the final migration into production. So I handed over the csv files and my migration notes, but they struck a number of errors with environmental differences, especially dates.

Add a LastModified column to SQL Server table

I had a job recently where I needed to find the delta from a number of Microsoft Access database tables. The db designer had made a lot of interesting architectural decisions, but the biggest problem I had was finding which records had been touched in the past few months. It is easy enough to detect the changed tables, but at a field level I had to run a diff of the old and new copies and extract the changes using DiffMerge.

UPDATE table1 from table 2

I'm using a SQL Server staging area for my current ETL work and I find it easiest to update cleaned IDs using temporary tables that can be created and deleted as necessary. The syntax is simple and I use it in stored procs for persistent matched data and ad hoc queries for manual matching.

NOT IN sub-select query with NULL values

I was wondering why my NOT IN query was failing to return the expected amount of records, so I googled and found the 'SQL with Manoj' post at wordpress. If there are NULL values returned by your sub-select query then you need to handle the NULL values explicitly.