| Author |
Topic |
|
jforward5
Starting Member
19 Posts |
 Posted - 2009-07-21 : 14:03:01
|
| I am making a SSIS Package using the Business Intelligence Studio and I am having difficulty getting it, not only to complete the job, but also to find all the new rows from my source. I have read some articles about doing lookups for new or changed rows and used the examples shown to direct my steps but I can not seem to get it to work properly.My source data is external and has to be accessed using ODBC, which I did manage to link to SQL as a linked server. I have tried making the data source object with the ODBC and with the linked server, but neither has a better end result. I am not sure where I am making the mistakes at. The external data resides in a progress database and has close to 2 million rows. The package runs what seems like infinitely and only finds one row that is new, and only finds 300,000 rows total. Maybe someone could show me some more detailed instructions on how to use the lookup against an external data source and how to make it run with the best performance.One thing I noticed is that in "Full Cache mode" the package never does the lookup from what I can tell, and it boggs the server down so much that nothing else can be done on the server until the package is stopped. In partial cache mode it never pulls all the rows, I let it run for 2 days and it only pulled 2,000 rows and never looked anything up. With cache mode off it gives me the 1 new row, which should be more like 10,000 new rows, and pulls 300,000 rows from the external source.I need help, I have been working on this for 3-4 weeks and have got to finish this project! |
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2009-07-22 : 09:25:04
|
| Questions:1) How fresh is the external data and how often do you match against it?2) Are you trying to extract/match data time specific? |
 |
|
|
jforward5
Starting Member
19 Posts |
Posted - 2009-07-22 : 09:41:19
|
| 1) New Rows are added everyday2) I am trying to exact match but once a day. Will ussually be at the end of the day, but right now I am building and testing it during the day... |
|
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2009-07-22 : 10:12:55
|
| Have you had success with performance using SQL itself? I'm leaning towards data dump to temp table and have SQL do the dirty work rather than SSIS lookup. Since it's not a constant/real time matching then I would give that approach a try. |
|
|
|
jforward5
Starting Member
19 Posts |
Posted - 2009-07-22 : 10:16:52
|
| Can you explain this process or give me a link to how to do this using your suggetion? |
|
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2009-07-22 : 10:23:17
|
| Sorry, what I mean is that import the data to a SQL server and dump the data into a temp or staging table so you can do the matching. Are asking how to import data using SSIS or SQL matching? |
|
|
|
jforward5
Starting Member
19 Posts |
Posted - 2009-07-22 : 10:45:41
|
| I see. This process might work. I will give it a try. Thank you. |
|
|
|
jforward5
Starting Member
19 Posts |
Posted - 2009-07-22 : 11:50:03
|
| The one road block with this method that I am now having is I need this whole process to be scheduled, so I am building it as a package. However when I make the Data Flow Task for matching the data I can't reference the temporary table since it has not been created yet... What do you suggest I do about this? |
|
|
|
rgombina
Constraint Violating Yak Guru
319 Posts |
Posted - 2009-07-22 : 12:54:39
|
| Okay - here's what I would do:1) select a server where you want the data to be temporarily staged (external and your lookup)2) create the temporary table for your external data3) import data from external data using OLE DB Source to temporary table (OLE DB Destination)4) create a Stored Proc to do the matching5) use SQL Task to fire up the Stored ProcSo the flow: Data Flow Task (import data) > SQL Task (run Stored Proc for matching)Run the package manually and once you have the mechanics working then you can schedule it as a job. |
|
|
|
|
SSIS Lookup Problems...