April 12, 2013 at 8:20 am
Hi friends,
Background: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
USING(
SELECT
..........
From source_table
..........
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.
Kind Regards,
April 12, 2013 at 8:24 am
Have a MERGE proc and call that instead?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 15, 2013 at 2:00 am
Hi Phil, thanks for your answer.
I stilll have one question:
-Can I avoid the use of Dynamic SQL using a stored procedure?
I have read about "Table-Valued Parameters" and stored procedures and I think I'm close to finish this puzzle but I can't see how can I parameterized the table name.
Kind Regards,
April 17, 2013 at 3:59 am
Hi all,
I have found that it is not possible to avoid the generation of dynamic SQL code if you want to parameterized a SQL Server object name (sysname). However, I found this great article: Using the MERGE Statement in SSIS Via a Stored Procedure[/url] and as far as I'm concerned this thread is closed.
Kind Regards,
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply