I have a number of large conversion projects which will involve a one-time migration of VFP data to SQL Server 2005.

All of these projects involve PKs and FKs that are C(16) binary GUIDs. I believe the algorithm ( which we use in VFE ) was originally written by Rick Strahl.

My concern is upsizing these fields to SQL Uniqueidentifiers as part of a one-time conversion. ( future access of this data from VFP will involve GUID(36) which works fine with UIDs. )

Obviously, I can first construct GUID(36) from the GUID(16) in a consistant way and then convert that. ( the VFP upsizer, of course, will be useless, since even in the new and improved version I don't believe converting a C(36) much less a C(16) to UID is going to be an option )

I would like to use SSIS to move the data using VFPOLEDB in one move from the original tables without changing their structure. ( I have routines for replacing blank dates with nulls etc ) I also have Strataframe's DDT for deploying the SQL Data structures, so the pull will be into a SQL Database the already exists and has the tables to receive the data.

I seem to remember when working with DTS a couple years ago I was quite surprised to be able to get DTS to see the GUID(16) as a binary and convert it to UID but as I am very ignorant of the inner workings of both DTS and SSIS I don't seem to be able to reproduce that.

So, the question : Has anyone done this ( Rick ? ) or does anyone know of a CAST() that would get the GUID(16) into UID in one go ( and perhaps give me some hints about how to incorporate that knowledge into an SSIS package )

Guidance very much appreciated.