Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Merge Statement using Execute SQL Task - A deployment issue


Merge Statement using Execute SQL Task - A deployment issue

Author
Message
Paul Hernández
Paul Hernández
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 661
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,

Paul Hernández
Phil Parkin
Phil Parkin
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11002 Visits: 19865
Have a MERGE proc and call that instead?


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Paul Hernández
Paul Hernández
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 661
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,

Paul Hernández
Paul Hernández
Paul Hernández
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 661
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 and as far as I'm concerned this thread is closed.

Kind Regards,

Paul Hernández
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search