iDrive UPS Process Data Migration

This article outlines the steps to generate, clean, and import UPS shipment data from iDrive into SQL Server Management Studio (SSMS).

1. Generate CSV Report

1. Access iDrive Audit.
2. Generate the CSV report based on Ship Date (at least three full weeks back).
3. e.g.
   1. 

2. Prepare CSV File

1. Open the generated CSV file.
2. Filter out long strings in the ‘Service’ column:
   • In E2, apply the formula: =LEFT(F2,40).
   • Copy the formula down the entire column.
   • Copy the entire E column and paste values over column F.
   • Rename the header of F to ‘Service’.
   • Delete Column E.
3. Remove unnecessary columns:
   • Delete columns O → Q and U → AH (removes invalid characters).
4. Save and close the file (it must be closed before import).

3. Import Data into SQL Server

1. Open SQL Server Management Studio 19 (SSMS).
2. Connect to RSI-SQL1-PROD server.
3. Open RSI_Live under Databases.
4. Go to File → Open → File and browse to UPS iDrive Queries.
5. Delete old records:
   • Run the highlighted query to delete records from the selected date range:
   • 
   • Replace '2024-11-01' with the start date of the current report.
   • Highlight the query and click Execute.

4. Import the CSV File

1. Right-click RSI_Live → Tasks → Import Data.
2. Choose Flat File Source as the import method.
3. Browse to the CSV file.
   1. 
4. Select SQL Server Native Client as the destination.
   1. 
5. Change destination table to RSI_iDrive_UPS_Shipments.
   1. 
6. Review for changed/invalid columns using the Edit Mapping button.

Final Steps

• Verify the imported data to ensure accuracy.
• If errors occur, check the formatting and special characters in the CSV file.

This process ensures that UPS shipment data is properly cleaned and imported into the database for reporting.