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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

612 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