Solved

SSIS Package with ODBC Source and OLE DB Source

Posted on 2014-02-20
3
716 Views
Last Modified: 2016-02-10
I'm trying to do what I think is simple...
Creating a package to pull old FoxPro data and put it into a SQL table.  The insert however should only pull in "new" records. The FoxPro table has a unique key (testid).  So I thought I would have the ODBC Source and create a second source (OLE DB) which was just a query to grab the max() testid from the SQL table.  But I'm having trouble trying to figure out how to get these two seperate sources to talk to put into a single destination.  I though 'union all' would work but no matter what I do I cannot get the types (yes I created a 0 as maxtestid in the ODBC query from FoxPro) to be compatible.

Any ideas?
0
Comment
Question by:ClowWater
  • 2
3 Comments
 
LVL 34

Expert Comment

by:Brian Crowe
ID: 39874093
Use an OUTER JOIN on the two sources and only pass through records where the SQLTable.uniquekey IS NULL.
0
 

Accepted Solution

by:
ClowWater earned 0 total points
ID: 39879637
BriCrowe...I tried but couldn't get it to work.

What I did to get it to work...I used a T-SQL Statment task and put max value into a user variable (thus deleting the OLEDB source).  Then used the conditional split on the ODBC source (inside data flow task) to filter out my results that I then sent to the OLEDB destination.
0
 

Author Closing Comment

by:ClowWater
ID: 39891401
worked
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

My client has a dictionary table. They're defining a list of standard naming convention. Now, they are requiring my team to provide us a mechanism how to match new incoming data with existing data in their system.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

910 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