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, December 10, 2014 4:38 AM
Points: 120, Visits: 491
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 @ 11:18 AM
Points: 5,316, Visits: 12,346
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.

It is better to keep your mouth shut and appear stupid than to open it and remove all doubt. (Mark Twain)
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, December 10, 2014 4:38 AM
Points: 120, Visits: 491
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, December 10, 2014 4:38 AM
Points: 120, Visits: 491
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