Merge Statement using Execute SQL Task - A deployment issue

  • 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
  • Have a MERGE proc and call that instead?

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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
  • 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,

    Paul Hernández

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply