Using a DTS package to do an update

Posted on 2004-09-29
Medium Priority
Last Modified: 2013-11-30
I am trying to use a DTS package to pull data from a SQL Database and update it to a PostgreSQL database. I am able to set up a simple package that will copy a row between the two. (Append) But I would like to have the data that I am pulling from SQL to UPDATE a table in PostgreSQL on some matching Criteria. I have tried a SQL task along with two connections I have tried ActiveX script, and data driven querry. Can some one please tell me how to accomplish this. I have it working just fine when the tables are from the same database, but having problems from diffrent databases. I am using a ODBC Connection for both databases.

Thanks in advance.
Question by:jcritchlow
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 12179036
If you use a Transfer Task, you will always only be able to append rows.
If you want to update rows, you will need a SQL Step, and have a linked server to the postgreSQL database in your situations, and update using OpenQuery() function.
Please refer to the Books Online to check out how this works, but of course you can ask for details here.

Expert Comment

ID: 12182318
Why not running an join Update statement instead of DTS and Update?

Update serverName.DatabaseName.dbo.PostgreSQL
Set Col1 = 'updatefield'
From yourTable yt join serverName.DatabaseName.dbo.PostgreSQL p
     ON yt.id = p.id

Author Comment

ID: 12192020
sp_addlinkedserver 'pragmeta', 'any','MSDASQL',supp_web,'','',''
sp_addlinkedsrvlogin 'pragmeta','false'
update openquery(pragmeta, 'select * from j_order where zip = 12346') set zip = 12345

I have got this far using a querry in querry analyser, But I need to set zip = to a valuse in my SQL database using a where clause. Not sure how to go about it.


Author Comment

ID: 12201144
Can some one please help me, I am really struggling. I have two databases

Database 1 is a SQL database
Database 2 is a Postgres Database

I have a view written in the SQL Database that has the information in it I need. It consists of a SOPNumber, ID, and a tracking number

I need my view from the SQL database to join to the postgres table on the ID field, when it matches I need the tracking number field in the SQL database to update the tracking number field in the Postgres Database.

I have tried using a DTS package with a look up but it will only append the row to the postgres database.

I have tried a linked server, with a open query which I have had some success with. I am able to update the row if I Hard code what I am setting the field to.

I need to be able to set the field in postgres to the field in SQL view based on a join on the ID field. I have the example above of the linked server method. It does work the way it is, but as you can see I am hardcoding the zip.

Please, Please some one help before I go crazy.

Thanks in advance.


Accepted Solution

MichaelSFuller earned 2000 total points
ID: 12206412
You mentioned a DTS Package couldn't you just write a script to do your task as a step in the package? Something like this?

'  Visual Basic ActiveX Script

Function Main()

'these are the constants and must be included
        '---- CursorTypeEnum Values ----
        Const adOpenForwardOnly = 0
        Const adOpenKeyset = 1
        Const adOpenDynamic = 2
        Const adOpenStatic = 3
        '---- LockTypeEnum Values ----
        Const adLockReadOnly = 0
        Const adLockOptimistic = 1
        Const adLockPessimistic = 2
        Const adLockBatchOptimistic = 3
        '---- CommandTypeEnum Values ----
        Const adCmdUnknown = &H8
        Const adCmdText = &H1
        Const adCmdTable = &H2
        Const adCmdStoredProc = &H4

        'Declare variables
        Dim conSQLServer
        Dim cmdPostgreSQL
        Dim recSQLData
        'create connection and recordset objects
        Set conSQLServer = CreateObject("ADODB.Connection")
        Set cmdPostgreSQL = CreateObject("ADODB.Command")
        Set recSQLData = CreateObject("ADODB.Recordset")

        conSQL.Mode = adModeRead
        conSQL.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=<databasename>;Data Source=<servername>"

        'Grab our SQL data
        strSQL = "Select <row1>, <row2> from SQL Table"
         'Read only
        recSQLData .Open strSQL, conTest, adOpenStatic , adLockReadOnly
        If Not recSQLData.BOF Then
              recSQLData .movefirst
        End If

        Do while Not recSQLData.EOF
            'Put together my sql statement    
            strSQL="Update table set x =" & recSQLData .fields("TGFLAG").Value & "where z=" & recSQLData .fields("condition").Value
            'open connection
            cmdPostgreSQL.Open "DSN=dsn"
            'Execute command
       'close the open recordset
       set recSQLData = Nothing
       'close the current connection
       set conSQLServer = Nothing
       set cmdPostgreSQL = Nothing
       'return success to goto next step
       Main = DTSTaskExecResult_Success
End Function

The other way you could do it is to setup replication between the two servers if you have the bandwidth.


Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question