Integration Services Package

Name	Value	Expression	Description
User::AlertSystemEmailEnabled	Y		Configuration setting in SSIS_Configurations that determines if Alerts will be send via e-mail in case of an abend or data problems
User::DeleteFromDimension	DELETE FROM [EnterpriseDW] .[dbo].[DimPayee] WHERE PayeeSK IN ( SELECT PayeeSK FROM [dbo].[Obsolete_DimPayee] obs WHERE obs.DoNotDeleteFlag = 0 )	" DELETE FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "] .[dbo].[DimPayee] WHERE PayeeSK IN ( SELECT PayeeSK FROM [dbo].[Obsolete_DimPayee] obs WHERE obs.DoNotDeleteFlag = 0 ) "
User::Domain			Configuration setting from SSIS_Configuration to specify the domain where the e-mail server is running.
User::EDWDBName	EnterpriseDW		Configuration setting in SSIS_Configurations that specifies the name of the Enterprise Data Warehouse.
User::EDWLinkedServerName	N/A		If the Enterprise Data Warehouse resides on a different server from the DWStaging and DWoperations databases, a linked server must be defined and the name populated into the Var_EDWLinkedServername. If it resides on the same server, this setting must be set to ?N/A?. This is a configuration setting from SSIS_Configurations.
User::EmailAttachmentList			The list of files to be attached to the e-mail detailing record counts and data validation issues.
User::EmailBodyText	Testing (text maintained in SSIS Configurations table)		The text that will be coded in the body of the e-mail. This is an configuration setting in SSIS_Configurations that will be appended to the end of the e-mail.
User::EmailFileLocation	C:\DW Local Stuff\SSIS\		The directory on the File System where the files to be attached to the e-mail for notifications will be stored. This is a configuration setting from SSIS_Configurations.
User::EmailFrom	help@hetgroup.org		Configuration setting from SSIS_Configurations that will specify the From E-mail address to be used in e-mail notifications.
User::EmailPassword	datawarehouse		The configuration setting from SSIS_Configurations that specify the password used for the e-mail server when sending e-mail notifications.
User::EmailTo	ipeterso@hetgroup.org		The configuration setting from SSIS_Configurations that specify the To Email address when sending e-mail notifications. This cannot be multiple e-mail addresses, but can be a distribution list.
User::EmailUserName	windows		The configuration setting from SSIS_Configurations that specify the userid used for the e-mail server when sending e-mail notifications.
User::GetCustomerMods	DECLARE @IncrementalBeginDtTm datetime = 'Jan 1 1900 12:00AM' /* Date and Time to validate against RowUpdatedOn in Source Tables / DECLARE @IncrementalEndDtTm datetime = 'Mar 4 2016 10:33AM' SELECT CAST('C' + Cust.CUSTOMER_ID as varchar(14)) as PayeeAK ,Cust.CUSTOMER_ID AS PayeeId ,0 as PayeeSequence ,'C' as PayeeTypeCode ,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType ,CAST(ISNULL(Cust.CUSTOMER_NAME,'N/A') AS varchar(50)) AS PayeeName ,CAST(ISNULL(Cust.CUSTOMER_NAME,'N/A') AS varchar(50)) AS PayeeNameConsolidated ,CAST('Unknown' AS VARCHAR(10)) AS IsMinority ,CAST(ISNULL(AddrBA.STATE,ISNULL(AddrAD.STATE,ISNULL(Addr0.STATE,'N/A'))) AS VARCHAR(3)) AS StateCode ,CAST(ISNULL(StateDescBA.DESCRIPTION,ISNULL(StateDescAD.DESCRIPTION,ISNULL(StateDesc0.DESCRIPTION,'N/A'))) AS VARCHAR(30)) AS State ,CAST(ISNULL(AddrBA.ZIP,ISNULL(AddrAD.ZIP,ISNULL(Addr0.ZIP,'N/A'))) AS VARCHAR(11)) AS ZipCode ,CAST(CASE WHEN StateDescBA.DESCRIPTION IS NOT NULL THEN CountryDesc.DESCRIPTION WHEN StateDescBA.DESCRIPTION IS NULL AND StateDescAD.DESCRIPTION IS NOT NULL THEN CountryDesc.DESCRIPTION WHEN StateDescBA.DESCRIPTION IS NULL AND StateDescAD.DESCRIPTION IS NULL AND StateDesc0.DESCRIPTION IS NOT NULL THEN CountryDesc.DESCRIPTION ELSE 'N/A' END AS VARCHAR(30)) AS Country FROM AR_CUSTOMER_A Cust LEFT OUTER JOIN UTL_CODE_TABLE PayeeDesc ON ( PayeeDesc.TABLE_NAME = 'PAYEETYPE' AND PayeeDesc.CODE = 'C' AND PayeeDesc.STATUS = 'A' ) -- Get Billing Address LEFT OUTER JOIN AR_CUSTOMER_A_ADDRESS_DETAILS AddrBA ON ( Cust.ISN_AR_CUSTOMER_A = AddrBA.ISN_AR_CUSTOMER_A AND AddrBA.TYPE = 'BA' ) -- Get Physical Address LEFT OUTER JOIN AR_CUSTOMER_A_ADDRESS_DETAILS AddrAD ON ( Cust.ISN_AR_CUSTOMER_A = AddrAD.ISN_AR_CUSTOMER_A AND AddrAD.TYPE = 'AD' ) -- Get Billing Address LEFT OUTER JOIN AR_CUSTOMER_A_ADDRESS_DETAILS Addr0 ON ( Cust.ISN_AR_CUSTOMER_A = Addr0.ISN_AR_CUSTOMER_A AND Addr0.cnxarraycolumn = 0 ) LEFT OUTER JOIN UTL_CODE_TABLE StateDescBA ON ( StateDescBA.TABLE_NAME = 'STATE' AND StateDescBA.CODE = AddrBA.STATE AND StateDescBA.STATUS = 'A' ) LEFT OUTER JOIN UTL_CODE_TABLE StateDescAD ON ( StateDescAD.TABLE_NAME = 'STATE' AND StateDescAD.CODE = AddrAD.STATE AND StateDescAD.STATUS = 'A' ) LEFT OUTER JOIN UTL_CODE_TABLE StateDesc0 ON ( StateDesc0.TABLE_NAME = 'STATE' AND StateDesc0.CODE = Addr0.STATE AND StateDesc0.STATUS = 'A' ) LEFT OUTER JOIN UTL_CODE_TABLE CountryDesc ON ( CountryDesc.TABLE_NAME = 'COUNTRY-ST' AND CountryDesc.CODE = 'US' AND CountryDesc.STATUS = 'A' ) WHERE Cust.CUSTOMER_ID IN ( / Select changed records from Cust File */ SELECT CUSTOMER_ID FROM AR_CUSTOMER_A WHERE ( AR_CUSTOMER_A.RowUpdatedOn >= @IncrementalBeginDtTm AND AR_CUSTOMER_A.RowUpdatedOn <= @IncrementalEndDtTm AND AR_CUSTOMER_A.CUSTOMER_TYPE <> 'ST' ) ) ORDER BY PayeeAK, PayeeName	"DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */ DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "' SELECT CAST('C' + Cust.CUSTOMER_ID as varchar(14)) as PayeeAK ,Cust.CUSTOMER_ID AS PayeeId ,0 as PayeeSequence ,'C' as PayeeTypeCode ,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType ,CAST(ISNULL(Cust.CUSTOMER_NAME,'N/A') AS varchar(50)) AS PayeeName ,CAST(ISNULL(Cust.CUSTOMER_NAME,'N/A') AS varchar(50)) AS PayeeNameConsolidated ,CAST('Unknown' AS VARCHAR(10)) AS IsMinority ,CAST(ISNULL(AddrBA.STATE,ISNULL(AddrAD.STATE,ISNULL(Addr0.STATE,'N/A'))) AS VARCHAR(3)) AS StateCode ,CAST(ISNULL(StateDescBA.DESCRIPTION,ISNULL(StateDescAD.DESCRIPTION,ISNULL(StateDesc0.DESCRIPTION,'N/A'))) AS VARCHAR(30)) AS State ,CAST(ISNULL(AddrBA.ZIP,ISNULL(AddrAD.ZIP,ISNULL(Addr0.ZIP,'N/A'))) AS VARCHAR(11)) AS ZipCode ,CAST(CASE WHEN StateDes	This variable contain the SQL Query that will retrieve the data from the AR_CUSTOMER table to load into the DimPayee dimension.
User::GetEmployeeMods	DECLARE @IncrementalBeginDtTm datetime = 'Jan 1 1900 12:00AM' /* Date and Time to validate against RowUpdatedOn in Source Tables / DECLARE @IncrementalEndDtTm datetime = 'Mar 4 2016 10:33AM'SELECT CAST('E' + RIGHT('000000000' + CAST(Empl.PERSON_ID AS VARCHAR(9)),9) as varchar(14)) as PayeeAK ,CAST(Empl.PERSON_ID AS VARCHAR(9)) as PayeeId ,0 as PayeeSequence ,'E' as PayeeTypeCode ,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType , CAST(ISNULL(Empl.LASTNAME, 'N/A') + ', ' + ISNULL(Empl.FIRSTNAME, ' ') + ' ' + ISNULL(Empl.MIDDLE, ' ') AS varchar(50)) AS PayeeName ,CAST(ISNULL(Empl.LASTNAME, 'N/A') + ', ' + ISNULL(Empl.FIRSTNAME, ' ') + ' ' + ISNULL(Empl.MIDDLE, ' ') AS varchar(50)) AS PayeeNameConsolidated ,CAST('Unknown' AS VARCHAR(10)) AS IsMinority ,ISNULL(CAST(Addr.STATE_CODE AS VARCHAR(3)), 'N/A') AS StateCode ,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS State ,CAST(ISNULL(Addr.ZIP, 'N/A') AS VARCHAR(11)) as ZipCode ,CAST(ISNULL(CountryDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS Country FROM EMPLOYEE_A Empl LEFT OUTER JOIN UTL_CODE_TABLE PayeeDesc ON ( PayeeDesc.TABLE_NAME = 'PAYEETYPE' AND PayeeDesc.CODE = 'E' AND PayeeDesc.STATUS = 'A' ) LEFT OUTER JOIN EMPLOYEE_A_ADDRESS_INFO Addr ON ( Empl.ISN_EMPLOYEE_A = Addr.ISN_EMPLOYEE_A AND Addr.cnxarraycolumn = 0 ) LEFT OUTER JOIN UTL_CODE_TABLE StateDesc ON ( StateDesc.TABLE_NAME = 'STATE' AND StateDesc.CODE = Addr.STATE_CODE AND StateDesc.STATUS = 'A' ) LEFT OUTER JOIN UTL_CODE_TABLE CountryDesc ON ( CountryDesc.TABLE_NAME = 'COUNTRY-ST' AND CountryDesc.CODE = Addr.COUNTRY_CODE AND CountryDesc.STATUS = 'A' ) WHERE Empl.PERSON_ID IN ( / Select changed records from Empl File */ SELECT PERSON_ID FROM EMPLOYEE_A WHERE ( EMPLOYEE_A.RowUpdatedOn >= @IncrementalBeginDtTm AND EMPLOYEE_A.RowUpdatedOn <= @IncrementalEndDtTm AND EMPLOYEE_A.PERSON_ID > 0 ) ) ORDER BY PayeeAK, PayeeName	"DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */ DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "'SELECT CAST('E' + RIGHT('000000000' + CAST(Empl.PERSON_ID AS VARCHAR(9)),9) as varchar(14)) as PayeeAK ,CAST(Empl.PERSON_ID AS VARCHAR(9)) as PayeeId ,0 as PayeeSequence ,'E' as PayeeTypeCode ,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType , CAST(ISNULL(Empl.LASTNAME, 'N/A') + ', ' + ISNULL(Empl.FIRSTNAME, ' ') + ' ' + ISNULL(Empl.MIDDLE, ' ') AS varchar(50)) AS PayeeName ,CAST(ISNULL(Empl.LASTNAME, 'N/A') + ', ' + ISNULL(Empl.FIRSTNAME, ' ') + ' ' + ISNULL(Empl.MIDDLE, ' ') AS varchar(50)) AS PayeeNameConsolidated ,CAST('Unknown' AS VARCHAR(10)) AS IsMinority ,ISNULL(CAST(Addr.STATE_CODE AS VARCHAR(3)), 'N/A') AS StateCode ,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS State ,CAST(ISNULL(Ad	This variable contain the SQL Query that will retrieve the data from the EMPLOYEE_A table to load into the DimPayee dimension.
User::GetNonEmployeeMods	DECLARE @IncrementalBeginDtTm datetime = 'Jan 1 1900 12:00AM' /* Date and Time to validate against RowUpdatedOn in Source Tables / DECLARE @IncrementalEndDtTm datetime = 'Mar 4 2016 10:33AM' SELECT CAST('N' + RIGHT('000000000' + CAST(NonEmpl.PERSON_ID AS VARCHAR(9)),9) as varchar(14)) as PayeeAK ,CAST(NonEmpl.PERSON_ID AS VARCHAR(9)) as PayeeId ,0 as PayeeSequence ,'N' as PayeeTypeCode ,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType , CAST(ISNULL(NonEmpl.LASTNAME, 'N/A') + ', ' + ISNULL(NonEmpl.FIRSTNAME, ' ') + ' ' + ISNULL(NonEmpl.MIDDLE, ' ') AS varchar(50)) AS PayeeName , CAST(ISNULL(NonEmpl.LASTNAME, 'N/A') + ', ' + ISNULL(NonEmpl.FIRSTNAME, ' ') + ' ' + ISNULL(NonEmpl.MIDDLE, ' ') AS varchar(50)) AS PayeeNameConsolidated ,CAST('Unknown' AS VARCHAR(10)) AS IsMinority ,ISNULL(CAST(Addr.STATE_CODE AS VARCHAR(3)), 'N/A') AS StateCode ,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS State ,CAST(ISNULL(Addr.ZIP, 'N/A') AS VARCHAR(11)) as ZipCode ,CAST(ISNULL(CountryDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS Country FROM NON_EMPLOYEE_A NonEmpl LEFT OUTER JOIN UTL_CODE_TABLE PayeeDesc ON ( PayeeDesc.TABLE_NAME = 'PAYEETYPE' AND PayeeDesc.CODE = 'N' AND PayeeDesc.STATUS = 'A' ) LEFT OUTER JOIN NON_EMPLOYEE_A_ADDRESS_INFO Addr ON ( NonEmpl.ISN_NON_EMPLOYEE_A = Addr.ISN_NON_EMPLOYEE_A AND Addr.cnxarraycolumn = 0 ) LEFT OUTER JOIN UTL_CODE_TABLE StateDesc ON ( StateDesc.TABLE_NAME = 'STATE' AND StateDesc.CODE = Addr.STATE_CODE AND StateDesc.STATUS = 'A' ) LEFT OUTER JOIN UTL_CODE_TABLE CountryDesc ON ( CountryDesc.TABLE_NAME = 'COUNTRY-ST' AND CountryDesc.CODE = Addr.COUNTRY_CODE AND CountryDesc.STATUS = 'A' ) WHERE NonEmpl.PERSON_ID IN ( / Select changed records from NonEmpl File */ SELECT PERSON_ID FROM NON_EMPLOYEE_A WHERE ( NON_EMPLOYEE_A.RowUpdatedOn >= @IncrementalBeginDtTm AND NON_EMPLOYEE_A.RowUpdatedOn <= @IncrementalEndDtTm AND NON_EMPLOYEE_A.PERSON_ID > 0 ) ) ORDER BY PayeeAK, PayeeName	"DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */ DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "' SELECT CAST('N' + RIGHT('000000000' + CAST(NonEmpl.PERSON_ID AS VARCHAR(9)),9) as varchar(14)) as PayeeAK ,CAST(NonEmpl.PERSON_ID AS VARCHAR(9)) as PayeeId ,0 as PayeeSequence ,'N' as PayeeTypeCode ,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType , CAST(ISNULL(NonEmpl.LASTNAME, 'N/A') + ', ' + ISNULL(NonEmpl.FIRSTNAME, ' ') + ' ' + ISNULL(NonEmpl.MIDDLE, ' ') AS varchar(50)) AS PayeeName , CAST(ISNULL(NonEmpl.LASTNAME, 'N/A') + ', ' + ISNULL(NonEmpl.FIRSTNAME, ' ') + ' ' + ISNULL(NonEmpl.MIDDLE, ' ') AS varchar(50)) AS PayeeNameConsolidated ,CAST('Unknown' AS VARCHAR(10)) AS IsMinority ,ISNULL(CAST(Addr.STATE_CODE AS VARCHAR(3)), 'N/A') AS StateCode ,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS	This variable contains the SQL Query that will retrieve the data from the NON_EMPLOYEE_A table to load into the DimPayee dimension.
User::GetStudentMods	DECLARE @IncrementalBeginDtTm datetime = 'Jan 1 1900 12:00AM' /* Date and Time to validate against RowUpdatedOn in Source Tables / DECLARE @IncrementalEndDtTm datetime = 'Mar 4 2016 10:33AM' SELECT CAST('S' + Student.STUDENT_ID as varchar(14)) as PayeeAK ,Student.STUDENT_ID as PayeeId ,0 as PayeeSequence ,'S' as PayeeTypeCode ,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType , CAST(ISNULL(Student.LST_NM, 'N/A') + ', ' + ISNULL(Student.FRST_NM, ' ') + ' ' + ISNULL(Student.MDL_NM, ' ') AS varchar(50)) AS PayeeName , CAST(ISNULL(Student.LST_NM, 'N/A') + ', ' + ISNULL(Student.FRST_NM, ' ') + ' ' + ISNULL(Student.MDL_NM, ' ') AS varchar(50)) AS PayeeNameConsolidated ,CAST('Unknown' AS VARCHAR(10)) AS IsMinority ,ISNULL(CAST(StdntAddr.STATE AS VARCHAR(3)), 'N/A') AS StateCode ,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS State ,CAST(ISNULL(StdntAddr.ZIP_CD, 'N/A') AS VARCHAR(11)) as ZipCode ,CAST(ISNULL(CountryDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS Country FROM ST_STDNT_A Student LEFT OUTER JOIN UTL_CODE_TABLE PayeeDesc ON ( PayeeDesc.TABLE_NAME = 'PAYEETYPE' AND PayeeDesc.CODE = 'S' AND PayeeDesc.STATUS = 'A' ) LEFT OUTER JOIN ST_ADDRESSES_A StdntAddr ON ( Student.STUDENT_ID = StdntAddr.STUDENT_ID AND StdntAddr.ADDR_TY = '01' ) LEFT OUTER JOIN UTL_CODE_TABLE StateDesc ON ( StateDesc.TABLE_NAME = 'STATE' AND StateDesc.CODE = StdntAddr.STATE AND StateDesc.STATUS = 'A' ) LEFT OUTER JOIN UTL_CODE_TABLE CountryDesc ON ( CountryDesc.TABLE_NAME = 'COUNTRY-ST' AND CountryDesc.CODE = StdntAddr.CNTRY AND CountryDesc.STATUS = 'A' ) WHERE Student.STUDENT_ID IN ( / Select changed records from Student File / SELECT STUDENT_ID FROM ST_STDNT_A WHERE ( ST_STDNT_A.RowUpdatedOn >= @IncrementalBeginDtTm AND ST_STDNT_A.RowUpdatedOn <= @IncrementalEndDtTm ) UNION / Select changed records from Student Address File */ SELECT STUDENT_ID FROM ST_ADDRESSES_A WHERE ( ST_ADDRESSES_A.ADDR_TY = '01' AND ST_ADDRESSES_A.RowUpdatedOn >= @IncrementalBeginDtTm AND ST_ADDRESSES_A.RowUpdatedOn <= @IncrementalEndDtTm ) ) ORDER BY PayeeAK, PayeeName	"DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */ DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "' SELECT CAST('S' + Student.STUDENT_ID as varchar(14)) as PayeeAK ,Student.STUDENT_ID as PayeeId ,0 as PayeeSequence ,'S' as PayeeTypeCode ,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') as PayeeType , CAST(ISNULL(Student.LST_NM, 'N/A') + ', ' + ISNULL(Student.FRST_NM, ' ') + ' ' + ISNULL(Student.MDL_NM, ' ') AS varchar(50)) AS PayeeName , CAST(ISNULL(Student.LST_NM, 'N/A') + ', ' + ISNULL(Student.FRST_NM, ' ') + ' ' + ISNULL(Student.MDL_NM, ' ') AS varchar(50)) AS PayeeNameConsolidated ,CAST('Unknown' AS VARCHAR(10)) AS IsMinority ,ISNULL(CAST(StdntAddr.STATE AS VARCHAR(3)), 'N/A') AS StateCode ,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS State ,CAST(ISNULL(StdntAddr.ZIP_CD, 'N/A') AS VARCHAR(11)) as Zi	This variable contains the SQL Query that will retrieve the data from the ST_STDNT_A table to load into the DimPayee dimension.
User::GetVendorMods	DECLARE @IncrementalBeginDtTm datetime = 'Jan 1 1900 12:00AM' /* Date and Time to validate against RowUpdatedOn in Source Tables / DECLARE @IncrementalEndDtTm datetime = 'Mar 4 2016 10:33AM' SELECT CAST('V' + Vendor.VA_VENDOR_CODE + (RIGHT('0000' + CAST(Vendor.VA_VENDOR_SEQ_NBR AS VARCHAR(4)),4)) AS VARCHAR(14)) as PayeeAK ,Vendor.VA_VENDOR_CODE AS PayeeId ,Vendor.VA_VENDOR_SEQ_NBR AS PayeeSequence ,'V' AS PayeeTypeCode ,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') AS PayeeType ,CAST(ISNULL(Vendor.VA_MAILING_ADDRESS_NAME, 'N/A') AS VARCHAR(50)) AS PayeeName ,CAST(ISNULL(VendorIsn.VENDOR_NAME,'N/A') AS VARCHAR(50)) AS PayeeNameConsolidated ,CAST( CASE WHEN Minority.MINORITY_CODE = NoMinority.FIELD_VALUE THEN 'No' ELSE 'Yes' END AS VARCHAR(10)) AS IsMinority ,ISNULL(CAST(VENDOR.VA_STATE AS VARCHAR(3)), 'N/A') AS StateCode ,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS State ,CAST(ISNULL(VENDOR.VA_ZIP, 'N/A') AS VARCHAR(11)) as ZipCode ,CAST(ISNULL(CountryDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS Country FROM FIP_VENDOR Vendor LEFT OUTER JOIN UTL_CODE_TABLE PayeeDesc ON ( PayeeDesc.TABLE_NAME = 'PAYEETYPE' AND PayeeDesc.CODE = 'V' AND PayeeDesc.STATUS = 'A' ) LEFT OUTER JOIN UTL_CODE_TABLE StateDesc ON ( StateDesc.TABLE_NAME = 'STATE' AND StateDesc.CODE = VENDOR.VA_STATE AND StateDesc.STATUS = 'A' ) LEFT OUTER JOIN UTL_CODE_TABLE CountryDesc ON ( CountryDesc.TABLE_NAME = 'COUNTRY-ST' AND CountryDesc.CODE = VENDOR.VA_COUNTRY AND CountryDesc.STATUS = 'A' ) / Find the No Minority code from the MNRTY-CAT table to check against / LEFT OUTER JOIN UTL_CODE_TABLE NoMinorityCode ON ( NoMinorityCode.TABLE_NAME = 'MNRTY-CAT' AND NoMinorityCode.CODE = '7' AND NoMinorityCode.STATUS = 'A' ) LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC NoMinority ON ( NoMinority.ISN_UTL_CODE_TABLE = NoMinorityCode.ISN_UTL_CODE_TABLE and NoMinority.cnxarraycolumn = 0 ) / Get the Vendor Header to get the ISN to check the Minority Code / LEFT OUTER JOIN FIP_VENDOR VendorIsn ON ( VendorIsn.VENDOR_CODE = Vendor.VA_VENDOR_CODE ) LEFT OUTER JOIN FIP_VENDOR_MINORITY_CODE Minority ON ( Minority.ISN_FIP_VENDOR = VendorIsn.ISN_FIP_VENDOR and Minority.cnxarraycolumn = 0 ) WHERE Vendor.VA_VENDOR_CODE IN ( / Select changed records from Empl File */ SELECT VA_VENDOR_CODE FROM FIP_VENDOR WHERE ( FIP_VENDOR.RowUpdatedOn >= @IncrementalBeginDtTm AND FIP_VENDOR.RowUpdatedOn <= @IncrementalEndDtTm AND FIP_VENDOR.VA_VENDOR_CODE <> ' ' ) UNION SELECT VENDOR_CODE FROM FIP_VENDOR WHERE ( FIP_VENDOR.RowUpdatedOn >= @IncrementalBeginDtTm AND FIP_VENDOR.RowUpdatedOn <= @IncrementalEndDtTm AND FIP_VENDOR.VENDOR_CODE <> ' ' ) ) ORDER BY PayeeAK, PayeeName	"DECLARE @IncrementalBeginDtTm datetime = '" + @[User::IncrementalBeginDtTm] + "' /* Date and Time to validate against RowUpdatedOn in Source Tables */ DECLARE @IncrementalEndDtTm datetime = '" + @[User::IncrementalEndDtTm] + "' SELECT CAST('V' + Vendor.VA_VENDOR_CODE + (RIGHT('0000' + CAST(Vendor.VA_VENDOR_SEQ_NBR AS VARCHAR(4)),4)) AS VARCHAR(14)) as PayeeAK ,Vendor.VA_VENDOR_CODE AS PayeeId ,Vendor.VA_VENDOR_SEQ_NBR AS PayeeSequence ,'V' AS PayeeTypeCode ,ISNULL(PayeeDesc.DESCRIPTION, 'N/A') AS PayeeType ,CAST(ISNULL(Vendor.VA_MAILING_ADDRESS_NAME, 'N/A') AS VARCHAR(50)) AS PayeeName ,CAST(ISNULL(VendorIsn.VENDOR_NAME,'N/A') AS VARCHAR(50)) AS PayeeNameConsolidated ,CAST( CASE WHEN Minority.MINORITY_CODE = NoMinority.FIELD_VALUE THEN 'No' ELSE 'Yes' END AS VARCHAR(10)) AS IsMinority ,ISNULL(CAST(VENDOR.VA_STATE AS VARCHAR(3)), 'N/A') AS StateCode ,CAST(ISNULL(StateDesc.DESCRIPTION, 'N/A') AS VARCHAR(30)) AS State ,CAS	This variable contains the SQL Query that will retrieve the data from the FIP_VENDOR table to load into the DimPayee dimension.
User::IncrementalBeginDtTm	Jan 1 1900 12:00AM		The IncrementalBeginDtTm setting will contain the start date and time for which updates will be extracted. This is a configuration setting from SSIS_Configurations that are set in the Control Package.
User::IncrementalEndDtTm	Mar 4 2016 10:33AM		The IncrementalEndDtTm setting will contain the End Date and Time for which updates will be extracted. These values will be compared against the RowUpdatedOn column on the tables in the DWStaging database to determine which rows to extract and load into the Enterprise Data Warehouse.
User::NewInsertDimPayee	/* SQL INSERT into DimPayee from New Temp Table */ INSERT INTO [EnterpriseDW].[dbo].[DimPayee] (PayeeAK ,PayeeId ,PayeeSequence ,PayeeTypeCode ,PayeeType ,PayeeName ,PayeeNameConsolidated ,IsMinority ,StateCode ,State ,ZipCode ,Country ,RowIsCurrent ,RowStartDate ,RowEndDate) SELECT PayeeAK ,PayeeId ,PayeeSequence ,PayeeTypeCode ,PayeeType ,PayeeName ,PayeeNameConsolidated ,IsMinority ,StateCode ,State ,ZipCode ,Country ,RowIsCurrent ,RowStartDate ,RowEndDate FROM dbo.Staged_dbo_DimPayee__New	" /* SQL INSERT into DimPayee from New Temp Table */ INSERT INTO " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimPayee] (PayeeAK ,PayeeId ,PayeeSequence ,PayeeTypeCode ,PayeeType ,PayeeName ,PayeeNameConsolidated ,IsMinority ,StateCode ,State ,ZipCode ,Country ,RowIsCurrent ,RowStartDate ,RowEndDate) SELECT PayeeAK ,PayeeId ,PayeeSequence ,PayeeTypeCode ,PayeeType ,PayeeName ,PayeeNameConsolidated ,IsMinority ,StateCode ,State ,ZipCode ,Country ,RowIsCurrent ,RowStartDate ,RowEndDate FROM dbo.Staged_dbo_DimPayee__New "	This variable contains the string that will be used to insert any new rows into the DimPayee table.
User::RowCount	0		The number of rows that were loaded into the Dimension. This is will be compared against the row count from the source system.
User::SCD1UpdateDimPayee	/* Sample Update Type1 to Dim */ UPDATE [EnterpriseDW].[dbo].[DimPayee] SET PayeeAK = SCD1.PayeeAK ,PayeeId = SCD1.PayeeId ,PayeeSequence = SCD1.PayeeSequence ,PayeeTypeCode = SCD1.PayeeTypeCode ,PayeeType = SCD1.PayeeType ,PayeeName = SCD1.PayeeName ,PayeeNameConsolidated = SCD1.PayeeNameConsolidated ,IsMinority = SCD1.IsMinority ,StateCode = SCD1.StateCode ,State = SCD1.State ,ZipCode = SCD1.ZipCode ,Country = SCD1.Country FROM [EnterpriseDW].[dbo].[DimPayee] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimPayee__Type1Update] AS SCD1 ON Dim.PayeeAK = SCD1.PayeeAK	" /* Sample Update Type1 to Dim */ UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimPayee] SET PayeeAK = SCD1.PayeeAK ,PayeeId = SCD1.PayeeId ,PayeeSequence = SCD1.PayeeSequence ,PayeeTypeCode = SCD1.PayeeTypeCode ,PayeeType = SCD1.PayeeType ,PayeeName = SCD1.PayeeName ,PayeeNameConsolidated = SCD1.PayeeNameConsolidated ,IsMinority = SCD1.IsMinority ,StateCode = SCD1.StateCode ,State = SCD1.State ,ZipCode = SCD1.ZipCode ,Country = SCD1.Country FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimPayee] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimPayee__Type1Update] AS SCD1 ON Dim.PayeeAK = SCD1.PayeeAK "	The SQL string that will update any changed rows in the DimPayee dimension.
User::SingleEmailAttachment	c:\stubfile.txt		Temporary file name that will be used to create the e-mail notifications. This is a configuration setting from SSIS_Configurations.
User::SMTPPort	25		The SMTPPort for the e-mail server to be used when sending notification e-mails. This is a configuration setting from SSIS_Configurations.
User::SMTPServerName	www.ad.hetgroup.org		The SMTP Server Name contains the name for the e-mail server. This is a configuration setting from SSIS_Configurations.
User::TotalRowSource	0		This is the Row count from the Source System to compare against that of the DimCurriculum table to ensure that all records were copied.

