SSIS Control Flow Chart
- Import data from excel and the database to an intermediate database (OrgSt), after making sure it is empty.
- Validate that the data imported to the intermediate DB.
- Load all the tables to be used in the process to a cache
- Import the data to the database
- after you alter a table by importing data to it reload the table to a cache
Validations
- each record in OrgSt_OUSR has to either originate("Source") from the excel (E) or from the Database (D)
- each record in OrgSt_CM_User has to either originate("Source") from the excel (E) or from the Database (D)
- check if more than one users share the same Email
- each record in OrgSt_CM_UserLogin has to either originate("Source") from the excel (E) or from the Database (D)
- each record in OrgSt_CM_Location has to either originate("Source") from the excel (E) or from the Database (D)
Organizational_Structure_Dacia.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
OUSR
Database Mapping | Lookup | |||||||
---|---|---|---|---|---|---|---|---|
Column | Description | Table | Field | DB Type | Table | Field | ||
U_Name | OUSR | U_Name | ||||||
E_Mail | OUSR | E_Mail |
CM_Branch
Database Mapping | Lookup | |||||||
---|---|---|---|---|---|---|---|---|
Column | Description | Table | Field | DB Type | Table | Field | ||
Code | IDMS_COMMONDEV211.BRANCH | Code | VARCHAR(30) | |||||
Description | IDMS_COMMONDEV211.BRANCH | Description | VARCHAR(80) | |||||
Company | IDMS_COMMONDEV211.BRANCH | CompanyID | VARCHAR(30) | IDMS_COMMONDEV211.COMPANY | Code |
CM_Location
Database Mapping | Lookup | |||||||
---|---|---|---|---|---|---|---|---|
Column | Description | Table | Field | DB Type | Table | Field | ||
Code | IDMS_COMMONDEV211.LOCATION | Code | VARCHAR(30) | |||||
Description | IDMS_COMMONDEV211.LOCATION | Description | VARCHAR(80) | |||||
Branch | IDMS_COMMONDEV211.LOCATION | BranchID | VARCHAR(30) | IDMS_COMMONDEV211.BRANCH | BranchID | |||
Country | IDMS_COMMONDEV211.LOCATION | |||||||
City | IDMS_COMMONDEV211.LOCATION | |||||||
PostalCode | IDMS_COMMONDEV211.LOCATION | |||||||
Latitude | IDMS_COMMONDEV211.LOCATION | |||||||
Longitude | IDMS_COMMONDEV211.LOCATION | |||||||
Address | IDMS_COMMONDEV211.LOCATION |
CM_DimensionPermission
Database Mapping | Lookup | |||||||
---|---|---|---|---|---|---|---|---|
Column | Description | Table | Field | DB Type | Table | Field | ||
UserEmail | IDMS_COMMONDEV211.USER | |||||||
DimensionId | IDMS_COMMONDEV211.DIMENSIONPERMISSION | DimensionID | NVARCHAR(30) | |||||
DimensionValues | IDMS_COMMONDEV211.DIMENSIONPERMISSION | DimensionValues | NVARCHAR(4000) | |||||
Company | IDMS_COMMONDEV211.DIMENSIONPERMISSION | CompanyID | NVARCHAR(30) | IDMS_COMMONDEV211.COMPANY | Code |
CM_User
Database Mapping | Lookup | |||||||
---|---|---|---|---|---|---|---|---|
Column | Description | Table | Field | DB Type | Table | Field | ||
IDMS_COMMONDEV211.USER | VARCHAR(50) | |||||||
FirstName | IDMS_COMMONDEV211.USER | Firstname | VARCHAR(50) | |||||
LastName | IDMS_COMMONDEV211.USER | Lastname | VARCHAR(50) | |||||
IsInspector | IDMS_COMMONDEV211.USER | IsInspector | TINYINT | |||||
InterCompanyOperation | IDMS_COMMONDEV211.USER | InterCompanyOperation | TINYINT | |||||
IsDisabled | IDMS_COMMONDEV211.USER | IsDisabled | TINYINT | |||||
ChangePwsAtNextLogin | IDMS_COMMONDEV211.USER | TINYINT |
CM_UserGroup
Database Mapping | Lookup | |||||||
---|---|---|---|---|---|---|---|---|
Column | Description | Table | Field | DB Type | Table | Field | ||
UserEmail | IDMS_COMMONDEV211.USERGROUP | |||||||
Group | IDMS_COMMONDEV211.USERGROUP | GroupID | VARCHAR(30) | IDMS_COMMONDEV211.GROUP | GroupID | |||
Company | IDMS_COMMONDEV211.USERGROUP |
CM_UserLogin
Database Mapping | Lookup | |||||||
---|---|---|---|---|---|---|---|---|
Column | Description | Table | Field | DB Type | Table | Field | ||
UserEmail | IDMS_COMMONDEV211.USERLOGIN | |||||||
Company | IDMS_COMMONDEV211.USERLOGIN | CompanyID | VARCHAR(30) | IDMS_COMMONDEV211.COMPANY | CompanyID | |||
PreferredBranch | IDMS_COMMONDEV211.USERLOGIN | PreferredBranchID | VARCHAR(30) | IDMS_COMMONDEV211.BRANCH | BranchID | |||
PreferredLocation | IDMS_COMMONDEV211.USERLOGIN | PreferredLocationID | VARCHAR(30) | IDMS_COMMONDEV211.LOCATION | LocationID |
Add Comment