Post reply

Update Query Using Different Connection Managers

  • February 28, 2008 at 8:41 am

    #184380

    What was easy with DTS has me stumpped here. I need to run a simple Update query. The data comes from an SQL database, and the target is an Access database. There is a common field for the join. Data from the SQL database will be used to update the target field in the Access database.

    Every Data Flow Transformation object I've seen allows for only one Connection Manager to be selected.

    I don't need someone to write the code for me, just help me understand the architecture for this type of task.

    Has anyone tried this before?

    Eric

    [font="Verdana"]There are 10 types of people in the world. Those who ready binary and those who don't.[/font]

  • February 28, 2008 at 9:43 am

    #783329

    Eric,

    I think this should work.

    First, define both the Access and SQL DBs in connection manage. Then drag a SQL task. You can use query buider or just write straight SQL to pull the data. I find it easier in these kind of cases to use query bilder.

    Let me know if you have any questions.

    ------------
    🙂

  • February 28, 2008 at 9:58 am

    #783337

    Hi Rav,

    Thanks for the help, but I've already tried that. After dropping an Execute SQL Task to the Control Flow canvas, I'm stuck. The SQL Task can have only one connection associated with it. I need to update DB2 based upon values in DB1. I'm joining two tables on a single field, but those tables are in different databases. Like I said before - easy in DTS, but baffling in SSIS.

    Thanks again,

    Eric

    [font="Verdana"]There are 10 types of people in the world. Those who ready binary and those who don't.[/font]

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

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