Welcome to my world.

I've been doing ETL development with SSIS essentially for the last 8 years. What you're encountering are issues I've had to deal with on a daily basis.

Usually...

I do have one complaint:

By using ORDER BY OrderID, I'm making the assumption that the primary key (which is implied to be an IDENTITY column) ascends sequentially with OrderDate.

This could...

If you don't actually need results from the QUARTER table, an EXISTS subquery can actually be a little bit faster.

WITH CTE AS (
    SELECT...

If you wanted the third column header to read as "A = B", then you have to type it as

[A = B]

If you want the third column...

To clarify why you got an error:

After the CTE definition, you had a DECLARE variable statement. You can only use a CTE within the context of DML language (SELECT, INSERT,...

Here's what I would do:

SET DATEFIRST = {whatever day of the week your week starts};

SELECT
CurrentDayOfCurrentWeek = (datepart(dd, getdate()) - 1) % 7,
WeekStartDate = dateadd(dd, -(datepart(dw, getdate() -...

Because you don't have any sort of join relationship between SHIPMENT and any other table (either in the FROM clause or the sub-select subquery), the query is performing two cross...

What version of SSIS are you working with?

Have you checked the security levels of the other packages, or the project?

If the project is also EncryptSensitiveWithPassword, it may be that the...

In your OPENXML query, you need to drill all the way down to the Symbol node of your node tree. So it should look like this:

I think why it is failing on the set-based vs the RBAR approach is because of where the subquery exists in the statement. In order to evaluate the subquery, it...

I don't know how they do things in the UK, but to me it looks like you have a lot of practical experience that can impress would-be employers. If your...

Are you able to create a staging table?

What I would do is have your data flows populate the staging table. Your staging table should have a LineText column for the...

One thing when using the LEAD or LAG functions, in your OVER clause, you may also want to add a "PARTITION BY vidID, textAuditID" statement before ORDER BY. So the...