Using a DTS package to do an update

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.
jcritchlowAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
MichaelSFullerConnect With a Mentor Commented:
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
            cmdPostgreSQL.Execute(SQLQuery)
       
            recSQLData.movenext      
       
        Loop        
               
                     
       'close the open recordset
       recSQLData.Close
       set recSQLData = Nothing
                 
       'close the current connection
       conSQLServer.Close
       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.

Late,
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
CHeers
0
 
MartinCMSCommented:
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
where........
0
 
jcritchlowAuthor Commented:
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.

0
 
jcritchlowAuthor Commented:
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.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.