Thanks Luis,

That is a partial solution but missed the point of needing to match the positional elements of each split as it will produce a cartesian join. If I...

Thanks Phil. first one to reply again!

The problem is that it is the generated dtsx that would get deployed and I need to be able to prove that what...

Hi Chad, welcome to the boards.

The short answer is not much. If you can't trust your data source then the safest thing to do is to initially load it...

@Gila,

My point exactly - As your end user finds new rules that need to be applied, it is (IMHO) easier to do that with another inline filter and merge/join tasks...

sounds to me like you need a recursive Common Table Expression

Or look at the PIVOT() function

My 2 cents worth

Firstly, as has already been pointed out this is going to be a nightmare to get any sort of accuracy - can you go back to the...

I would be looking to further separate the entities:

Work done (agent, Project, activity type) is separate from the work value (agent, activity type, rate, rate_start, rate_end)

If the rate for an...

@OP

so if you don't know what dates records were created/last updated, how would you choose records to be archived?

@JM

If someone changes the table structure, you are ~£%$!"$* 🙂 but...

+1 for RedGate.

They also have a data compare so that you can script config and static data

Their tools also integrate very nicely into SSMS and SQL compare can handle scripting...

Do you have a field in each table that you can confidently rely upon to provide a monthly cut off, Or a field which provides a FK reference to the...

yes, but if you inherited a cupboard with a plywood back, you wouldn't replace it with a solid wood one, especially not if the front was made from MDF.

Sometimes you...

Thanks Gianluca, I'll try that now. But you are right, it does seem like a tedious workaround.

Preaching to the converted mate!

Currently have 40+ customers all with modified databases that do not match the release standard and the test database may or may not match the production;

Retro...

I have only scanned the thread so apologies if this has already been pointed out.

As stated, your PK should probably be auto-increment integer; however presenting an alternate Alpha Numeric key...