Solved

Using a DTS package to do an update

Posted on 2004-09-29
8
251 Views
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.
0
Comment
Question by:jcritchlow
8 Comments
 
LVL 142

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.
CHeers
0
 
LVL 8

Expert Comment

by:MartinCMS
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
where........
0
 

Author Comment

by:jcritchlow
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.

0
 

Author Comment

by:jcritchlow
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.

0
 
LVL 5

Accepted Solution

by:
MichaelSFuller earned 500 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
            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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ms sql stored procedure 22 77
MS SQL 2014 get SPIDs of users 6 26
t-sql complement 8 32
Increasing Identity length in sql server 4 20
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now