Migrate client attachments from other systems to ClientTrack
Introduction
It has become common when migrate data from another system to ClientTrack, beside structured data from Excel, CSV, SQL backup…, we also need to migrate documents associated to clients. The amount of these documents can be very large, up to many GBs of data and it used to take several hours or day to convert data from file into binary data in SQL table.
Engineering team has built a tool called Onboarding Blob Migration. This tool helps to transfer documents directly to Azure BLOB storage, reduce significantly the migration time down to less than an hour.
This article provides step-by-step guidelines about how to use Onboarding Blob Migration tool to migrate documents into ClientTrack
SStep 1: Upload files/documents to Eccovia BLOB container
- Open Microsoft Azure Storage Explorer. If you don’t have access to the Attached Containers, please submit an IT request.
- Go to Storage Accounts --> Attached Containers --> BLOB Containers --> Datamigrationsources-2(SAS)
- Add a new folder for the files to be uploaded. The folder name should contain client name, for example Chattanoogaattach.
- Upload files to that new folder. We can upload an entire folder with multiple sub-folders.
Step 2: Use Onboarding Blob Migration tool to upload data from the folder created in step #1 to each client environment. The tool will also create a table containing file name (includes folder path if any) and BLOB location. We will need this information in the scripting step later. See steps below to configure the tool:
- SQL Connection string: this is the database and server where the tool will create the table. Onboarding team use _ConversionData database on sqlmi-onb server, the connection string will be “Data Source=sqlmi-onb-wu2-prod-1.eccomain.local;Initial Catalog=_ConversionData;Integrated Security=True;Trust Server Certificate=True”. Click on Check Connection to make sure the connection string is correct.
- BLOB source connection string: Connect to the BLOB container source we upload file in step #1. Use the connection below: “DefaultEndpointsProtocol=https;AccountName=stonbsql1backupswu2prod1;AccountKey=DUxBjgTlFCkvth5WyBmxHZZjiOB0ZHLygyUJsLNTQ8SaFpktqUnL1Uafm3NHjM40Q0zi5TcdxLXVcC2TQi5t/w==;BlobEndpoint=https://stonbsql1backupswu2prod1.blob.core.windows.net/;”. Click on Load Blob to make sure BLOB connection string is correct and the tool will load the list of BLOB containers to select in the next step.
- Blob Container: select datamigrationsources from the list. Click on Load Folders to load the list of folders for the next step.
- BLOB Folders: Select folder created in step 1.c. In this example, it is chattanoogaattach
- Destination Blob Container: This will be the BLOB Container of the client environment that we are going to migrate files to. We can see the list of Containers in MS Azure Storage Explorer, under Azure Commercial folder à Storage Accounts à ctcustomerdata, scroll down to check if chattanoogarhc-conversion is in the list.
-
Click on Check Files to check how many files in the source container. If the number looks correct. Click Transfer All to initiate the file transfer from source BLOB container to client environment’s container.
Step 3: Once the transfer is complete, go to the database set up in step 2a and check the output table. The table name will be same to BLOB folder name, in this example it is chattanoogaattach
Step 4: Scripting We will use the BlobLocation field in the output table for FileUpload.FileLocation. Mapping logic will be varied for each project. This is an example we used for Chattanooga.
ALTER TABLE [_ConversionData].[dbo].[Chattanoogaattach] ADD ServerFileName VARCHAR(100);
GO
UPDATE [_ConversionData].[dbo].[Chattanoogaattach] SET ServerFileName = RIGHT(FileName, charindex('/', REVERSE(FileName)) - 1)
MERGE ClientFiles AS tar
USING ( SELECT c.ClientID, a.Client_ID, file_attachment_id, a.[File_Name], a.Server_FileName, o.OrgID AS OrgID
, MIN(a.date_created) AS date_created, MAX(a.date_updated) AS date_updated, u.UserID AS CreatedBy, uu.UserID AS UpdatedBy
FROM [_ConversionData].[dbo].[Chattanoogaattach] f
INNER JOIN [ChattanoogaRHC_Source].[dbo].[sp_file_attachment] a ON f.ServerFileName = a.server_filename
INNER JOIN IntegrationRemoteKey k On a.Client_ID = k.RemoteID AND k.TableName = 'cmClient' AND k.ColumnName = 'ClientID'
INNER JOIN cmClient c ON k.MyID = c.ClientID
INNER JOIN [_ConversionData].dbo.OrgMapping om ON a.provider_creating_id = om.providerid
INNER JOIN osOrganization o On om.OrgID = o.OrgID AND o.ActiveStatus <> 'D' --OR prntp.name = o.Organization
LEFT JOIN ChattanoogaRHC_Source.dbo.sp_user su1 ON a.user_creating_id = su1.user_id
LEFT JOIN osUsers u ON su1.Email = u.LoginID AND u.ActiveStatus <> 'D'
LEFT JOIN ChattanoogaRHC_Source.dbo.sp_user su2 ON a.user_updating_id = su2.user_id
LEFT JOIN osUsers uu ON su2.Email = uu.LoginID AND uu.ActiveStatus <> 'D'
GROUP BY c.ClientID, a.Client_ID, file_attachment_id, a.[File_Name], a.Server_FileName, u.UserID, uu.UserID, o.OrgID
) AS src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
ClientID, DocumentName, RestrictOrg, OrgID, CreatedBy, CreatedDate, UpdatedBy, UpdatedDate, ActiveStatus
)
VALUES (ClientID, LEFT([File_Name], 50) , 'X', OrgID, ISNULL(CreatedBy, 'DSI'), date_created, ISNULL(UpdatedBy, 'DSI'), date_updated, 'A')
OUTPUT 'ClientFiles', 'ClientDocumentID', inserted.ClientDocumentID, 4, CAST(src.Client_ID AS VARCHAR) + '_' + CAST(src.File_attachment_ID AS VARCHAR) , GETDATE() INTO IntegrationRemoteKey;
INSERT INTO FileUpload (Fromtable, LinkID, FileName, FileLocation, ContentType, RestrictOrg, OrgID, CreatedBy, CreatedDate, ActiveStatus)
SELECT 'Edit Client File', cf.ClientDocumentID, a.ServerFileName, a.BlobLocation
, CASE WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.doc' THEN 'application/msword'
WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.docx' THEN 'application/msword'
WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.GIF' THEN 'image/gif'
WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.jpeg' THEN 'image/jpeg'
WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.jpg' THEN 'image/jpeg'
WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.pdf' THEN 'application/pdf'
WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.PNG' THEN 'image/png'
WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.rtf' THEN 'application/rtf'
WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.xlsx' THEN 'application/vnd.ms-excel'
WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.xls' THEN 'application/vnd.ms-excel'
WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.txt' THEN 'text/plain'
WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.htm' THEN 'text/html'
WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.ppt' THEN 'application/vnd.ms-powerpoint'
WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.zip' THEN 'application/zip'
WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.bmp' THEN 'image/bmp'
WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.tiff' THEN 'image/tiff'
WHEN substring( server_fileName, charindex( '.', server_filename), 20) = '.tif' THEN ‘image/tif’
END
, cf.RestrictOrg, cf.OrgID, cf.CreatedBy, cf.CreatedDate, cf.ActiveStatus
FROM IntegrationRemoteKey k
INNER JOIN [ChattanoogaRHC_Source].dbo.sp_file_attachment f ON LEFT(k.RemoteID, CHARINDEX('_', k.RemoteID) - 1 ) = f.Client_ID
AND SUBSTRING(k.RemoteID, CHARINDEX('_', k.RemoteID) + 1 , 10 ) = f.file_attachment_id
INNER JOIN ClientFiles cf ON k.MyID = cf.ClientDocumentID
INNER JOIN [_ConversionData].[dbo].[Chattanoogaattach] a ON f.Server_FileName = a.ServerFileName
WHERE TableName = 'ClientFiles'
Step 2, 3, and 4 need to be repeated for each environment we want to migrate files to. Normally we will migrate to Conversion first for QA and UAT. Once it passes UAT, we will repeat the same steps for Production.