Thanks, Phil.

I again looked at the value of the variables and I noticed that the value for vDateThreshold was set incorrectly due to some ordering of the tasks. I just...

<!--more-->I set a breakpoint at the postExec event of the exec sql task and the variable seemed to be set correctly.

I'll triple check that though.

That variable uses the string data type.

I know the variable is being set as expected by the EXEC SQL task but my other string variable called vSQLString uses an expression...

No the number is inconsistent but is always less.

The proc works as expected when used outside ssis. When I call the proc in my package I'm capturing the row count and storing that in a table so I...

The main problem I see is the back dated changes that I've seen which can go back a couple of years.

We'd need to know about those so we'd...

Thank you for your response.
I'm not sure that would work for us but I'll take some time to digest it and possibly come back with some questions.

Yeah I'm aware of those things as well. Tuning this system is going to be a long and painful process.

One thing I am surprised about is how bad the plan...

Thanks for you reply.

I discovered someone had added an index on the risk__tag table which was causing the strange plan in production. I agree the code isn't great and I'm...

I think I may have discovered the problem.

Someone seems to have created an additional index that looks to have been throwing the query out.

Thanks for the suggestions.

I've already isolated a few of the UPDATE statements that are performing badly and made some changes which has had an impact. I think I'm going to...

Hi Kevin,

I'm not new to being a DBA, just new to my current role and have been given this process to look at.

The INSERT statements are the biggest offenders as...

I've seen a similar error with other types of connections where the wrong runtime is being used. Maybe you could check that?

Otherwise confirm you've correctly mapped the environment variables to...

In terms of best practice I like to parameterize variables that I know tie my package or project to a particular environment. Things like connection strings, file paths and user...

What problem are you experiencing?

Are you saying you're getting a login failed error?