Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Surkum
Starting Member
2 Posts |
 Posted - 2009-01-23 : 13:35:58
|
| Hi Everyone,I am a newbie to SSIS and trying to sharpen my skills in this technology.We use SQL Server 2005 and we have designed a process using SSIS to pull data from DB2 files in the AS/400. The process is designed to compare the changed records in the DB2 files and store the results in a SQL database. We have about 14 DB2 files from which we pull data. Some of these files contain over a million records. We have 14 individual packages for each of the 14 files to pull the data from the AS/400. In one of the packages, the DB2 pull had 7 million records and the package execution is taking too long,sometimes running into days. My question to the forum is how do we improve the efficiency of the process?Any help is greatky appreciated.ThanksSurkum |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2009-01-23 : 14:32:41
|
| My first question would be how are you doing the comparison and what is your indicator that you have something that has changed. For example is there a simple date field comparison that you are leveraging or do you have to hit each field of each row and compare it to what you pulled in on your last import?Mike"oh, that monkey is going to pay" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-24 : 10:56:13
|
| seems like what you need to use is SCD wizard which looks for delta changes based on key value you provide. Anyways, more details on your package structure will help us to provide more accurate suggestions |
|
|
|
Surkum
Starting Member
2 Posts |
Posted - 2009-01-26 : 09:22:22
|
| Thanks to both for your reply. We have a stored procedure which compares the relative record number of the old record and the new record and the old sequence number and the new sequence number minus 1. The stored procedure takes in the page size as a parameter.If you need I can post the SP codeThanksSurkum |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-26 : 09:27:04
|
| if you're using scd wizard you dont need procedure and also relative record number. just give key of you table and it will compare based on key value to get delta changes. |
|
|
|
|
|
|
Package Execution Taking too Long to complete