Solved

SSIS Integration Services, pull from different SQL Server, insert/update nightly on custom table

Posted on 2012-03-22
4
215 Views
Last Modified: 2013-03-22
I am new to integration services, and would like to setup a nightly task that references a view on a different SQL server on my network. I want to insert/update information into a custom student table that would store account numbers from another SQL box. I am confused on the control flow and data flow parts of this task.

I created a control flow of: Data Flow Task and then went into the properties of it. From the data properties, I created a OLE DB Source and another OLE DB Source, but what am confused on is my inner join. I am trying to map to the Copos Server, but since it is on a different server, I am getting errors on adding the linked server when I try and click mappings. Do I have to add a linked Server when the Source server is under the Connection Manager?

Should I get going about this another way? Should I be using the Data Flow Transformations of Merge or a Merge Join?
Screen-Shot-2012-03-22-at-11.54..png
0
Comment
Question by:Emi975
[X]
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
  • 3
4 Comments
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 37753368
so forgetting about SSIS for a minute - Are you saying you need to a SELECT from a table on Server1 and then INNER to a second table on Server2?
0
 

Author Comment

by:Emi975
ID: 37753397
I want to read a view from Server 1 and then do a inner join on the update statement for Server 2. The view does not have the same personid to match the personid on Server2. Thats why I have to do a inner join.
0
 
LVL 17

Expert Comment

by:Barry Cunney
ID: 37753477
So you need to do something in SSIS that mimics the below SQL pseudo code?

UPDATE std_1
SET std_1.AccountNumber = std_2.AccountNumber
FROM Server1.Student std_1
JOIN Server2.Student std_2
ON std_2.StudentID = std_1.StudentID
0
 
LVL 17

Accepted Solution

by:
Barry Cunney earned 500 total points
ID: 37753580
You may be able to user a Merge Join as outlined in this article
http://www.mssqltips.com/sqlservertip/1322/merge-multiple-data-sources-with-sql-server-integration-services/

Change the sources to be OLEDB sources as opposed to Flat File sources
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

691 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