When you run xp_cmdshell, it runs in the context of the MSSQL service account (or a proxy if you have it configured), not the login you have used to connect...

I guess I don't consider these things that make replication more complicated, they are just things you have to do to configure and manage replication.

Creating some kind of custom identity...

I would first suspect data type or collation. The comparison being done is going to be very type-sensitive and absolutely case and accent sensitive. Make sure you do...

I agree with much of that, but this is a case of consolidating business units, not data from several companies (according to the original post). So, the security concerns...

I have never had any issues with identity columns and replication, and I have used replication extensively.

People seem to get hung up on identity columns having unused "gaps" in the...

What you are sescribing is building your own service broker.

When you send a message to a broker, you can have a procedure configured on the broker that will essentially process...

Assuming you are using Active Directory and Server 2003 for your domain controllers, it is already turned on. You should just have to configure your SPN's.

Use the setspn command...

Ok, in your example for the column compare in SSIS vs. T-SQL, your comparisons are a bit different. The T-SQL comparison will be based on your collation settings and...

How are you running the package, using xp_CmdShell?

You need to configure Kerberos authentication. NTLM authentication will only let you pass an authentication token across two logins. So, your token on your local machine is passed...

What type of data source (Connection Manager) are you using? All of the connection managers allow you to set their properties with expressions, but unless you need to dynamically...

That's looking somewhat reasonable. The sub-queries and OR's in it may give you some performance issues, but this is looking along the lines I would start. It will...

If this is a total re-design, why wouldn't you just create a single database that included composite keys in the tables - one of the key fields indicating the business...

You can turn it off (or completely uninstall it), but the reports within Management Studio use it, so you may lose some useful features.

Just sitting there, it does not use...

This is a pretty common issue. Unfortunately, SQL Server does not have data-level permissions like this. There are lots of ways to implement it and you should read...