Property	Value
Name	DimPayeeLoad
Description	This package will load any change to the DimPayee dimension. The first step in this package is to truncate all the working tables needed to track new and updated records. The next step will load the data into a staged table in DWOperations. The data will be checked if there are any duplicate records and these will be flagged. The data in the Staged Table will be compared against that in the dimension and will be inserted or updated as required. If any duplicate records were found, a notification e-mail will be send to the appropriate staff
Checkpoint File Name
Checkpoint Usage	Never
Check Signature On Load	True
Creation Date	Tuesday, May 08, 2012 4:11 PM
Creator Computer Name	ipeterson
Creator Name	ipeterso\hetgroup
Debug Mode	False
Delay Validation	False
Disable	False
Disable Event Handlers	False
Enable Configurations	True
Encrypt Checkpoints	False
Fail Package On Failure	False
Fail Parent On Failure	False
Forced Execution Value	0
Force Execution Value	None
Description	False
ID	e2618259-96b3-47b6-a4db-b605b4d9755a
Interactive Mode	False
Is Default Locale ID	False
Isolation Level	Serializable
Locale ID	1033
Logging Mode	Enabled
Max Concurrent Executables	-1
Maximum Error Count	1
Offline Mode	False
Package Type	DTSDesigner100
Protection Level	EncryptSensitiveWithUserKey
Save Checkpoints	False
Suppress Configuration Warnings	False
Suspend Required	False
Transaction Option	Supported
Update Objects	False
Version Build	367
Version Comments
Version Guid	aebe1fea-0520-41d0-a198-b897f339e130
Version Major	1
Version Minor	0

Name	Type	Description
DFT Load Temp Payee Table for Verification for Customers	Data Flow Task	Data Flow Task
DFT Load Temp Payee Table for Verification for Employees	Data Flow Task	Data Flow Task
DFT Load Temp Payee Table for Verification for Non-Employees	Data Flow Task	Data Flow Task
DFT Load Temp Payee Table for Verification for Students	Data Flow Task	Data Flow Task
DFT Load Temp Payee Table for Verification for Vendors	Data Flow Task	Data Flow Task
DFT Load Temp Payee Tables from Staged Table	Data Flow Task	Data Flow Task
SEQ Alert System	Sequence	Sequence Container
SEQ Remove Obsolete Records From DimPayee	Sequence	Sequence Container
SQL Detect and Flag Duplicates	Task	Execute SQL Task
SQL Empty Temp Payee Tables	Task	Execute SQL Task
SQL Load New Temp Table to DimPayee	Task	Execute SQL Task
SQL Load SCD1 Temp Table to DimPayee	Task	Execute SQL Task