Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Merge Statement using Execute SQL Task - A deployment issue Expand / Collapse
Author
Message
Posted Friday, April 12, 2013 8:20 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 5:14 AM
Points: 119, Visits: 486
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
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1441730
Posted Friday, April 12, 2013 8:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:33 AM
Points: 5,166, Visits: 12,015
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.

When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
Post #1441733
Posted Monday, April 15, 2013 2:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 5:14 AM
Points: 119, Visits: 486
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
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1442194
Posted Wednesday, April 17, 2013 3:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 5:14 AM
Points: 119, Visits: 486
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
http://hernandezpaul.wordpress.com/
https://twitter.com/paul_eng
Post #1443169
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse