SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS synchronization, find new rows and save them for later


SSIS synchronization, find new rows and save them for later

Author
Message
dan 91669
dan 91669
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 119
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:
 [li][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
Phil Parkin
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214443 Visits: 24734
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:
 [li][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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
dan 91669
dan 91669
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 119
Ok so i looked at the tutorial i used - i grabbed the wrong arrow (apparently obviously).

What about the other question?
Phil Parkin
Phil Parkin
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214443 Visits: 24734
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.


Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
dan 91669
dan 91669
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 119
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
Phil Parkin
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214443 Visits: 24734
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
dan 91669
dan 91669
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 119
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
Phil Parkin
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214443 Visits: 24734
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?



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
dan 91669
dan 91669
Old Hand
Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)Old Hand (336 reputation)

Group: General Forum Members
Points: 336 Visits: 119
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
Phil Parkin
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214443 Visits: 24734
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.



Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search