SSIS Control Flow Chart
- Load all tables to be used in this process to a cache
- Import data from cache to MT Tables, after making sure they are empty. The MT Tables serve as temporary database.
- Import excel data
ODVehiclesV1_1.xls Content
Vehicles
Database Mapping | Lookup | |||||||
Column | Description | Table | Field | DB Type | Table | Field | ||
DMS Code | DMS Vehicle Code | @XIS_CARS | U_IDMS_DMSCode | NVARCHAR(100) | ||||
Vehicle Code | Vehicle Code | @XIS_CARS | Code | NVARCHAR(50) | ||||
U_IDMS_DIM_Company | Dimension Company | @XIS_CARS | U_IDMS_DIM_COMPANY | NVARCHAR(30) | "IDMS_COMMONDEV211"."COMPANY" | |||
U_IDMS_DIM_Branch | Dimension Branch | @XIS_CARS | U_IDMS_DIM_Branch | NVARCHAR(30) | "IDMS_COMMONDEV211"."BRANCH" | |||
U_IDMS_DIM_Location | Dimension Location | @XIS_CARS | U_IDMS_DIM_Location | NVARCHAR(30) | "IDMS_COMMONDEV211"."LOCATION" | |||
U_IDMS_DIM_Make | Dimension Make | @XIS_CARS | U_IDMS_DIM_Make | NVARCHAR(30) | ||||
U_Mdl Code | Model Code | @XIS_CARS | U_MdlCode | NVARCHAR(50) | @XIS_MDLS | Code | ||
Make | Make Code | @XIS_CARS | U_Mnfctr | NVARCHAR(50) | @XIS_MFCTCD | Code | ||
Family | Famliy Code | @XIS_CARS | U_Grp | NVARCHAR(50) | @XIS_FMLYCR | Code | ||
Model | Model Description | @XIS_CARS | NVARCHAR(100) | |||||
Model as a comment | Model Description jus as a comment | @XIS_CARS | U_IDMS_ModelAsAComment | NVARCHAR(254) | ||||
VIN | VIN | @XIS_CARS | U_VCode | NVARCHAR(50) | ||||
U_OwnBP | Owner BP Code | @XIS_CARS | U_OwnBP | NVARCHAR(15) | OCRD | CardCode | ||
License plate | License Number | @XIS_CARS | U_LicNum | NVARCHAR(20) | ||||
Engine Number | Engine Number | @XIS_CARS | U_ECode | NVARCHAR(20) | ||||
Gear Number | Gear Number | @XIS_CARS | U_GCode | NVARCHAR(20) | ||||
First Registration Date | First Registration Date | @XIS_CARS | U_FstRegDate | TIMESTAMP | ||||
KBA Key | KBA Key | @XIS_CARS | U_KBAKey | NVARCHAR(100) | ||||
Interior Color - ODInteriorColor | Interior Color - IDInteriorColor | @XIS_CARS | U_IntrColor | NVARCHAR(254) | @XIS_INTRCLR | Code | ||
Manufacturer Interior Color Code | Manufacturer Interior Color Code | |||||||
Manufacturer Interior Color Name | Manufacturer Interior Color Name | |||||||
Interior Type - ODInteriorType | Interior Type - ODInteriorType | @XIS_CARS | U_IntrType | NVARCHAR(254) | @XIS_INTRTYPE | Code | ||
Exterior Color - ODExteriorColor | Exterior Color - ODExteriorColor | @XIS_CARS | U_CarColor | NVARCHAR(30) | @XIS_CAR_COLORS | Code | ||
Vehicle Status - ODVehicleStatus | Exterior Color - ODExteriorColor | @XIS_CARS | U_Status | NVARCHAR(50) | @XIS_CARSTATUS @IDMS_CUSTAPP_PARAMS | Code U_DfltValue | ||
Vehicle Type - ODCarType | Vehicle Type - ODCarType | @XIS_CARS | U_CarType | NVARCHAR(50) | @XIS_CARTYPES | Code | ||
Mileage (km) | Mileage (km) | @XIS_CARS | U_LastKm | DECIMAL (21,6) | ||||
Production Year | Production Year | @XIS_CARS | U_ProdYear | NVARCHAR(4) | ||||
Drive Side - 1 or 0 numeric1 | Drive Side - 1 or 2 1=Left | 2=Right | @XIS_CARS | U_DriveSide | NVARCHAR(1) | ||||
Warranty End Date | Warranty End Date | @XIS_CARS | U_WrntyDt | TIMESTAMP | ||||
Warranty Limitation (km) | Warranty Limitation (km) | @XIS_CARS | U_WrntyKm | INTEGER | ||||
Damaged | Damaged (Y/N) | @XIS_CARS | U_Damaged | NVARCHAR(1) | ||||
Engine Displacement | Engine Displacement | @XIS_CARS | U_EngDsplt | NVARCHAR(10) | @XIS_ENGINE_DSPLT | Code | ||
Gear Type - ODGearType | Gear Type - ODGearType | @XIS_CARS | U_GearType | NVARCHAR(50) | @XIS_GEAR_TYPE | Code | ||
Fuel Type - ODFuelTypeCode | Fuel Type - ODFuelTypeCode | @XIS_CARS | U_FuelType | NVARCHAR(30) | @XIS_FUEL_TYPES | Code | ||
Fuel Consumption (Combined) | Fuel Consumption (Combined) | @XIS_CARS | U_FulConCom | DECIMAL(21,6) | ||||
Fuel Consumption (Urban) | Fuel Consumption (Urban) | @XIS_CARS | U_CtyCycCon | DECIMAL(21,6) | ||||
Fuel Consumption (Extra Urban) | Fuel Consumption (Extra Urban) | @XIS_CARS | U_LngDisCon | DECIMAL(21,6) | ||||
Number Of Axles | Number Of Axles | @XIS_CARS | U_Axles | INTEGER | ||||
Cylinders | Cylinders | @XIS_CARS | U_Cylindrs | NVARCHAR(8) | ||||
Power (PS) | Power (PS) | @XIS_CARS | U_PowerPS | INTEGER | ||||
Top Speed | Top Speed | @XIS_CARS | U_TopSpeed | INTEGER | ||||
Acceleration | Acceleration | @XIS_CARS | U_Acclrtn | NVARCHAR(50) | ||||
Combined CO2 emissions (gr/km) | Combined CO2 emissions (gr/km) | @XIS_CARS | U_CO2EmsCom | DECIMAL(21,6) | ||||
Emissions Sticker | Emissions Sticker | @XIS_CARS | U_EXT_EmisSticker | NVARCHAR(50) | @XIS_EMISSTICKER | Code | ||
Number of Vehicles Owners | Number of Vehicles Owners | @XIS_CARS | U_EXT_NmOfPrevOwnr | NVARCHAR(15) | ||||
Has Full Service History - valid values Y N | Has Full Service History - valid values Y N | @XIS_CARS | U_EXT_FullServHis | NVARCHAR(1) | ||||
Last Maintenance Date | Last Maintenance Date | @XIS_CARS | U_LstMntncDt | TIMESTAMP | ||||
Last Maintenance Odometer | Last Maintenance Odometer | @XIS_CARS | U_LstMntncOd | DECIMAL(21,6) | ||||
Next Technical Inspection Date | Next Technical Inspection Date | @XIS_CARS | U_NxtTechInsDt | TIMESTAMP | ||||
Tax Group - ODTaxGroupCode | Tax Group - ODTaxGroupCode | @XIS_CARS | U_IDMS_VatProductGroupCode | NVARCHAR(50) | @IDMS_VATPRODGROUP | Code | ||
Has Climatisation - U_Climatisation(15) | Has Climatisation - U_Climatisation(15) | @XIS_CARS | U_Climatisation | NVARCHAR(15) | ||||
Has Park Assistance - U_ParkAssnts(15) | Has Park Assistance - U_ParkAssnts(15) | @XIS_CARS | U_ParkAssnts | NVARCHAR(15) | ||||
Metallic Colour - Y or N | Metallic Colour - Y or N | @XIS_CARS | U_IsClrMetallic | NVARCHAR(15) | ||||
Flags | Flags | |||||||
Vehicle Availability status | Vehicle Availability status | @XIS_CARS | U_IDMS_AvailabilityStatus | NVARCHAR(50) | ||||
Body style - ODBodyStyleCode | Body style - ODBodyStyleCode | @XIS_CARS | U_BdyStyle | NVARCHAR(50) | @XIS_BODYSTYLE | Code | ||
Drive type - U_4WDType | Drive type - U_4WDType | @XIS_CARS | U_4WDType | NVARCHAR(50) | ||||
Vehicle Length - U_LengthMM | Vehicle Length - U_LengthMM | @XIS_CARS | U_LengthMM | INTEGER | ||||
Vehicle Width - U_WidthMM | Vehicle Width - U_WidthMM | @XIS_CARS | U_WidthMM | INTEGER | ||||
Vehicle Height - U_HeightMM | Vehicle Height - U_HeightMM | @XIS_CARS | U_HightMM | INTEGER | ||||
Purchase date | Purchase Date | @XIS_CARS | U_PrchDate | TIMESTAMP | ||||
No of Doors | No of Doors | @XIS_CARS | U_DoorsNum | NVARCHAR(10) | ||||
No of Keys | No of Keys | @XIS_CARS | U_IDMS_NumOfKeys | INTEGER | ||||
Salable - Y or N valid values | Salable - Y or N valid values (Y/N) | @XIS_CARS | U_Salable | NVARCHAR(1) | ||||
Extended Warranty - 1 or 0 numeric1 | Extended Warranty - 1 or 0 numeric1 1=Yes | 0=No | @XIS_CARS | U_IDMS_ExtendedWarranty | SMALLINT | ||||
A/C Cleaning - 1 or 0 numeric1 | A/C Cleaning - 1 or 0 numeric1 1=Yes | 0=No | @XIS_CARS | U_IDMS_ACCleaning | SMALLINT | ||||
Road Assistance - 1 or 0 numeric1 | Road Assistance - 1 or 0 numeric1 1=Yes | 0=No | @XIS_CARS | U_IDMS_RoadAssistance | SMALLINT | ||||
Emission Check - 1 or 0 numeric1 | Emission Check - 1 or 0 numeric1 1=Yes | 0=No | |||||||
Available Only For Dealer - 1 or 0 numeric1 | Available Only For Dealer - 1 or 0 numeric1 1=Yes | 0=No | |||||||
VehicleImage | Vehicle image (p.e. veh1.jpg) | |||||||
Branch | Branch | @XIS_CARS | U_IDMS_Branch | NVARCHAR(100) | ||||
Location | Location | @XIS_CARS | U_IDMS_Location | NVARCHAR(100) | ||||
MarketPlaceDescription | @XIS_CARS | U_IDMS_MarketPlaceDesc | NVARCHAR(4000) | |||||
Variant | MarketPlace Variant | @XIS_CARS | U_IDMS_MarketPlaceVariant | NVARCHAR(50) | ||||
EUEmissionStandard | @XIS_CARS | U_EmdStdCd | NVARCHAR(254) | @IDMS_OPTIONFIELD | WHERE U_IDMS_Entity = 'Vehicle' AND U_IDMS_Field = 'EuroClassType' |
Vehicle Prices
Database Mapping | Lookup | |||||||
---|---|---|---|---|---|---|---|---|
Column | Description | Table | Field | Data Type | Table | Field | ||
Vehicle Code | ITM1 | ItemCode | NVARCHAR(50) | OITM | ItemCode | |||
Currency | Currency Code | ITM1 | Currency | |||||
Price1 | ITM1 | PriceList | DECIMAL(21,6) | |||||
Price2 | ITM1 | PriceList | DECIMAL(21,6) | |||||
Price3 | ITM1 | PriceList | DECIMAL(21,6) |
Options
Database Mapping | Lookup | |||||||
---|---|---|---|---|---|---|---|---|
Column | Description | Table | Field | Data Type | Table | Field | ||
DMSCode | Vehicle Code | @XIS_DESGINCODES | U_IDMS_DMSCode | NVARCHAR(50) | ||||
DMSCode | @XIS_DESGINCODES | Code | ||||||
DMSCode | @XIS_DESGINCODES | Name | ||||||
Name | Currency Code | |||||||
ModelCode | Model Code | |||||||
IsStandard | Is Standard Option (Y/N) | |||||||
IsChangeable | Is Changeable (Y/N) | @XIS_DESGINCODES | U_IDMS_IsChangeable | NVARCHAR(1) | ||||
IsPackage | Is Package (0/1) | |||||||
IncludedOptions | Included Option | |||||||
CO2Adjustment | CO2 Adjustment | |||||||
OptionType | OptionType | @XIS_DESGINCODES | U_IDMS_EXT_OptionType | NVARCHAR(50) | ||||
OptionCategory | Option Category | @XIS_DESGINCODES | U_IDMS_OptionCategory | NVARCHAR(50) | @IDMS_OPTIONCATEGORY | Code | ||
TaxGroupCode | Tax Group Code | @XIS_DESGINCODES | U_IDMS_EXT_TaxGroupCode | NVARCHAR(50) | ||||
VATProductGroup | Vat Product Group | |||||||
ModelTreeLevel | Model Tree Level | |||||||
NetPrice | Net Price | @XIS_DESGINCODES | U_IDMS_EXT_MsrpPrice | DECIMAL(21,6) | ||||
GrossPrice | Gross Price | @XIS_DESGINCODES | U_IDMS_EXT_DlntPrice | DECIMAL(21,6) | ||||
Weight | Weight | @XIS_DESGINCODES | U_IDMS_Weight | DECIMAL(21,6) | ||||
IsAttributeLHAC | Is Attribute in LH Configuration (0/1) | @XIS_DESGINCODES | U_IDMS_IsAttributeLHAC | INTEGER | ||||
RegistrationFee | Registration Fee | @XIS_DESGINCODES | U_IDMS_RegistrationFee | DECIMAL(21,6) | ||||
ParentCode | Parent Code | @XIS_DESGINCODES | U_IDMS_ParentCode | NVARCHAR(254) | ||||
DisplayInConfigurator | Display In Configuration | @XIS_DESGINCODES | U_IDMS_ShowInConfigurator | NVARCHAR(1) | ||||
OptionDefault | Option Default (Y/N) | @XIS_DESGINCODES | U_IDMS_OptionDefault | NVARCHAR(1) | ||||
CostPrice | Cost Price | @XIS_DESGINCODES | U_IDMS_Cost | DECIMAL(21,6) |
Flags
Database Mapping | Lookup | |||||||
---|---|---|---|---|---|---|---|---|
Column | Description | Table | Field | Data Type | Table | Field | ||
Code | Cost | @IDMS_FLAG | Code | NVARCHAR(50) | ||||
Name | Name | @IDMS_FLAG | Name | NVARCHAR(100) |
Vehicle Standard Options
Database Mapping | Lookup | |||||||
---|---|---|---|---|---|---|---|---|
Column | Description | Table | Field | Data Type | Table | Field | ||
Code | Vehicle Code | @XIS_CARSMDLSPECIF | Code | NVARCHAR(50) | @XIS_CARS | Code | ||
LineId | Line Id | @XIS_CARSMDLSPECIF | LineId | INTEGER | ||||
U_DesignCd | Option Code | @XIS_CARSMDLSPECIF | U_DesignCd | NVARCHAR(50) | ||||
U_DesignDs | Option Description | @XIS_CARSMDLSPECIF | U_DesignDs | NVARCHAR(254) | ||||
U_IDMS_DMSCode | Option DMS Code | @XIS_CARSMDLSPECIF | U_IDMS_DMSCode | NVARCHAR(50) | ||||
U_IDMS_EXT_OptionType | Option Type Valid values{Trim,Color,Option} | @XIS_CARSMDLSPECIF | U_IDMS_EXT_OptionType | NVARCHAR(50) | ||||
U_UnitPrice | Unit Price | @XIS_CARSMDLSPECIF | U_ListPrice | DECIMAL(21,6) | ||||
U_IDMS_Cost | Cost | |||||||
U_IDMS_OptionCategory | Option Category | @XIS_CARSMDLSPECIF | U_IDMS_OptionCategory | NVARCHAR(50) | @IDMS_OPTIONCATEGORY | Code |
Vehicle Images
Database Mapping | Lookup | |||||||
---|---|---|---|---|---|---|---|---|
Column | Description | Table | Field | Data Type | Table | Field | ||
VehicleCode | @XIS_CARS5 | Code | NVARCHAR(50) | |||||
VehicleImage | @XIS_CARS5 | U_FileLink | NVARCHAR(max) | |||||
SortOrder | @XIS_CARS5 | U_SortOreder | INTEGER | |||||
ImageGroup | @XIS_CARS5 | U_IDMS_Group | NVARCHAR(50) |
Vehicle Optional Options
Database Mapping | Lookup | |||||||
---|---|---|---|---|---|---|---|---|
Column | Description | Table | Field | Data Type | Table | Field | ||
Code | Vehicle Code | @XIS_CARSPECIF | Code | NVARCHAR(50) | @XIS_CARS | Code | ||
LineId | Line Id | @XIS_CARSPECIF | LineId | INTEGER | ||||
U_DesignCd | Option Code | @XIS_CARSPECIF | U_DesignCd | NVARCHAR(50) | ||||
U_DesignDs | Option Description | @XIS_CARSPECIF | U_DesignDs | NVARCHAR(254) | ||||
U_IDMS_DMSCode | Option DMS Code | @XIS_CARSPECIF | U_IDMS_DMSCode | NVARCHAR(50) | ||||
U_IDMS_EXT_OptionType | Option Type Valid values{Trim,Color,Option} | @XIS_CARSPECIF | U_IDMS_EXT_OptionType | NVARCHAR(50) | ||||
U_UnitPrice | Unit Price | @XIS_CARSPECIF | U_ListPrice | DECIMAL(21,6) | ||||
U_IDMS_Cost | Cost | @XIS_CARSPECIF | ||||||
U_EquipmentGroup | Equipment Group | @XIS_CARSPECIF | U_EquipmentGroup | NVARCHAR(100) | ||||
U_IDMS_IsPackage | Is Package (0/1) | @XIS_CARSPECIF | U_IDMS_IsPackage | INTEGER | ||||
U_IDMS_OptionCategory | Option Category | @XIS_CARSPECIF | U_IDMS_OptionCategory | NVARCHAR(50) | @IDMS_OPTIONCATEGORY | Code |
Add Comment