I'm facing the same problem across different projects and I’m not able to find an appropriate solution:
When I want to perform a MERGE with Source and Target tables in the same server but in different databases, I simply use the fully qualify name of the table (or whatever its name is). I need to use it because one and only one connection can be set in an Execute SQL Task. So, everything is wonderful, until I have to deliver the package, and the admins deploy it in another server.Example Situation:
Supposed that I create an OLEDB Connection to the Source Database, let´s say dev_db_A
and use a package configuration to hold the connection string. In the Merge command I do something like this:
MERGE dev_db_B.target_table as TARGET
There´s no problem with the source_table
, since the connection is parameterized and could it be changed in the configuration file/table. But suppose that the target table database is named test_db_B
. The package failed! The database “dev_db_B
” could not be found.Workaround 1:
Teach the admins how to open the package in the BIDS and edit this value.Workaround 2:
Use a variable to hold the query and parameterize the SQL Server Objects Names, like the database names or table names. Conclusion:
I don`t like both solutions, the first one does not avoid to hardcode the name of the database, the second one is better for me but large queries rely on a String variable (or more if they are too long) and are not easy to read and maintain.
After this long story (hope you´re not sleeping yet), what else can I do?
Any comment would be appreciated.