Skip to end of banner Go to start of banner

User Management Process

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
1. 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	Email
	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
	Email			IDMS_COMMONDEV211.USER	Email	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

No labels