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
ODMasterDataV1_1.xls Content
- The Database Mapping columns contain the reference of the excel column in the database, where the data from the excel column is being stored
- The Lookup columns contain the database table from which the excel column retrieves data
BusinessPartners
Database Mapping | Lookup | |||||||
Column | Description | Table | Field | DB Type | Table | Field | ||
DMS Code | The code from customer's system(ERP-DMS) | OCRD | U_IDMS_DMSCode | NVARCHAR(50) | ||||
CardCode | Customer's code for OneDealer | OCRD | CardCode | NVARCHAR(15) | ||||
CardName | BP Name | OCRD | CardName | NVARCHAR(100) | ||||
CardType | Type C = Customer S = Vendor L = Lead | OCRD | CardType | NVARCHAR(1) | ||||
GroupCode | Group Code | OCRD | GroupCode | SMALLINT | ||||
CmpPrivate | Company or Private C = Company I = Private U = Unknown | OCRD | CmpPrivate | NVARCHAR(1) | ||||
Phone1 | Telephone 1 | OCRD | Phone1 | NVARCHAR(20) | ||||
Phone2 | Telephone 2 | OCRD | Phone2 | NVARCHAR(20) | ||||
CntctPrsn | Contact Person Name | OCRD | CntctPrsn | NVARCHAR(90) | ||||
LicTradNum | Federal Tax ID | OCRD | LicTradNum | NVARCHAR(32) | ||||
Currency | Currency Code | OCRD | Currency | NVARCHAR(3) | ||||
Cellular | Mobile Phone | OCRD | Cellular | NVARCHAR(50) | ||||
City | City | OCRD | City | NVARCHAR(100) | ||||
Country | Country Code | OCRD | Country | NVARCHAR(3) | ||||
E_Mail | OCRD | E_Mail | NVARCHAR(100) | |||||
CreateDate | Create Date | OCRD | CreateDate | TIMESTAMP | ||||
UpdateDate | Last Update Date | OCRD | ||||||
ECVatGroup | VAT Group | OCRD | ECVatGroup | NVARCHAR(8) | OVTG | Code | ||
LangCode | Language Code | OCRD | LangCode | INTEGER | OLNG | Code | ||
U_IDMS_DIM_Branch | Dimension Company | OCRD | U_IDMS_DIM_Branch | NVARCHAR(30) | "IDMS_COMMONDEV211"."BRANCH" | Code | ||
U_IDMS_DIM_Company | Dimension Branch | OCRD | U_IDMS_DIM_Company | NVARCHAR(30) | "IDMS_COMMONDEV211"."COMPANY" | Code | ||
U_IDMS_DIM_Location | Dimension Location | OCRD | U_IDMS_DIM_Location | NVARCHAR(30) | "IDMS_COMMONDEV211"."LOCATION" | Code | ||
U_IDMS_CustomVAT | Custom VAT | OCRD | U_IDMS_CustomVAT | DECIMAL(21,6) | ||||
U_IDMS_VatBusinessGroupCode | Vat Business Group Code | OCRD | U_IDMS_VatBusinessGroupCode | NVARCHAR(50) | IDMS_VATBUSGROUP | Code | ||
U_IDMS_FinancialGroup | Financial Group Code | OCRD | U_IDMS_FinancialGroup | NVARCHAR(50) | IDMS_FINANCIALGROUP | Code |
ContactPersons
Database Mapping | Lookup | |||||||
Column | Description | Table | Field | DB Type | Table | Field | ||
CardCode | BP Code | OCPR | CardCode | NVARCHAR(15) | OCRD | CardCode | ||
Name | Name | OCPR | Name | NVARCHAR(50) | ||||
Address | Address | OCPR | Address | NVARCHAR(100) | CRD1 or OCRD | Address | ||
Tel1 | Telephone 1 | OCPR | Tel1 | NVARCHAR(20) | ||||
Tel2 | Telephone 2 | OCPR | Tel2 | NVARCHAR(20) | ||||
Cellolar | Mobile Phone | OCPR | Cellolar | NVARCHAR(50) | ||||
OCPR | E_MailL | NVARCHAR(100) | ||||||
Gender | Gender E = Undefined F = Female M = Male | OCPR | Gender | NVARCHAR(1) | ||||
Title | Title | OCPR | Title | NVARCHAR(10) | ||||
Active | Is Active (Y/N) | OCPR | Active | NVARCHAR(1) | ||||
FirstName | First Name | OCPR | FirstName | NVARCHAR(50) | ||||
LastName | Last Name | OCPR | LastName | NVARCHAR(50) | ||||
U_IDMS_Gender | OD Gender E = gt_Undefined F = gt_Female M = gt_Male | OCPR | U_IDMS_Gender | NVARCHAR(30) | ||||
U_IDMS_DIM_Branch | Dimension Branch | OCPR | U_IDMS_DIM_Branch | NVARCHAR(30) | "IDMS_COMMONDEV211"."BRANCH" | |||
U_IDMS_DIM_Company | Dimension Company | OCPR | U_IDMS_DIM_Company | NVARCHAR(30) | "IDMS_COMMONDEV211"."COMPANY" | |||
U_IDMS_DIM_Location | Dimension Location | OCPR | U_IDMS_DIM_Location | NVARCHAR(30) | "IDMS_COMMONDEV211"."LOCATION" | |||
U_IDMS_Language | Language Code | OCPR | U_IDMS_Language | INTEGER | ||||
U_IDMS_Title | OD Title | OCPR | U_IDMS_Title | NVARCHAR(50) | ||||
U_IDMS_ContactCode | Contact person unique code | OCPR | U_IDMS_ContactCode | NVARCHAR(50) |
Addresses
Database Mapping | Lookup | |||||||
Column | Description | Table | Field | DB Type | Table | Field | ||
Address | Address Code | CRD1 | Address | NVARCHAR(50) | ||||
CardCode | BP Code | CRD1 | CardCode | NVARCHAR(15) | OCRD | CardCode | ||
LineNum | Line Number | CRD1 | LineNum | INTEGER | ||||
Street | Street | CRD1 | Street | NVARCHAR(100) | ||||
ZipCode | Zip Code | CRD1 | ZipCode | NVARCHAR(20) | ||||
City | City | CRD1 | City | NVARCHAR(100) | ||||
Country | Country Code | CRD1 | Country | NVARCHAR(3) | ||||
AdresType | Address Type S = Ship to B = Bill to | CRD1 | AdresType | NVARCHAR(1) | ||||
StreetNo | Street Number | CRD1 | StreetNo | NVARCHAR(100) | ||||
U_IDMS_ODCountryCode | OneDealer Country Code | |||||||
U_IDMS_ODCountry | OneDealer Country | CRD1 | U_IDMS_ODCountry | NVARCHAR(50) | OCRY | Name | ||
U_IDMS_ContactCode | CP Code | CRD1 | U_IDMS_ContactCode | NVARCHAR(50) | OCPR | U_IDMS_ContactCode | ||
U_IDMS_AddressCode | CRD1 | U_IDMS_AddressCode | NVARCHAR(50) | |||||
U_IDMS_DIM_Branch | Dimension Branch | CRD1 | U_IDMS_DIM_Branch | NVARCHAR(30) | "IDMS_COMMONDEV211"."BRANCH" | |||
U_IDMS_DIM_Company | Dimension Company | CRD1 | U_IDMS_DIM_Company | NVARCHAR(30) | "IDMS_COMMONDEV211"."COMPANY" | |||
U_IDMS_DIM_Location | Dimension Location | CRD1 | U_IDMS_DIM_Location | NVARCHAR(30) | "IDMS_COMMONDEV211"."LOCATION" | |||
U_IDMS_AddressType | OD Address Type 0 = Work 1 = Home 2 = Other | CRD1 | U_IDMS_AddressType | NVARCHAR(30) |
CustomFieldsBP
NOTE: CustomFiledsBP is an entity that enables us to add information dynamically. The entity's type defers, based on the needs of each customer
Database Mapping | Lookup | |||||||
Column | Description | Table | Field | DB Type | Table | Field | ||
U_IDMS_Entity | U_IDMS_Entity | @IDMS_CST_FIELD | U_IDMS_Entity | NVARCHAR(30) | ||||
U_IDMS_StrProperty1 | String Property 1 | @IDMS_CST_FIELD | U_IDMS_StrProperty1 | NVARCHAR(254) | ||||
U_IDMS_StrProperty2 | String Property 2 | @IDMS_CST_FIELD | U_IDMS_StrProperty2 | NVARCHAR(254) | ||||
U_IDMS_StrProperty3 | String Property 3 | @IDMS_CST_FIELD | U_IDMS_StrProperty3 | NVARCHAR(254) | ||||
U_IDMS_EntityValue | BP Code | @IDMS_CST_FIELD | U_IDMS_EntityValue | NVARCHAR(254) |
CustomFieldsCP
Database Mapping | Lookup | |||||||
Column | Description | Table | Field | DB Type | Table | Field | ||
U_IDMS_Entity | U_IDMS_Entity | @IDMS_CST_FIELD | U_IDMS_Entity | NVARCHAR(30) | ||||
U_IDMS_StrProperty1 | String Property 1 | @IDMS_CST_FIELD | U_IDMS_StrProperty1 | NVARCHAR(254) | ||||
U_IDMS_StrProperty3 | String Property 3 | @IDMS_CST_FIELD | U_IDMS_StrProperty3 | NVARCHAR(254) | ||||
U_IDMS_StrProperty9 | String Property 9 | @IDMS_CST_FIELD | U_IDMS_StrProperty9 | NVARCHAR(254) | ||||
U_IDMS_DateProperty1 | Date Property 1 | @IDMS_CST_FIELD | U_IDMS_DateProperty1 | TIMESTAMP | ||||
U_IDMS_ContactCode | @IDMS_CST_FIELD | |||||||
CardCode | BP Code | @IDMS_CST_FIELD | ||||||
CntctCode | CntctCode | @IDMS_CST_FIELD | U_IDMS_EntityValue | NVARCHAR(254) |
Template Excel file
The Sheets DataPrivMaster, and DataPrivDetail refer to GDPR - Import / Export Processes but are included in the above excel file
0 Comments