If the values are, as you suggest:

Employed (String) - "Manager"

Non-Employed (String) - "Fired"

Retired (String) - "Enjoying Life"

what would you set Employed Status to?

Looks to me as though you can simplify your WHERE clause to

WHERE j.Code <> 'L3'

and do the whole thing as a single INSERT.

What are the possible values in the three source columns and how do they map to the target column?

It's a subquery within a subquery (a subsubquery??) – it's much easier to read with some formatting applied:

BEGIN
    SELECT COUNT(*)
    FROM...

Since I'm using SSIS to map the data from the flat file to my staging table I prefer to get all the data in the staging table, then try...

• This reply was modified 6 years, 11 months ago by Phil Parkin. Reason: Fix typo

Do you receive any error messages?

Use something like this to get your start and end dates:

DECLARE @StartDate DATE = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0);
DECLARE @DayNo TINYINT = DAY(GETDATE());

IF @DayNo > 15
 ...

• This reply was modified 6 years, 11 months ago by Phil Parkin. Reason: Fix error in query

Is that a statement or a question?

Here is a link to a code template that should push you in a better direction, regarding structure.

That's not really the way to do it.

You can remove all of those BEGINs and ENDs (except those pertaining to TRY and CATCH).

In your CATCH block, you should probably use...

IMHO, you are on the right path.  I'm currently doing exactly what you described.  Importing flat, delimited files to a 'staging' table where all fields are defined as VARCHAR(500),...

Use DATEFROMPARTS(Year, Month, Day).

Please provide some additional background information:

1. Why did you decide not to use SSISDB environments to handle those parameters which vary between Dev/QA/Production? They make config files redundant.
2. Why have...

So restoring the ssisb did not create the master key.

No, you have to do a backup/restore of the master key as a separate task.

Is the jobs run using sql agent...

I have not heard anything about being able to do this ... but then again, I've never lived anywhere where the standard Gregorian (not English - more like Italian!) calendar...

• This reply was modified 6 years, 11 months ago by Phil Parkin. Reason: Improve narrative