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 |
|
Rodgerga
Starting Member
3 Posts |
 Posted - 2009-04-06 : 14:30:23
|
| I have the following requirement.1a. Lookup the a value in Table A.colume1(Destination). 1b. If no value exists for Table A.colume1(Desti), by rule this must be the first time the process has run, and therefore must pick up claims beginning on January 1, 2000. This is done by looking up the MIN value in Tableb.column2(Source) where the run_start_date_time > January 1, 2000. 1c. If a value does exist for Table A.colume1(Desti), look up the MIN value in Tableb.column2 that is greater than the value in Table A.colume1(Source). This should be one digit greater, but that is not guaranteed so the lookup must be done.1d. If there is no value in Tableb.column2(Source) that is greater than TableA.column1(Desti), then the process is complete and can stop.1e.Insert records into Table c(Desti) is an additive table, meaning data is never updated in place, new rows are just laid down for each time payment is run. Any help on this is really appreciated.I have just started working with SSIS. |
|
|
toddbkc
Starting Member
9 Posts |
Posted - 2009-04-07 : 10:45:12
|
| I'd write this as a stored procedure, or as a series of queries. For example, if you captured the max (date) from source, and then compared it to a value from the destination, then you could determine whether the process needs to run.It sounds like you are checking to see what records need to be brought from the source. Sometimes, you can get away with "automatically" pulling all records for a certain time period (say up to 2 weeks ago) into a staging table, and then filtering the insert to only bring into the Destination records that are new. In this way, you would have the source and destination data side by side.Sometimes folks get into thinking that they have to do everything the SSIS way, but that is not always the simplest way. Hope this helps!TODD |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-04-07 : 11:36:13
|
quote:
Originally posted by Rodgerga
I have the following requirement.1a. Lookup the a value in Table A.colume1(Destination). 1b. If no value exists for Table A.colume1(Desti), by rule this must be the first time the process has run, and therefore must pick up claims beginning on January 1, 2000. This is done by looking up the MIN value in Tableb.column2(Source) where the run_start_date_time > January 1, 2000. 1c. If a value does exist for Table A.colume1(Desti), look up the MIN value in Tableb.column2 that is greater than the value in Table A.colume1(Source). This should be one digit greater, but that is not guaranteed so the lookup must be done.1d. If there is no value in Tableb.column2(Source) that is greater than TableA.column1(Desti), then the process is complete and can stop.1e.Insert records into Table c(Desti) is an additive table, meaning data is never updated in place, new rows are just laid down for each time payment is run. Any help on this is really appreciated.I have just started working with SSIS.
as suggested earlier, i think this can be better dealt as a procedure. so write a procedure using above business logic |
|
|
|
|
|
|
|
|
SSIS Req help