Solved

SSIS Lookup Two Different Databases to Build

Posted on 2010-11-19
3
483 Views
Last Modified: 2012-05-10
I am building a data warehouse with sales in my SQL Server 2008 database.  I am feeding it with information from an Oracle 9 ERP system.  I am using SSIS to do the ETL.

I would like the best strategy to approach a particular ETL problem I have to build Daily Sales.
The SQL Server destination Sales table is called tblSales.  Every day I must add in the new Sales that have been generated for the previous day from the Oracle system.  It is possible, however, for a Sale to 'drop in' from weeks ago which has been in some form of query state with the Finance department.  There are no date flags to say when this occurs otherwise I could just look to see which recent Sales had dropped in and this would include the recent Sales.  I wouldn't really have a problem as I would just fire one query over to get all of those Sales.  
Long story short, I have a table of the unique TransactionNo / StoreNo combinations in both SQL Server and Oracle and created the table tblDifferentialSales.  This has all of the Sales that need to be transferred across.

My problem is that the method I am using in SSIS, although it works, is very slow (about 1 second per Transaction – there could be 20,000 transactions per day to import).  

Current Method:

1. Execute SQL Task to
select StoreNo, TransactionNo from tblDifferentialSales
this is outputs to an object Variable

2. For Loop Container, loops the object Variable in a Data Flow Task and creates the specific SQL to draw back one Sale Transaction in a Text Variable, txtSQL, which is then assigned to the [ADO NET Source].[SQLCommand].  

3. This is then output to the tblSales OLE DB destination.

I wondered if it is possible to use something like the ‘Merge Join’ Transformation to get the Oracle dataset to read from the SQL server dataset as its criteria.  I have tried to make this work but I am using both ADO and an OLE connections for different databases (mainly because the SQLCommand property seems to be more easily exposed than for an OLE connection).  I can find examples for Merge Joins but not with two different databases.  Bear in mind that the SQL Server connection may have 20,000 records in it but the Oracle ERP connection may have millions to join with.
I am open to any possibilities.  What is the best way to go about this, please?  Has anyone done something like this before?

0
Comment
Question by:DATABAS3
  • 2
3 Comments
 
LVL 7

Expert Comment

by:rmm2001
ID: 34177500
I'd stay away from doing the merge join. If you're in ssis 2008, cache the ids you have in sql server using a cache transformation (I'd put it to a file). Then make a data flow task. The source of the dataflow task will be the data from your oracle table. Then add a lookup transformation with the connection being your cache connection manager you just made from the sql ids. Map the columns accordingly. Send the "no match rows" to a multicast. On one side insert the data from the oracle table into your sql table. On the other side, insert the new ids into your sql id table and you should be updated and good to go!
0
 

Accepted Solution

by:
DATABAS3 earned 0 total points
ID: 34217655
I have done this but it doesn't seem to give me any greater speed.  It may be that I had the right idea originally (which when building from oracle to oracle in a cursor flies in a couple of minutes).

It may be the connection that is letting me down, rather than the loop.  The loop may be slowed by an overhead on the insert in Oracle (?).

Thanks for your idea - it was useful to learn a new way to do it but overall, the time still takes to long.  I am having to build in Oracle for speed and than import to SQL Server.  This is not ideal as it introduces another failover point.
0
 

Author Closing Comment

by:DATABAS3
ID: 36935095
I didn't really get enough options to look at this in any great detail.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem with SqlConnection 4 160
select over clause 1 40
Filtered index 5 56
Query Peformance + mulitple query plans 9 48
Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

920 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

12 Experts available now in Live!

Get 1:1 Help Now