August 10, 2017 at 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!
August 10, 2017 at 10:33 am
dan 91669 - Thursday, August 10, 2017 10:08 AMSystem: SQL Server Express 2014 64bit, Sage 100 2017 4.5 provideXI 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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 10, 2017 at 11:34 am
Ok so i looked at the tutorial i used - i grabbed the wrong arrow (apparently obviously).
What about the other question?
August 10, 2017 at 11:38 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 10, 2017 at 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?
August 10, 2017 at 11:49 am
dan 91669 - Thursday, August 10, 2017 11:43 AMWhy 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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 10, 2017 at 11:56 am
Phil Parkin - Thursday, August 10, 2017 11:48 AMdan 91669 - Thursday, August 10, 2017 11:43 AMWhy 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.
August 10, 2017 at 12:03 pm
dan 91669 - Thursday, August 10, 2017 11:56 AMInvoices 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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 10, 2017 at 12:39 pm
Phil Parkin - Thursday, August 10, 2017 12:03 PMdan 91669 - Thursday, August 10, 2017 11:56 AMInvoices 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 > 500to 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.
August 10, 2017 at 1:09 pm
dan 91669 - Thursday, August 10, 2017 12:39 PMCannot 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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 10, 2017 at 1:17 pm
Phil Parkin - Thursday, August 10, 2017 1:09 PMdan 91669 - Thursday, August 10, 2017 12:39 PMCannot 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...
August 10, 2017 at 1:42 pm
dan 91669 - Thursday, August 10, 2017 1:17 PMPhil Parkin - Thursday, August 10, 2017 1:09 PMOK, 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 queryselect cols
from invoiceHeader (source table)
WHERE TransDate >= [Max Trans Date variable]
3) Modify your detail select queryselect d.cols
from invoiceDetail d
join invoiceHeader h on d.InvoiceNo = h.InvoiceNo
where h.TransDate >= [Max Trans Date variable]
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 10, 2017 at 2:50 pm
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)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply