Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 988
  • Last Modified:

SQL Update via SSIS?

Hello, looking for some direction on the best way to run an UPDATE query on a SQL Server DB based on a SELECT query of another DB?  My goal is to build an SSIS package to schedule to run this procedure daily.

  sSQL = "SELECT libraries.*, genes.name FROM libraries LEFT JOIN genes ON libraries.gene_id = genes.id ORDER BY position;"

I need to use the results of this query to UPDATE another DB table called Matrix.pipeline.

I am guessing I could do something like this:
INSERT INTO tabelename (the table you want to copy into)
SELECT * from tablename (the table you want to copy from)

My issue is that the the SELECT is from a DB on another server (PostgreSQL) via ODBC conn.
The UPDATE is on my SQL2005 server.

TIA,
Andrew
SS
0
Ahelbling
Asked:
Ahelbling
  • 3
  • 3
2 Solutions
 
venk_rCommented:
0
 
AhelblingAuthor Commented:
Ok, I read through that and it seems like there should be a simpler way, no?

Can I not just run 1 query to SELECT from the postgreSQL DB using the connection I have established within SSIS (tested and working) and INSERT INTO my SQL2005 DB?  Table structures are identical on both ends.
0
 
AhelblingAuthor Commented:
SELECT * INTO MyWorkingTable FROM OPENQUERY(LINKEDTABLE, 'SELECT * FROM tablename')
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
venk_rCommented:
Try the below option .Create a linked server
INSERT
OPENQUERY(<linked server>,'select log_text,log_date from test)
SELECT <value>,getdate() from test1
--------------------------------------------------------------------------------
0
 
AhelblingAuthor Commented:
Thanks I will give it a try once I am able to successfully add the linked server in SSMS 2005.  I keep getting this error even though I turned off fiber mode...
Error
0
 
venk_rCommented:
Seems like you have Lightweight Pooiling enabled .
If you're getting this error message, go to SSMS and uncheck "Lightweight pooling", and find some (additional) downtime to restart the SQL Server service.  The error message will go away.  

Please read the below article before doing this.
http://technet.microsoft.com/en-us/library/bb402857.aspx
0
 
nishant joshiTechnology Development ConsultantCommented:
The best way to solve your requirement is

1)create table using execute sql task
2)choose data flow task
    1.choose source table from different server
    2.select destination fror source in dataflow....

Thanks,
Nishant
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now