SSIS synchronization, find new rows and save them for later

Question

SSIS synchronization, find new rows and save them for later

dan 91669

SSChasing Mays

Points: 600
More actions
August 10, 2017 at 10:08 am

#323054

System: SQL Server Express 2014 64bit, Sage 100 2017 4.5 provideX
I am trying to sync my AR_InvoiceHistory [detail] and [header] sage database with my SQL database. I seem to have a working solution now for the header using the Lookup function to find the new rows and insert them to my destination. This takes a while on a relatively smaller table than the detail table - about 10minutes. It would be faster if i knew how to modify the SQL pull request from Mas100 to only start at invoices dated newer than #### - but i can't see where to modify that script from the ODBC source block (this is a secondary question).
So basically what i would like to do, is after finding what the new invoice numbers are in the [Header], i would like to 'capture' them such that when i go to pull new invoices in the [detail] table i don't need to have SSIS scan the whole Sage and SQL databases looking to see what changed.... it'll just look at the captured rows (invoice numbers) from the [Header] procedure and drag them in to the sql [detail] table. I can't do any sort of date parameter on the [detail] table - it is not a column.
one more side question... while syncing the Header tables i got this error:one more side question... while syncing the Header tables i got this error:

[Lookup [2]] Warning: The Lookup encountered duplicate reference key values when caching reference data. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.......
Why is it saying duplicate reference key?...

thanks!

Phil Parkin SSC Guru Points: 247158 More actions · Answer 1

dan 91669 - Thursday, August 10, 2017 10:08 AM
System: SQL Server Express 2014 64bit, Sage 100 2017 4.5 provideX
I am trying to sync my AR_InvoiceHistory [detail] and [header] sage database with my SQL database. I seem to have a working solution now for the header using the Lookup function to find the new rows and insert them to my destination. This takes a while on a relatively smaller table than the detail table - about 10minutes. It would be faster if i knew how to modify the SQL pull request from Mas100 to only start at invoices dated newer than #### - but i can't see where to modify that script from the ODBC source block (this is a secondary question).
So basically what i would like to do, is after finding what the new invoice numbers are in the [Header], i would like to 'capture' them such that when i go to pull new invoices in the [detail] table i don't need to have SSIS scan the whole Sage and SQL databases looking to see what changed.... it'll just look at the captured rows (invoice numbers) from the [Header] procedure and drag them in to the sql [detail] table. I can't do any sort of date parameter on the [detail] table - it is not a column.
one more side question... while syncing the Header tables i got this error:one more side question... while syncing the Header tables i got this error:
[Lookup [2]] Warning: The Lookup encountered duplicate reference key values when caching reference data. This error occurs in Full Cache mode only. Either remove the duplicate key values, or change the cache mode to PARTIAL or NO_CACHE.......
Why is it saying duplicate reference key?...
thanks!

Because the way in which you have defined your lookup results in more than one row being returned for the same match key.

dan 91669 SSChasing Mays Points: 600 More actions · Answer 2

Ok so i looked at the tutorial i used - i grabbed the wrong arrow (apparently obviously).

What about the other question?

Phil Parkin SSC Guru Points: 247158 More actions · Answer 3

I presume that you are using a table as the source for your data flow?
If so, change this to a query and add whatever filter criteria you need.

dan 91669 SSChasing Mays Points: 600 More actions · Answer 4

Why did i miss that.

Ok, last one. SSIS figures out through the lookup what the new rows (invoices) are in the header, how do i capture those rows (invoice numbers) and make it an easy task for SSIS to grab the new rows from the detail table in Sage?

Phil Parkin SSC Guru Points: 247158 More actions · Answer 5

dan 91669 - Thursday, August 10, 2017 11:43 AM
Why did i miss that.
Ok, last one. SSIS figures out through the lookup what the new rows (invoices) are in the header, how do i capture those rows (invoice numbers) and make it an easy task for SSIS to grab the new rows from the detail table in Sage?

That is the tricky part.
Do the invoice numbers follow some sort of ascending sequence? Or do they contain a 'DateCreated' column?

dan 91669 SSChasing Mays Points: 600 More actions · Answer 6

Phil Parkin - Thursday, August 10, 2017 11:48 AM
dan 91669 - Thursday, August 10, 2017 11:43 AM
Why did i miss that.
Ok, last one. SSIS figures out through the lookup what the new rows (invoices) are in the header, how do i capture those rows (invoice numbers) and make it an easy task for SSIS to grab the new rows from the detail table in Sage?
That is the tricky part.
Do the invoice numbers follow some sort of ascending sequence? Or do they contain a 'DateCreated' column?

Invoices are ascending, but not continuous. 1234, 1235, 1236, 2234,2235,3234, etc. So i can't just grab everything after such 1234. There is no date.

