my advise is for your company to hire a good SQL Consultant with both code and Data analysis experience to go through your issues and find out what happened and...

you going to replace a PROD DB with a non prod one? how is it that your QA db is correct and your prod is bad? or is it just...

it will have issues with connecting to prior SSIS instances.

for plain SQL database/dba access it should be fine with 2008 - maybe not 2005.

latest versions also have the "debug" option...

what is the database compatibility level?

Assigning a value to a variable as part of the declaration is only possible if the database is a certain level - don't remember which...

Sometimes, that's difficult because a lot of technical manuals aren't written...

yes - its called "read the manual"

might be good to post a link to the tool you are using

@Ed B - in order to truncate a partitioned table , the table and indexes must be aligned (partitioned on the same partition function).- see https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?view=sql-server-ver15

@ScottPletcher - while for a...

how is the packaging being executed? and are those parameters to the SP also parameters to the execution of the package?

and how will the OP create a non clustered PK...

for SQL 2008 R2 SSMS was installed as part of the SQL Server installer - you can get it from there.

to avoid installing it on your pc I would check...

as it would be a unique index I don't believe there would be negative impact on that table (at least not significant).

for the trigger - yes the idea is that...

instead of a Primary Key you can define it as a Unique Index.

one of your issues is how you ensure that GSN_ID remains unique throughout the table. - a update/insert...

see this https://stackoverflow.com/questions/761121/performance-issue-comparing-to-string-format for a small compare rewrite - very specific but it kind of addresses performance.

and then https://hashnode.robinrottier.com/net-6-string-format-performance and https://devblogs.microsoft.com/dotnet/string-interpolation-in-c-10-and-net-6/

main issue with "format" is that it has soo much...

another question is why are you converting to NVARCHAR - is your software now going to be used by users that require data that does not fit  on a VARCHAR?...