Phil Parkin SSC Guru Points: 247158 More actions · Answer 7

dan 91669 - Thursday, August 10, 2017 11:56 AM
Invoices are ascending, but not continuous. 1234, 1235, 1236, 2234,2235,3234, etc. So i can't just grab everything after such 1234. There is no date.

OK, but if you know that Max(InvoiceNo) from your target db is, say, 500, can you not do

select cols from source where InvoiceNo > 500

to find new rows?
Are we to assume that existing rows cannot be updated or deleted? Or if they can, that you are not interested in cascading these changes to the target db?

dan 91669 SSChasing Mays Points: 600 More actions · Answer 8

Phil Parkin - Thursday, August 10, 2017 12:03 PM
dan 91669 - Thursday, August 10, 2017 11:56 AM
Invoices are ascending, but not continuous. 1234, 1235, 1236, 2234,2235,3234, etc. So i can't just grab everything after such 1234. There is no date.
OK, but if you know that Max(InvoiceNo) from your target db is, say, 500, can you not do
select cols from source where InvoiceNo > 500
to find new rows?
Are we to assume that existing rows cannot be updated or deleted? Or if they can, that you are not interested in cascading these changes to the target db?

Cannot really change anything on the source server.
Invoice numbers are broken up by different parameters to create invoice series's, therefore i can't really set a starting point. Since the header and detail both have invoiceNumber as a field, i was hoping to use the results of the header sync task to fascinate the detail sync.

Phil Parkin SSC Guru Points: 247158 More actions · Answer 9

dan 91669 - Thursday, August 10, 2017 12:39 PM
Cannot really change anything on the source server.
Invoice numbers are broken up by different parameters to create invoice series's, therefore i can't really set a starting point. Since the header and detail both have invoiceNumber as a field, i was hoping to use the results of the header sync task to fascinate the detail sync.

OK, so you want to be able to capture the fact that Invoices a, b and c are new and then, somehow, modify the query which selects the detail rows such that it filters them at source to include only those which belong to a, b and c?
I can't think of an elegant way of doing that in SSIS. Easy enough in T-SQL.
To be honest, I am very surprised that these tables do not have date columns which you can use.

dan 91669 SSChasing Mays Points: 600 More actions · Answer 10

Phil Parkin - Thursday, August 10, 2017 1:09 PM
dan 91669 - Thursday, August 10, 2017 12:39 PM
Cannot really change anything on the source server.
Invoice numbers are broken up by different parameters to create invoice series's, therefore i can't really set a starting point. Since the header and detail both have invoiceNumber as a field, i was hoping to use the results of the header sync task to fascinate the detail sync.
OK, so you want to be able to capture the fact that Invoices a, b and c are new and then, somehow, modify the query which selects the detail rows such that it filters them at source to include only those which belong to a, b and c?
I can't think of an elegant way of doing that in SSIS. Easy enough in T-SQL.
To be honest, I am very surprised that these tables do not have date columns which you can use.

yeah, you and me both. Sage decided to let the transaction and invoice dates live in the header but not the detail. There are no other date fields utilized, and even if they were they likely wouldn't be key fields anyway.... you know just to add insult to injury.

So i guess this leaves me with doing a lookup on the entire detail tables? i feel bad for my ram and cpu...

Phil Parkin SSC Guru Points: 247158 More actions · Answer 11

dan 91669 - Thursday, August 10, 2017 1:17 PM
Phil Parkin - Thursday, August 10, 2017 1:09 PM
OK, so you want to be able to capture the fact that Invoices a, b and c are new and then, somehow, modify the query which selects the detail rows such that it filters them at source to include only those which belong to a, b and c?
I can't think of an elegant way of doing that in SSIS. Easy enough in T-SQL.
To be honest, I am very surprised that these tables do not have date columns which you can use.
yeah, you and me both. Sage decided to let the transaction and invoice dates live in the header but not the detail. There are no other date fields utilized, and even if they were they likely wouldn't be key fields anyway.... you know just to add insult to injury.
So i guess this leaves me with doing a lookup on the entire detail tables? i feel bad for my ram and cpu...

OK, then I think that there is a way forward.
1) SELECT MAX(TransDate) from invoice header on target table and store in a variable in SSIS
2) Modify your header select query
select cols from invoiceHeader (source table) WHERE TransDate >= [Max Trans Date variable]
3) Modify your detail select query
select d.cols from invoiceDetail d join invoiceHeader h on d.InvoiceNo = h.InvoiceNo where h.TransDate >= [Max Trans Date variable]

dan 91669 SSChasing Mays Points: 600 More actions · Answer 12

got it. it works. Although truth be told i didnt get the variable to work but i am using the join - i didnt think that would work in the query. (yeah head on desk for not even trying)

SSIS synchronization, find new rows and save them for later

Cookies on SQLServerCentral